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]

Yes they were

 

[steve the fish]

I did some playing around. This doesnt work:

Dim dte(1 To 10) As Date

For i = LBound(dte) To UBound(dte)
    dte(i) = i & "/10/2017"
Next

Range("A1:A10").Value = Application.Transpose(dte)

Adding the date via the loop seems to produce the correct dates:

Dim dte(1 To 10) As Date

For i = LBound(dte) To UBound(dte)
    dte(i) = i & "/10/2017"
    Range("A" & i).Value = dte(i)
Next

Not storing the date as a date but a long also seems to produce the correct results:

Dim dte(1 To 10) As Long

For i = LBound(dte) To UBound(dte)
    dte(i) = CLng(CDate(i & "/10/2017"))
Next

Range("A1:A10").Value = Application.Transpose(dte)

 

[BigShango]

Thanks for the help troops. Got it sorted now between using Format and CDate. The real purpose of this is to compare dates to a deadline and count the number of instances before or after it. What I ended up doing was using Format to put it into 22,Jan,2017 format within variables so it can't get confused with the UK/US date format. It does some bizarre things if you don't wrap it in CDate. I was using MsgBoxes to check where the errors were and had it saying stuff like "22/Jan/2017 is before 01/Aug/2017" next row "22/Nov/2017 is before 01/Aug/2017" next row "24/Nov/2017 is after 01/Aug/2017"

Excel has always caused me problems when dealing with dates.

 

[JackDanIce]

Glad to see it's resolved and thanks to Steve for suggesting to use a long variable and then convert the date.