Monday, 1 February 2010

Excel: Calculate the difference between two dates

Let me just get this off my chest. "I don't do math in my head." I really hate math, but Excel is a dream as it makes math so easy. I used to have to really think how old my family members are. It is a really bizarre thought process for me, so I won't bore you with that. Instead, I'll show you how I made an Excel spreadsheet that will tell me. I have since used it to calulate days, months or years between two dates (such as time between start and end dates of a project).
Here is the formula's syntax. (BTW, don't go looking this up in the Help section as it is a "secret" formula.)
=DATEDIF(Earliest_Date, Latest_Date, UnitOfMeasure)
Here's how it works...

1st argument: A2 is the cell reference for the early date.

2nd argument: For the late date, I used the TODAY() formula so it is dynamic for whenever I open the spreadsheet to have the current date.

3rd argument: The unit of measure can be either "Y" for years, "M" for months or "D" for days, depending on how you want the results to be calculated.
If you prefer to calculate the WORKING days (excluding weekends and holidays) you can use the NETWORKDAYS formula.
=NETWORKDAYS(start_date, end_date, holiday_range[optional])
Here it is in action...

No comments:

Post a Comment

Note: only a member of this blog may post a comment.