Date calculation problem

[brianfosterblack]

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

=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

=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.

 

[Fluff]

How about

=IFERROR(DATEDIF(AQ5,DATE($F$3-1,4,1),"y"),"")

 

[brianfosterblack]

Tha

How about

=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.

 

[Fluff]

Ok

 

[Dave Patton]

Date and Time 2021.xlsm
	A	B	C	D
1	Text	DOB	Current Year
2	ID 620206 0155 087	06-Feb-1962	01-Apr-2021	59
3
9e

Cell Formulas
Range	Formula
B2	B2	=--TEXT("19"&MID(A2,4,6),"0000-00-00")
C2	C2	=DATE(YEAR(TODAY()),4,1)
D2	D2	=DATEDIF(B2,C2,"y")

 

[brianfosterblack]

Date and Time 2021.xlsm
	A	B	C	D
1	Text	DOB	Current Year
2	ID 620206 0155 087	06-Feb-1962	01-Apr-2021	59
3
9e

Cell Formulas
Range	Formula
B2	B2	=--TEXT("19"&MID(A2,4,6),"0000-00-00")
C2	C2	=DATE(YEAR(TODAY()),4,1)
D2	D2	=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]

Ok

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

 

[Fluff]

Glad we could help & thanks for the feedback.