Forum Discussion

Jo-AnnDP's avatar
Jo-AnnDP
Copper Contributor
Aug 11, 2022
Solved

date calculation issues

trying to get the "years" between birth date and today i've changed the colum (birth date) to custom dd-mmmm-yy but it stays as is 07.08.22 - which makes my formula #VALUE when i manually cha...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Aug 11, 2022

    Jo-AnnDP That's because the date columns contain texts. Your system doesn't recognize dates with the dot between the day, month and year.

    Select the Birthdate column. On the Data ribbon select Text to Columns. Advance to step 3 without changing anything.

    In Step 3 set the column type to Date and select DMY. Press Finish. That should transform the "dates with dots" to valid dates for your system settings.

     

Lorenzo's avatar
Lorenzo
Silver Contributor
Aug 11, 2022

Jo-AnnDP 

 

If all your Birth Dates end with 2 digits and you're only looking to get Year(s) diff. a simple solution in F2: =RIGHT(E2,2) - RIGHT(D2,2)

 

Otherwise in G2:

=DATEDIF(--SUBSTITUTE(D2,".","/"), --SUBSTITUTE(E2,".","/"), "y")

 

Resources