Excel: Date Differences
Published on 29th March 2005
This month we are going to look at how you can use Excel to calculate time periods such as ages of invoices and people.
Dates, Times and Formats
First of all, don't forget that Excel's calendar is based on every day being a normal number (ie. today (29th March) is 38440 because it is that many days since 1st Jan 1900). Times are a fraction of a day, so 38440.25 is 6am on 29th March 2005).
Only when a Date/Time format is applied to a cell is the number displayed as a date and/or time. Apply comma format to a cell showing a date or time and you'll see the underlying plain number.
Calculating a Time Period
If you want to find the length of time between two date/times, simply write a formula that will subtract the earlier date/time from the later. The answer will be the difference between the two.
Experiment with the format of your answer. If the answer is formatted as a simple number you will see the number of days difference between the two dates/times. If the answer is formatted as a date/time, the answer will not make sense because a 10 day 6 hour difference will come out as 10 Jan 1900 6am! Only if the time difference is less that 24 hours should you apply a time format.
Be careful, however, if the difference is not a whole number of days it will round in the usual way, so that 10 days difference will change to 11 at midday on the 10th day.
Date Functions
When you are making calculations with dates, you are either finding the difference between two dates/times or the difference between one date/time and now.
=TODAY(): The TODAY() function always gives you today's date, so if you have a formula that subtracts Today() from the Due date you'll see how many days before payment is due. (=TODAY()-31/03/2005)
=NOW(): The NOW() function will give you the current date and time. This is useful if you are trying to calculate differences that are less than a day.
Calculate a Person's Age
When you want to measure differences in months or years, arithmetic doesn't solve the problem because you can't aways divide the number of days by 30 or 365 to get the right answer.
Excel's DATEDIF function allows you to quickly calculate the difference in Years, Months and/or Days. (Pronounced "Date Diff" not "Dated if")
For example, let's say you need to know which employees are within five years of the retirement age of 65.
Column A lists the employee names, and column B lists their birth dates. To find the age (in years) of the first employee listed in A2, enter the following function in C2:
=DATEDIF(B2, TODAY(), "Y")
Excel will display the age in years of the employee listed in A2. The function takes leap years etc into account, and doesn't round so the employee does not become 65 when they are 64 and a half.
The three parts of the function are the first date, the second date, and the unit of time you want. =DATEDIF(date1, date2, time unit).
As you can see from the example the time unit "Y" gives you the answer in years. Other time units are:
- "m" - months
- "d" - days
- "ym" - remainder months (ignores whole years)
- "md" - remainder days (ignores whole days)
If you need to know the exact age of each employee in years, months, and days, enter the following formula in C2:
=DATEDIF(B2,NOW(),"y") &" years," & DATEDIF(B2,NOW(),"ym") &" months," & DATEDIF(B2,NOW(),"md") & " days"
This formula returns the age as a text string (e.g., 60 years, 4 months, 3 days).
As you can see calculating differences in dates and times, can become very complicated. Remember to use the correct format for your answer and the right time unit if you are using DATEDIF.