# Date calculation problem

#### brianfosterblack

##### Board Regular
I seem to be having a problem with my calculation when someone has a different date format on their computer
My date format is dd/mm/yyyy which is the standard in my country
I have an ID number which is our national ID 620206 0155 087 where the first 6 numbers are the date of birth in reverse. I have to assume that all people are under 100 and therefore use this formula to determine the date of birth. Cell AM 5 contains the ID number
Excel Formula:
``=IF(A5="","",IFERROR(0+TEXT(19+(0+LEFT(AM5,2)<0+TEXT(NOW(),"yy"))&LEFT(AM5,6),"0000-00-00"),""))``
I then use this formula to determine the age where cell F3 contains the year for next year but I need the age on the first of April for the current year. Cell AQ5 contains the result of the above formula
Excel Formula:
``=IFERROR(DATEDIF(AQ5,"1/4/"&\$F\$3-1,"y"),"")``
This works fine on my computer for my date settings but as soon as someone has their date settings in another format the age calculation does not work. Can anyone please help.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=IFERROR(DATEDIF(AQ5,DATE(\$F\$3-1,4,1),"y"),"")``

#### brianfosterblack

##### Board Regular
Tha
Excel Formula:
``=IFERROR(DATEDIF(AQ5,DATE(\$F\$3-1,4,1),"y"),"")``
Thank you, this works fine on my computer. I am sending it off to the person with a different date setting and will let you know the result.

Thanks for the help.

Ok

#### Dave Patton

##### Well-known Member

Date and Time 2021.xlsm
ABCD
1TextDOBCurrent Year
2ID 620206 0155 087 06-Feb-196201-Apr-202159
3
9e
Cell Formulas
RangeFormula
B2B2=--TEXT("19"&MID(A2,4,6),"0000-00-00")
C2C2=DATE(YEAR(TODAY()),4,1)
D2D2=DATEDIF(B2,C2,"y")

#### brianfosterblack

##### Board Regular
Date and Time 2021.xlsm
ABCD
1TextDOBCurrent Year
2ID 620206 0155 087 06-Feb-196201-Apr-202159
3
9e
Cell Formulas
RangeFormula
B2B2=--TEXT("19"&MID(A2,4,6),"0000-00-00")
C2C2=DATE(YEAR(TODAY()),4,1)
D2D2=DATEDIF(B2,C2,"y")
Hi Dave,

Thanks for this but the reason I use 1st April is that this is for an annual membership which runs from April to March of the next year. So if I use the Today() formula it will show the 1st April 2022 for anyone joining in January to March 2022. In cell F3 we enter the year ending and that is currently 2022. That is why we use that year reference.
I have received confirmation that the solution offered by Fluff is working and resolves my problem. However, thank you for the fornukla to determine date of birth. I will be using that.

#### brianfosterblack

##### Board Regular
Thanks Fluff. I have received confirmation that this is working perfectly. Thank you

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
2
Views
219
Replies
3
Views
213
Replies
10
Views
468
Replies
3
Views
164
Replies
7
Views
102

1,147,477
Messages
5,741,360
Members
423,657
Latest member
Medrok2021

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back