Excel - Formula to calculate difference in hours between two dates

Calculate Hours Between Two Dates and Times in Excel
 by GREGORY on DECEMBER 10, 2010

 Recently I was asked 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

 Dates in Excel

 When you type a Date into Excel, you may never see the underlying number, like 40519, but it’s there nonetheless. This a date serial number and it makes Date calculations easy. You 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.)

 Time in Excel

 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. Time Fraction Calculation

 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 2 Dates and Times

 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) Finding the number of hours between two date/times 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. Calculate Hours Between Two Dates and Times 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.

 Extract Time with the MOD Function in Excel by GREGORY on NOVEMBER 1, 2012

 I had a reader comment on my last post about how to extract time from a date-time number using the MOD function. Simple really.

 The syntax is MOD(number,divisor). The MOD function returns the remainder after number is divided by divisor. A simple example is MOD(5,2), which equals one (1). It works like this: five (5) divided by two (2) equals two (2), with one (1) left over. All numbers are evenly divisible by one (1) so the MOD function returns any fractional part when the second argument is one (1).

 In the screen shot below, cell C2 has the Date-Time number: 10/8/12 6:28 PM. It has an underlying serial number: 41990.7698, which you can see in cell C3 with General formatting.

 MOD Function Time Extract Using the formula =MOD(C2,1) you can see the result in cells D2 and D3, with different cell formatting. Extracting the Time value from a Date-Time value is simple with the MOD function.