You are here:

Excel/Fromula for calculating interval between Date and Time

Advertisement


Question
I am trying to record elapsed time between submission and receipt of documents, accounting for Standard work hours of 8-5.

Col1          Col2          Col3          Col4        Col5          Col6
7/15/2013   9:38   7/15/2013   11:29   1:51   Mferris
7/15/2013   14:32   7/16/2013   10:37   0   Mferris

Col1= Date Submitted
Col2= Time Submitted
Col3 = Date Received
Col4 =Time Received
Col5 = Interval between submission and receipt (excluding hours ouside of the normal workday) Col 6 = Processor

If the we did not experience intervals that crossed over into a "next" day, I could easily do this.

Q's:

Do I combine the Date and Time into 1 Cell, using a text format?

Can I make this work with different cells for Time and Date.

Thanks

R

Answer
Richard,

Data and time are essentially the same thing.  Dates (and times) represent the number of days since a base date.  In windows, this is midnight on the turn of the century from 1899 to 1900 (1 Jan 1900).  so today (July 25, 2013) is 41480.   41,480 days since the base date.  

You can enter  July 25, 3013 or 7/25/2013 in a cell.  then format that cell as general and you will see the number 41480. this is called a Date Serial number.   So what is actually stored is the number of days elapsed from the base date and excel interprets that and displays the date in the format you specify (if you have used a date format for the cell (or a date/time format)).  time is just an extension of that.  If you instead enter  

July 25, 2013 8:00 AM

in a cell and then format it as general you will see:  41480.3333333333
the .3333333333  indicated 1/3rd of a day which is 8 hours.  So 8 AM is 1/3rd of a day.  
When you store time in a separate cell, you are really acting unnaturally from an excel perspective.  It is best just to entry  you date and time all in the same cell.

If you do have Time and Date entered separately as shown you can combine those using the sum function or the plus operator.

Date:  A1
Time:  B1
C1:  =A1+B1  format the cell as  mm/dd/yyyy hh:mm AM/PM   or   mm/dd/yy hh:mm for 24 hour time.

so:

Q's:

Do I combine the Date and Time into 1 Cell, using a text format?
-- No.  If you do it as text, then you won't have a date and time.  You will have a text string as a result.

Can I make this work with different cells for Time and Date?

-- you could.  Instead of referring to C1 (in my example) you could refer to Sum(A1:B1)
So if I had start date and time in A1 and B1 and end Date and time in A2 and B2 then I could get the elapsed hours with

=24*(Sum(A2:B2)-Sum(A1:B1))
and format the cell with this formula as General since multiplying by 24  (24 hours per day) will convert it into hours.  

If you are trying to take to dates (dates and times) and determine the hours on workdays between 8 AM and 5 PM, that would be very very complex.  

There is a Networkdays formula that will give you the number of workdays between two dates.  You could figure out the number of whole workdays (start Date/Time + 1) and (End Date/time -1) would give whole workdays.  Then calculate the hours on the start date/time and end date/time and add in those values to get total hours.  Of course if the total expanse will never be more than two days (and never crosses a weekend/holiday), then you could calculate  (Start time) to 5 PM) plus (8 AM to end time)  


Chip Pearson explains the storage of dates and times on his page (for reference):

http://www.cpearson.com/excel/datetime.aspx
Click on his topic index to see more information on dates and times (and everything else in Excel).

 


--
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.