Replacing . with : in date time value changes the date

[EchoPassenger]

I have a column of dates and times, all formatted as dd/mm/yyyy hh:mm

I am trying to write some code to amend the data when a user enters the time in the format of hh.mm instead of hh:mm

Strangely though, I am finding that running my code causes excel to switch around the dd/mm part of the data...

Please Login or Register  to view this content.

If I run this on a cell containing 07/05/2021 09.15 then it changes to 05/07/2021 09:15

However, the date format set for the cell remains dd/mm/yyyy hh:mm, plus if I manually use the Find/Replace dialog to make the same replacement it doesn't switch around the dd/mm

Might be something incredibly obvious, but I can't work out why it's happening.

[Richard Buttrey]

What is 07/05/2021 meant to be?

The 7th May or the 5th July?

[EchoPassenger]

The date format is dd/mm/yyyy, so 7th May

[Pete_UK]

I suspect that the first entry, 07/05/2021 09.15, is actually a text value that just happens to look like a date and time, and that when you do your replacement then Excel tries to make sense of it and uses your regional settings to conclude how to format the result.

Hope this helps.

Pete

[EchoPassenger]

But the cell number format is set to dd/mm/yyyy hh:mm, and my regional settings are the same, plus why would the code cause Excel to change it but Find/Replace doesn't change it?

Is there another way to code the replacement that might work differently?

[Richard Buttrey]

Hi

One way

Please Login or Register  to view this content.

[EchoPassenger]

Thanks Richard, I'll give that a try

[Marc L]

Hi,

'cause of the time included just use VBA CDate function to convert the text replacement as a Date …