date calculation issues

[Jo-AnnDP]

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 changed the birth date colum to 07-Aug-22 my formula works but there are 2200+ birth dates and i dont have time to manually change all of them.... please help?

[#2]

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

[#3 · Accepted solution]

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

[#4]

@riny thank you the text to columns worked perfectly