3
I'm trying to work out how many seconds there are in a given time range.
So for instance if I have the following times: 16:36:00 - 16:37:38
I need to the value of 98 seconds to be placed within a 3rd cell. I've managed to get it down to 00:01:38 so far but I can't figure out how to get it broken down any further.
Any help will be greatly appreciated.
Ortund
Posted 2011-09-16T15:33:38.890
Reputation: 272
3
You want the following:
=(A1-A2)*86400
Where A1 is the larger time and A2 is the smaller time. The reason this works is that Excel stores times as fractions of a day and dates and days since 1/1/1900. You can see this for yourself by typing in a time then pressing Ctrl+~ (Control+Tilde). 12:00PM evaluates to 0.5 for example. Since there's 86,400 seconds in a day, multiplying a time by 86,400 returns the seconds.
Banjoe
Posted 2011-09-16T15:33:38.890
Reputation: 146
+1 for the Ctrl+~ - I learn something every day... (better, correct answer by the way) – chris neilsen – 2011-09-16T22:25:34.600
6
If you're dealing with strings that have the times in them, do this:
=(timevalue("16:37:38") - timevalue("16:36:00"))*24*60*60
But if you're just dealing with times in cells that are time/date formatted, say cells A1 and A2, then do this:
=(A2-A1)*24*60*60
...and make sure the cell with the formula is formatted for numbers.
Excel uses an internal time that is the amount of days since "January 0", 1900. So even though you're seeing nicely formatted times, inside excel is working with days.
Jonathan M
Posted 2011-09-16T15:33:38.890
Reputation: 191
Dates are days since 1/0/1900 and times are fractions of a day. If you put "12:00 PM" in a general formatted cell then change the format to m/d/yyyy h:mm AM/PM you'll see the default 1/0/1900 date. Press Ctrl+~ to see, on the sheet, how each cell's data is stored. – None – 2011-09-16T22:02:40.553
As Banjoe says, Excel stores date/time values internally as days, not seconds. I've gone ahead and corrected the answer. The Stack Overflow users may not log into Super User and it bothers me to have incorrect information in the answer. – Bavi_H – 2011-09-17T16:28:01.640
Do you have the times in internal time format, or are they strings? – None – 2011-09-16T15:35:47.187
I've set cell formatting to use hh:mm:ss time format within Excel – None – 2011-09-16T15:38:22.210