# how to calculate DOB if date format is differ

1. ## how to calculate DOB if date format is differ

HI
in my excel file im calculating DOB with datedif formula if the date format is Day, Month, Year in 2016 microsoft office
if the date format is month day year the the formula is not working, can you help with this.

and same formula is working month day year in 2010 microsoft office
not working in Day, Month, Year format.

attached file is for your convenience

TIA  Register To Reply

2. ## Re: how to calculate DOB if date format is differ

How do you know with: 12/11/1990, which month is?  Register To Reply

3. ## Re: how to calculate DOB if date format is differ

bebo021999 is not possible, but we use only one format
1. day month year or
2. month day year,

if we use number 2 month day year, then is if possible to calculate DOB ?  Register To Reply

4. ## Re: how to calculate DOB if date format is differ

Would suggest two options:

1) use helper in C2 to convert D2 to properly date:
=IFERROR(D2+0,DATE(RIGHT(D2,4)+0,LEFT(D2,2)+0,MID(D2,4,2)+0))

2) Replace D2 with (1) (use Ctrl-H) which I did in column F
=IF(DATEDIF(IFERROR(D2+0,DATE(RIGHT(D2,4)+0,LEFT(D2,2)+0,MID(D2,4,2)+0)), E2,"y")=0,"",DATEDIF(IFERROR(D2+0,DATE(RIGHT(D2,4)+0,LEFT(D2,2)+0,MID(D2,4,2)+0)), E2,"y")&" Years, ")& IF(DATEDIF(IFERROR(D2+0,DATE(RIGHT(D2,4)+0,LEFT(D2,2)+0,MID(D2,4,2)+0)), E2,"ym")=0,"",DATEDIF(IFERROR(D2+0,DATE(RIGHT(D2,4)+0,LEFT(D2,2)+0,MID(D2,4,2)+0)), E2,"ym")&" Months, ")& IF(DATEDIF(IFERROR(D2+0,DATE(RIGHT(D2,4)+0,LEFT(D2,2)+0,MID(D2,4,2)+0)), E2,"md")=0,"",DATEDIF(IFERROR(D2+0,DATE(RIGHT(D2,4)+0,LEFT(D2,2)+0,MID(D2,4,2)+0)), E2,"md")&" Days")  Register To Reply