Date format issue

[cmb80]

Hello all, I have some code which extracts data from the web and pastes into Excel.

The date (column A) extracts to the format below 01.04.2014

At this moment it is stored as just text. So I run a find and replace to change . to / - but when it does this - it makes the field a date, but it now reads 04/01/2014 rather than 01/04/2014.

The find and replace works if the day is greater than 12 (as there are only 12 months I guess), but for days less than 13 it flips the day and the month the wrong way around.

Can anyone provide some code to turn 01.04.2014 into 01/04/2014 (or 41730 - the date code for the date)

[MarvinP]

Hi,
The Date function should help. See if
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))
works for you in the attached.

[Norie]

Try Date>Text to columns... selecting DMY for column data format on the 3rd step.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[cmb80]

Hi all and thanks for your replies. JOHN H DAVIS your Vba code suited my needs perfectly - thank you