Excel/Determine time intervals in excel given date and time data
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.
PS:Please find attached the image file; I could send the xls file if needed.
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:
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.
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:
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