Aug 11 2022 12:50 AM
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?
Aug 11 2022 01:12 AM
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")
Aug 11 2022 01:18 AM - edited Aug 11 2022 01:19 AM
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.
Aug 11 2022 02:05 AM
Aug 11 2022 01:18 AM - edited Aug 11 2022 01:19 AM
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.