All you need is a birth date or starting date and Excel can figure out the age, next birthday, days to next birthday or when you reach a milestone like 10,000 or 15,000 days. That’s just some clever tricks with DateDif().
How old is someone or something?
To calculate the age, we will need to use “Y” as the unit within the DateDIF() formula as shown below. Note: DOB stands for date of birth.
=DATEDIF(DOB, TODAY(),”Y”)
In this example we’ve used the formula =DATEDIF(B2, TODAY(),”Y”) to work out Dwayne Johnson’s age of 50. The formula can then be filled to the remaining cells.
But how about the other different measures of DATEDIF()?
The DATEDIF() function calculates the number of days, months, or years between two dates.
DATEDIF() syntax
The syntax is:
=DATEDIF(start_date,end_date,unit)
Start_date A date representing the start date (required).
End_date A date representing the end date (required).
Unit A unit of time to use (days, months, or years).
Unit can be presented with:
- “Y” – shows the year between the two dates.
- “M” – shows the months between the two dates.
- “D” – shows the days between the two dates.
- “YM” – shows months without days and years
- “MD” – shows days without months and years
- “YD” – shows days without years
Calculate total number of days alive
You can also use DATEDIF() to calculate how many days you’ve been alive.
Simply take the usual formula =DATEDIF(DOB,TODAY(),"D") and use the unit “D” for days.
For example, take a birthday in 1994, I have been able to calculate the number of days I’ve been alive. Formulas are shown in the second row.
We will need to subtract the number of days alive from 15,000 days, that’s easy enough. A simple subtraction.
Next, use the TODAY() function to add the amount of 3,833 days. This will result in the date when I will be 15,000 days old.
Or take your birthday as an Excel date and add 15000 e.g. =Birthday+15000
Calculate days until next birthday
Excel can calculate days until the next birthday using DateDiff(). It’s something that should be in Outlook but isn’t except with a very complex custom field. But in Excel it’s very simple and useful.
In Column A enter the Dates of Birth or other anniversary dates (date of employment etc).
In B2 enter this formula to calculate your birthday for next year =DATE(YEAR(A2)+DATEDIF(A2,TODAY(),"y")+1,MONTH(A2),DAY(A2))
Then Column C is just DateDiff() to figure out the days between today and the next birthday.
=DATEDIF(Today(),C2,"d")
The interim column showing next birthday isn’t necessary. Here’s the same calculation but in a single formula, A2 is the birthday date:
=DATEDIF(TODAY(),DATE(YEAR(A2)+DATEDIF(A2,TODAY(),"y")+1,MONTH(A2),DAY(A2)),"d")
Conditional Formatting
You can even use Conditional Formatting to highlight the cells in Excel to alert you of birthdays coming up.
Highlight your data, then go to Home | Styles | Conditional Formatting | New Rule
Under Select a New Rule Type go to Format only cells that contain
Now we’re going to set it so that cells will highlight that a birthday is coming up within 2 weeks (14 days). So, under the drop-down box, choose ‘less than’ and the value 15.
Next go to Format and adjust your color, font style etc. as you wish, and click OK.
Now all the birthdays which are coming up in 14 days will be highlighted.
Five ways to turn text into Excel dates
Text to Excel Date conversion by adding Zero with Paste Special
Dates, time and duration, the truth in Excel
Excel Conditional Formatting, beyond the pre-sets
