Can someone please provide some guidance on how to complete this task, i need to re-arrange a date format in excel. For example, i have over 2000 cells with mixed date of birth formats.
I.e.: (1)01/02/1995 (2)02/01/1995 (3) 1/2/1995.
How i would i proceed, if my desired outcome is to have all DOBs in english format on my macbook.
That shouldn't be a problem for dates where the day number is 13 or greater. Both 14/01/1995 and 01/14/1995 clearly recognised as UK and US formats. But how about 01/02/1995? How would Excel know that it is already in the UK format or not. What is the source of these dates? Is there any other you can identify such ambiguous dates as UK or US formatted dates in the data set?
Hi, I don't know if this helps but just a few days ago I read the tip to use the built-in function N()
If an interesting function that shows the actual cell value of the parameter passed
In case of a date, it returns the serial value; I don't know how it treats dates before the 13th but it's worth a try.
I always try to write my dates and timestamps as a serial number to avoid these issues
Maybe it helps