Validating birthdate to be greater than 18 years
=IF($I=1, DATEDIF($B, TODAY(), "Y") & " Years, " & DATEDIF($B, TODAY(), "YM") & " Months, " & DATEDIF($B, TODAY(), "MD") & " Days", IF(ISNUMBER($B), DATEDIF($B, $B,"Y") & " Years, " & DATEDIF($B, $B,"YM") & " Months, " & DATEDIF($B, $B,"MD") & " Days", "")) The formulas in B10 and B11 work with the same logic.
Of course, they are much simpler because they include just one DATEDIF function to return age as the number of complete months or days, respectively.
Of course, it's not possible to cover every and each scenario, but the following examples will give you some ideas on how you can tweak an age formula depending on your particular task.
If you want to know someone's age at a certain date, use the DATEDIF age formula discussed above, but replace the TODAY() function in the 2 Where B1 is the DOB, and B2 is the date on which you want to calculate age.
The formula is obvious and easy-to-remember, however, there is a tiny problem.Assuming the dates of birth are in column B beginning with row 3, and the year of the last medical examination is in column C, the age calculation formula goes as follows: Because the exact date of the medical examination is not defined, you use the DATE function with an arbitrary date and month argument, e.g. The DATE function extracts the year from cell B3, makes a complete date using the month and day numbers that you supplied (1-Jan in this example), and passes that date to DATEDIF.As the result, you get the patient's age as of January 1 of a particular year: Supposing your friend was born on 8 March 1978.To learn the details, I invite you to download this Excel Age Calculator and investigate the formulas in cells B9: B11.Download Age Calculator for Excel They users of our Ultimate Suite for Excel don't have to bother about making their own age calculator in Excel - it is only a couple of clicks away: Done!