0

In Excel VBA I am assigning a hard coded date to a named range as below:

 Worksheets("Main").Range("Effectivedate").Value ="11/12/2018"

This is been triggered on a button Click. Now I have my system culture as UK. When I click the button the value populated on the cell is "12/11/2018". If I change the culture to US then it get populated as "11/12/2018".

As my application is been used between US and UK countries, I would like to know is there a way assign the date as such t the cell without formatting.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
user2081126
  • 77
  • 1
  • 11
  • I am using such a format only (dd mmmm yyyy). But the problem here is if I give input string as 11/12/2018, if UK culture then it converts as 12 November 2018. It should actually be 11 December 2018 – user2081126 Jun 07 '18 at 08:26
  • 2
    This is why below I suggest using an unambiguous format – QHarr Jun 07 '18 at 08:27
  • @QHarr: Thanks for the suggestion. But my requirement is to use it in dd mmm yyyy format. I can not specifically use the above mentioned. – user2081126 Jun 07 '18 at 08:28
  • @QHarr: Also to add even if I use the format you mentioned, in the editor window it is still 12/11/2018 and content gets saved like this. – user2081126 Jun 07 '18 at 08:29

1 Answers1

4

I would suggest using an umambiguous data format ("yyyy-mm-dd") like

"2018-12-11"

Can you otherwise try:

Option Explicit

Sub test()
    With Worksheets("Main").Range("Effectivedate")
        .Value = 43445
        .NumberFormat = "dd/mm/yyyy"
    End With
End Sub
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • I am using such a format only (dd mmmm yyyy). But the problem here is if I give input string as 11/12/2018, if UK culture then it converts as 12 November 2018. It should actually be 11 December 2018 – user2081126 Jun 07 '18 at 08:26
  • How can I derive this number 43445. My date I mentioned is dynamic and it comes froma different cell. Please can you advise how it can be converted as number like this. Thanks – user2081126 Jun 07 '18 at 08:37
  • 1
    @user2081126 this number is simply how excel stores the date. you can replace that line with `.Value = Format(DateSerial(2018,12,11),"dd/mm/yyyy")`, but format the cell prior to inputting the value. – Banana Jun 07 '18 at 08:39
  • Excel internally uses US format for dates so it is better to use unambiguous yyyy-mm-dd or pass as Long/Double and then format in sheet. – QHarr Jun 07 '18 at 08:39
  • @Banana and Qharr: Thanks for the solution. But my only concern here, though the date is displaying as 11 December 2018, in the editor window it still show as 12/11/2018 which is still in US format when the system culture is UK – user2081126 Jun 07 '18 at 08:46
  • What do you mean by in editor window? You want the user to see dd/mm/yyyy in the sheet correct? Then pass in umambiguous fashion (as described) and format in sheet as dd/mm/yyyy. – QHarr Jun 07 '18 at 08:47
  • Please ignore that. it is working perfectly :). Just would like to know if there is any direct convertion statement to convert date I get to serial. This date comes as dynamically. that's why – user2081126 Jun 07 '18 at 08:55
  • Try CLng(yourDate) if passing a long – QHarr Jun 07 '18 at 09:04
  • Can you post the line here in the comment? CLng(yourDate) – QHarr Jun 07 '18 at 10:07
  • cLng("11/12/2018") – user2081126 Jun 07 '18 at 10:09
  • Are you not reading in from a sheet? Where is the date value coming from? – QHarr Jun 07 '18 at 10:11
  • text as "11/12/2018" is ambiguous and will most likely be treated as mm/dd/yyyy by Excel. Don't pass in this manner. If you are reading from a sheet a real date you can use CLng(date). – QHarr Jun 07 '18 at 10:12
  • Then try CDate(...) but I worry that will use mm/dd/yyyy internally tbh. Make sure that in Excel it is formatted as an actual date, not text. If it is text, make sure it is yyyy-mm-dd. – QHarr Jun 07 '18 at 10:15
  • Thank you so much QHarr. Really appreciate your help here. – user2081126 Jun 07 '18 at 10:53
  • Any luck then with latest? – QHarr Jun 07 '18 at 10:54
  • 1
    Ya.. its working fine. I did CLng(CDte(String-Date)) – user2081126 Jun 07 '18 at 11:23