Date calculation problem

brianfosterblack

Board Regular
Joined
Nov 1, 2011
Messages
248
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
Joined
Jun 12, 2014
Messages
65,587
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IFERROR(DATEDIF(AQ5,DATE($F$3-1,4,1),"y"),"")
 
Solution

brianfosterblack

Board Regular
Joined
Nov 1, 2011
Messages
248
Tha
How about
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,587
Office Version
  1. 365
Platform
  1. Windows

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,011
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 1, 2011
Messages
248
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,587
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top