Functions: DATEDIF
August 17th, 2007 | by Michael |Note: This post should hold true for both Numbers, and Excel, as the functions are identical.
The DATEDIF function finds the number of days, months, or years between two dates.
The format is DATEDIF(start-date, end-date, method)
- start-date and end-date are self explanatory
- method has several options:
“D” - counts the days between the dates.
“M” - counts the months between the dates.
“Y” - counts the years between the dates.
“MD” - counts the days between the dates as if they were in the same year
“YM” - counts the months between the dates as if they were in the same year
“YD” - counts the months between the dates as if they were in the same month and year
OK, let’s put it all together for a real-word example. We are going to create a function that calculates a users current age from their birthday. Before we go through the example, I need to introduce two addition functions: 1) NOW(), returns today’s date. 2) Concatenate, joins two or more strings of text. You can use “&” as a shortcut for this function.
First, open a new sheet, and enter the word “Birthday:” in cell A1 of your table, in cell B2 enter “Your Age:”
Next, format cell B1 as a date, and enter your date of birth in the cell.
Finally, we will enter our formula in cell B2. Type the following:
=DATEDIF(B1,NOW),”Y”)&” Years,”& DATEDIF(B1,NOW(),”YM”)&” Months, “& DATEDIF(B1,NOW(),”MD”)& ” Days”
Now you should see something like the screenshot below.
Let’s take a closer look at our formula. It contains three DATEDIF functions, concatenated together with some text. All of the functions use the entered birthdate, B1, as the start-date, and the NOW() function is used as end-date. The first instance uses the “Y” method to determine the number of years elapsed since the birthdate, the second function uses the “YM” method to determine the number of months elapsed since the birthdate. The third function use the “MD” method to determine the number of days since the birthdate.
That’s the DATEDIF function in a nutshell.
For practice, you may consider altering the formula to create a sheet that determines how old you will be on a future date.
