Forum

Notifications
Clear all

Correcting Date of birth format while cleaning data.

3 Posts
3 Users
0 Reactions
174 Views
(@ishhy9577)
Posts: 1
New Member
Topic starter
 

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.

 
Posted : 28/08/2024 11:27 am
Riny van Eekelen
(@riny)
Posts: 1194
Member Moderator
 

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?

 
Posted : 29/08/2024 12:38 am
(@keebellah)
Posts: 373
Reputable Member
 

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

 
Posted : 29/08/2024 2:21 am
Share: