You are here:

Excel/Determine time intervals in excel given date and time data

Advertisement


Question
Date_Time_Difference
Date_Time_Difference  
Dear Nathan,

I would like some help with plotting charts (with date and time information) in Excel. My data acquisition software exports my time data in the form of "Time of Injection" and "Date of Injection". Since I am collecting data over several days, I would like to be able to plot a graph with the X-axis in hours. To determine the hours, I would need to subtract the date and time data from the first one (20:25:51, 11-07-12), which I would reference as 0.00 under Time (h). I have used the MOD function to determine the time intervals. I run into 2 problems:
1. The time interval seems to capture only 24-hour periods and doesn't update.
2. My way of determining the time intervals would not work if, for instance, the year changed. Let us say, if I were to determine the interval from 2012 to 2014 as one data point, my calculations would not capture that difference.
Would you know of a better way to do this? Also, I would like to know how to make the donation(s).
Thanks so much.

Sincerely yours,

Alfred

PS:Please find attached the image file; I could send the xls file if needed.

Answer
Alfred:

Without seeing the actual spreadsheet I am going to make the following assumption...

When calculating the difference between times you should also add in the difference between the dates.  For example, say the date is in cell A1 and the time is in cell B1.  The second date is in cell A2 and the second time is in cell B2.  Lets say this is the data...

1/1/2012   11:15 AM
1/2/2014   11:30 AM

To find the difference between the two dates & times you would use this formula:

=+(A2+B2)-(A1+B1)

Now, Excel is going to show the result as a date... which doesn't look very helpful.  It will appear like this: 1/1/1902 0:15... which is 2 years, 1 day, and 15 minutes if you look real close and ignore the century and month.  It is accurate and you can use it for calculations; however, there is an alternative formula for determining differences between two dates and times that displays more accurately.  It is called DATEDIF.

ttp://spreadsheets.about.com/od/excelsdatefunctions/ss/2012-05-12-excel-date-difference-function.htm

So I use the following function instead (which is a lot more complex, but it provides a more accurate display):

=DATEDIF(A1+B1,IF(B2<B1,-1+A2+B2,A2+B2#,"y"# & " years, " & DATEDIF#A1+B1,IF#B2<B1,-1+A2+B2,A2+B2#,"ym"# & " months, " & DATEDIF#A1+B1,IF#B2<B1,-1+A2+B2,A2+B2#,"md"# & " days, and "&TEXT##A2+B2#-(A1+B1#,"hh:mm:ss"#&" hours."

It shows the result as: 2 years, 0 months, 1 days, and 00:15:00 hours.

The formula could be split up however you like to display only certain aspects.  You could even do the following:

=TEXT##A2+B2#-#A1+B1),"[hh]:mm:ss")&" hours."

Which results in 17568:15:00 hours... which is 17,568 hours and 15 minutes.  It just depends on what you need.

Please send me an email if you need further assistance - Lotus@swbell.net #you may attach your spreadsheet).

Donations can be made to http://www.allexperts.com/ep/1059-66091/Excel/Nathan-Head.htm  
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


Nathan

Expertise


This expert requires a donation of $5 for questions answered (satisfaction guaranteed). Isn't $5 a value for this experts time?
Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (EXCEPT Visual Basic Coding/Programming and Macros, I don't answer questions in those categories).

Experience

I have been using spreadsheets since Lotus 1-2-3 was released.

Education/Credentials
Certified Public Accountant (CPA)

©2016 About.com. All rights reserved.