Convertiing Dates

[andrewc]

Hi,

In column A of the attached file I have some “dates” extracted from a text file.

As you can see some of these dates are in text form and the others are numbers.

Can someone please tell me how to convert these “dates” to actual dates (i.e. with serial numbers) in the format DD/MM/YYYY.

Thanks!

[Glenn Kennedy]

Hi there. Try this...

=IF(ISTEXT(A2),DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)),A2)

[ashishmehra2010]

Hi Andrew,

You can make use of Format Cells or use CTRL + 1 shortcut key
Select Custom & enter the format in Type box

Regards,
AM

[andrewc]

Hi,

Thanks for the quick response!

The formula suggested works for the dates in A2 and A3 but not for those in A4 and A5 which seem to be recognised by Excel as dates in the format MM/DD/YYYY and which I can't change to the DD/MM/YYYY just by changing the formatting.

Thanks!

[ashishmehra2010]

Hi Andrew,

Use this formula

Please Login or Register  to view this content.

Regards,
AM

[Glenn Kennedy]

For Excel, dates are simply numbers. In A3, the number is 42281. That corresponds to 4th October 2015.