Date problems. Excel switching date and month around.

[BigShango]

Hi,

I have a much more complicated code but my issue can be broken down to this simple line.

Range("a1").Value = Replace(Range("a1").Value, "action ", "")

Cell A1 starts off as, for example, "action 04/08/2017" (4th of August 2017, as I'm in the UK). My code removes the action part. Seems simple enough. The cell should read 04/08/2017 but Excel will flip it to 08/04/2017.

I understand that it's changing the date format to be US, or thinking it is in US and changing it to UK, but I can't for the life of me understand why or figure out how to stop it. If I type 04/08/2017 into a cell it shows up correctly, if I through any means cause code to type 04/08/2017 into a cell it'll change around to 08/04/2017. It's very frustrating.

Any help appreciated. Cheers.


Edit - just to simplify even further. If I run Range("a2").Value = "04/08/2017", I get 08/04/2017 in the cell.

This almost caused a huge disaster at work

 

[JackDanIce]

This happened to me about 2 months ago, developing code for a client and though the code was reading the data in as UK format (cell settings set to UK style, PC time and date settings set to UK), the code editing window showed the memory variables holding the dates as still being in UK format, but once processed and output returned to the sheet, the date has switched to a US type, which messed up the alignment of data.

To me, it seemed like the date conversion happened after the code complied and ran, i.e. after execution so to me, suggested maybe it's a quirk external to Excel, than being caused by Excel, but I honestly do not know.

In the end, I used string variables to break apart the date and then "re-created" it inside the code (think using DATESERIAL or similar, you'll need to check/verify which function VBA uses to do this) and then outputting results of that transform to ensure the dates were maintained as UK setting.

Client's PC I was writing the code on was running Excel 2013, not sure how relevant that is. At home, I use Excel 2010 and not experienced this problem.

This was my post asking similar when I encountered this: Date issues when using .Find in VBA (UK to US conversion)

 

[steve the fish]

Either wrapping the Replace with CDate or DateValue should work for you.

 

[JackDanIce]

@steve the fish that didn't work for me, it seemed to be a "deeper" issue than ensuring the data types were correct. I eventually had to "break" the date apart and then recreate it at output stage to ensure output was in UK format.

 

[Akuini]

Try using DateSerial function to make sure the order of day-month-year is right.

---

Please use Code Tags when posting a code. Like this: [CODE ]Your Code Here[/ CODE]

[B][COLOR=Royalblue]Sub[/COLOR][/B] dtDate[B]()[/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] dt
    dt [B]=[/B] Split[B]([/B]Replace[B]([/B]Range[B]([/B][B][COLOR=brown]"A1"[/COLOR][/B][B]).[/B]Value[B],[/B] [B][COLOR=brown]"action "[/COLOR][/B][B],[/B] [B][COLOR=brown]""[/COLOR][/B][B]),[/B] [B][COLOR=brown]"/"[/COLOR][/B][B])[/B]
         Range[B]([/B][B][COLOR=brown]"A1"[/COLOR][/B][B]).[/B]Value [B]=[/B] DateSerial[B]([/B]dt[B]([/B][B][B][COLOR=crimson]2[/COLOR][/B][/B][B]),[/B] dt[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B]),[/B] dt[B]([/B][B][B][COLOR=crimson]0[/COLOR][/B][/B][B]))[/B]
 
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

---

 

[steve the fish]

Hi Jack. There isnt a problem with doing it as you describe as its bulletproof. I just cant remember ever having to. For the problem above the CDate appears to work fine.

 

[JackDanIce]

Hi Steve, CDate didn't work for me, hence creating that thread back in June and settling for taking the "break it down and recreate route"! It was more to share info/experience than critique your suggestion, apologies if came across that way

 

[steve the fish]

No of course i took no offense. Id have thought what happened in your case was that excel already thought your date was a true date so CDate would make no difference.

 

[JackDanIce]

Possibly/probably, I really don't know what happened in my case!

In VBE, hovering over each array element holding a date value and checking the Locals window, it DID show value as DD/MM/YYYY but after, when I outputted same array, it had switched to MM/DD/YYYY!

Client and I picked up on this after we noticed only dates where DD=MM (and DD < 13) were aligned, pre and post macro run, but none of the other dates. CDate didn't appear to make any difference either, really odd situation.

 

[steve the fish]

Were the dates appearing between hashes, e.g. #01/10/2017#?