Guide to Simple Date Operations in Microsoft Excel
Calculate the number of days between two dates
Calculate the number of work days between two dates
Calculate the number of months between two dates
Calculate the number of months between two dates in different years
Calculate the number of years between two dates
Why don’t my results look right and end in the year 1900?
Calculate the number of days between two dates
In Excel finding the number of days between two dates is as straight forward as subtracting the cell with the oldest date from the cell with the most current date. For example in the spreadsheet below to find the difference between the two date you would enter the formula =A2-A1 into cell A3.
If your result appears as a date or you want more information about how Excel stores dates see the note below.
| A | B | |
| 1 | 25/03/2009 | |
| 2 | 05/04/2009 | formula: |
| 3 | 11 | =A2-A1 |
Calculate the number of work days between two dates
Finding the number of work days between two dates is made easy using Excel’s built in NETWORKDAYS function. In the example below find the number of work days between the two dates in A1 and A2 by entering the formula =NETWORKDAYS(A1,A2) into A3. When using the NETWORKDAYS function send your most current date as the first argument and your oldest date as the second argument.
| A | B | |
| 1 | 25/03/2009 | |
| 2 | 05/04/2009 | formula: |
| 3 | 7 | =NETWORKDAYS(A1,A2) |
Accounting for Holidays
This formula will also take a third argument. This third argument is a date or number of dates that are holidays or non-work days. For example if cell B1 contained a holiday your formula would read =NETWORKDAYS(A1,A2,B1). If column B contained a number of holidays in cells B1-B8 your formula would read =NETWORKDAYS(A1,A2,B1:B8).
Calculate the number of months between two dates
Finding the number of months between two dates in Excel involves first converting your dates to month values then subtracting you oldest date from you most current date. In the example below the date in A2 is first converted to a numeric month value using the MONTH() function then A1 is subtracted from it after also being converted using the MONTH() function. This calculation uses the numeric values of the months only and would break down if the date range crossed and annual boundary, see the solution next.
| A | B | |
| 1 | 25/03/2009 | |
| 2 | 05/04/2009 | formula: |
| 3 | 1 | =MONTH(A2)-MONTH(A1) |
Calculate the number of months between two dates in different years
Here is a more robust formula for finding the number of months between two dates across multiple years. The formula begins identically to the month range calculation above then appends the year range calculation (below) with year multiplied by 12 to convert years to months. This formula is also safe to use if your monthly range is within the same year. When applying this formula A1 contains your oldest date and A2 contains your youngest date.
| A | B | |
| 1 | 25/03/2009 | |
| 2 | 05/04/2010 | formula: |
| 3 | 13 | =MONTH(A2)-MONTH(A1)+(YEAR(A2)-YEAR(A1))*12 |
Calculate the number of years between two dates
Finding the number of years between two dates in Excel requires you to apply the YEAR() function to the dates then subtract the oldest from the youngest. For example find the number of years from A1 to A2 using YEAR(A2) and subtracting YEAR(A1) to get your final range.
| A | B | |
| 1 | 25/03/2009 | |
| 2 | 05/04/2011 | formula: |
| 3 | 2 | =YEAR(A2)-YEAR(A1) |
Why don’t my results look right and end in the year 1900?
If you completed the first example and your result was 11/01/1900 instead of 11 don’t worry. You need to change your cell formating from date to number. Likewise users of Mac Excel may have ended up with 12/01/1904, same solution. To fix this right-click on your cell and choose Format Cells or navigate to Format -> Cells. From the window that appears click Number and choose 0 as the number of decimals.
Why did this happen?
All dates in Excel as stored as a count of the total number of days since January 1, 1900 (or January 2, 1904 for Mac Excel). When you see 11/01/1900 Excel is simply applying its date formating to the number 11. Storing dates as a serial number like this allows date functions to be easy and efficient. Think about how much more difficult it would be to find the total number of days between two given dates if they were not stored as serial numbers.
Tags: calculate, database, excel, microsoft office