up vote 2 down vote favorite
share [g+] share [fb]

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.

link|improve this question
Do you have the times in internal time format, or are they strings? – Jonathan M Sep 16 '11 at 15:35
I've set cell formatting to use hh:mm:ss time format within Excel – Logan Young Sep 16 '11 at 15:38
feedback

migrated from stackoverflow.com Sep 17 '11 at 13:31

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

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.

link|improve this answer
+1 for the Ctrl+~ - I learn something every day... (better, correct answer by the way) – chris neilsen Sep 16 '11 at 22:25
feedback
up vote 5 down vote

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.

link|improve this answer
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. – Banjoe Sep 16 '11 at 22:02
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 Sep 17 '11 at 16:28
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.