# 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

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

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

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 ?

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")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1