Calculating the Difference Between Two Dates in Excel
If need to know the number of days between two dates, that is very easy to do in Excel. Just subtract one date from the other. For example if cell A2 has an invoice date in it of 1/1/2015 and cell B2 has a date paid of 1/30/2015, then you could enter use the formula =B2-A2 to get the number of days between the two dates, or 29. Excel may automatically format the cell with the formula in it as a date, which means instead of seeing 29, you’ll see 1/29/1900. But all you have to do is set the formatting for that cell to General or to any number format [right-click > choose Formatting] to make the result show as 29.
If you want to know the number of months or years between two dates, things get trickier. The best way is to use the DATEDIF[] function. You won’t find this one in the function wizard so you will have to remember how to type it in yourself. The explanation I heard for this is that it isn’t a standard Excel function, it is only included for backward compatibility with Lotus 1-2-3.
The format for the function is DATEDIF[start date,end date,unit].
Start date and end date are self explanatory. Unit meaning the unit of measure, can be “D”, “M”, or “Y” for day, months, or years respectively. There are 3 additional more unusual options that are explained in the example below.
The table below shows sample formulas using each of the unit options. Column D shows the formula that was actually used in column C to get the result shown in column C. Note that the answer only shows complete units. For example the formula on row 4 shows 5 years, even though the two dates are a little more than 5 years apart. The function does not round. So if the two dates had been 5 years and 364 days apart, the answer would still be 5.
| A | B | C | D | E | |
| 1 | START DATE | END DATE | DIFFERENCE | FORMULA | UNIT – DIFFERENCE CALCULATED IN.. |
| 2 | 01/01/2010 | 02/18/2015 | 1874 | =DATEDIF[A2,B2,”D”] | Days [gives same result as =B2-A2] |
| 3 | 61 | =DATEDIF[A2,B2,”M”] | Months | ||
| 4 | 5 | =DATEDIF[A2,B2,”Y”] | Years | ||
| 5 | 17 | =DATEDIF[A2,B2,”MD”] | Days, ignoring month and year [days from 1st to 18th] | ||
| 6 | 1 | =DATEDIF[A2,B2,”YM”] | months, ignoring year [months from Jan to Feb] | ||
| 7 | 48 | =DATEDIF[A2,B2,”YD”] | days, ignoring year [days from 1/1 to 2/18] |
This entry was posted on February 19, 2015, 10:33 am and is filed under All, General Tips. You can follow any responses to this entry through RSS 2.0. Both comments and pings are currently closed.
Recently I was asked how to subtract time in Excel [time difference] or how to calculate the number of hours between two points in time on different days. Since this was in a reader comment, I gave a brief answer that requires a fuller account here.
Dates and Times are all part of the master plan in Excel. Once you “get” the fundamentals, the rest is just icing on the cake.
A Date value in Excel
looks like this: 40519
A Time value in Excel looks like this: 0.58333
Cell formatting changes how you see these numbers.
The Date: 7 Dec, 2010
The Time: 2:00 PM
How to Calculate Time Between Dates in Excel or the Duration Between Two Dates
If you want to calculate time between dates in Excel or the duration between two dates, you need to understand what they mean first. When you type a Date into Excel, you may never see the underlying number, like 40519, but it’s there nonetheless. This is a date serial number and it makes Date calculations easy.
You might ask, “Why is this such a weird-looking number?”. Well, the Excel folks started a numbering system with Dates. In Excel for Windows they gave 1 Jan, 1900 the serial date number of 1, then continued numbering until this day and beyond. So serial number 40519 represents 7 Dec, 2010.
In Excel for Mac they started numbering Dates beginning with 2 Jan, 1904. [don’t ask] So the serial date 40519 represents 8 Dec, 2014 [Actually it’s known as the 1904 date system. To be clear, Macs can change Excel settings to use the 1900 date system].
How to Subtract Time in Excel [Time Difference]
When you type 2:00 PM into a cell in Excel the underlying value is a fraction, but Excel interprets this as a time serial number and formats the cell accordingly.
Try typing 0.25 into a blank cell, then change the cell formatting to a TIME format, and you’ll get something like 6:00 AM.
As an aside, you can calculate this fraction for any time value during the day by taking the total number of seconds that have passed from midnight until your time value and dividing by 86,400 seconds in a day.
Dates and Times Together
In Excel the unit of time is “the Day,” a key fact to know. You’ll notice that Dates are integers, and Time is a fractional number. You can add the two together to get a Date/Time format.
So adding a Date serial number, like 40518, to a Time serial number, like 0.25, gives us 40518.25. Formatting the cell holding this value using “d mmm, yyyy h:mm AM/PM” will show 6 Dec, 2010 6:00 AM.
You can also enter something like 7 Dec, 2010 2:00 PM into a cell and Excel will recognize this as a Date/Time format. However, if you change the cell formatting to General, the underlying number is 40519.05833.
So hopefully by now you can see that subtracting two Date/Time formatted numbers can be done mathematically. Subtracting 6 Dec, 2010 6:00 AM from 7 Dec, 2010 2:00 PM is done by Excel “underneath the hood” as 40519.05833 – 40518.25 and the result is 1.3333.
Calculating Hours Between Two Dates in Excel
If we recall that the unit of time is “the Day,” this value represents 1-1/3 days of time. Since there are 24 hours in a day, converting to hours is a simple multiplication 24 * 1.3333 = or 32 hours. [24 * 4/3 to be more precise]
Time Between Two Times / Dates
Finding the number of hours or the time between two times/dates is simple, just subtract the start date/time from the end date/time and multiply the result by 24 hours.
If you want to enter the dates and times separately [which is loads easier than typing in a date/time in one cell] then add the date/times together.
Hours = [[End_Date+End_Time]-[Start_Date+Start_Time]]*24
Here’s a look at a typical worksheet designed to calculate the hours between two dates.
As you can see, the formula for Hours, in cell F2, shows in the formula bar. And row 3 contains General formatting so you can view the date/time serial numbers for row 2.
Change the formatting for cells B2:E2 to match what you normally use for Date and Time data entry.
The NETWORKDAYS Function
People also asked how to calculate the time between days but without taking into account weekends or specified holidays. The NETWORKDAYS function will help you to calculate the time period by excluding weekdays and specified holidays.
The formula looks like this: =NETWORKDAYS[start_date, end_date, [holidays]]
Like in the previous case the start_date and end_date are required arguments, yet the argument [holidays] is optional. You can use this argument to specify which days should be excluded.
This time I used two random dates which contained two weekends and I added two desired dates under the holidays argument just to show you how it works.
If you need to add some weekends you will have to add one more argument [weekends] and use =NETWORKDAYS.INTL[start_date, end_date, [weekend], [holidays]]
After you calculated the days by using the NETWORKDAYS formula, to calculate the hours you can simply multiply the given number with the number of hours you need for as long as they stay the same. Also, do not work harder by looking for complicated formulas to calculate simple things.
What else you should know:
- If the start date is later than the end date the formula will generate a negative number.
- The NETWORKDAYS formula will also include the start and the ending dates in the calculations, due to this if you have the same date the value returned will be 1.
If you want to find more formulas regarding the date and time feel free to check the following list of functions.
I hope this article helps you to better understand how to calculate hours between two dates in Excel. You can exercise what you learned today by calculating the time left until your next birthday, to do this you can also use the =Today function or the =Now function.
If you found this article helpful, please feel free to check our other articles about tips and tricks in Excel. We also have a dedicated section for details and formulas about dates and times in Excel.