hh:mm:ss converted to seconds

[kreases]

Hi,

I have a spreadsheet for server downtime and I need to convert the downtime from hh:mm:ss to seconds, I have been using a formula but found this doesn't work in certain circumstances like the one below:

down date downtime up date uptime hh:mm:ss seconds
18/09/2006 00:00:00 24/09/2006 23:59:59 167:59:59 0

The seconds column should have calculated 604799 seconds.

I would appreciate any help, thanks.

John.

 

[Richard Schollar]

Hi John

If you have start and finsih times, then:

=(Finish-Start)*86400

will give a decimal representing the number of seconds. Start and finish can be references holding these dates/times ie:

=(B1-A1)*86400

Is this what you wanted?

Richard

 

[kreases]

Richard,

I'm not really sure what that formula does but the start and finish times are across different days and your formula doesn't seem to take account of any day change.

In the example I gave you the downtime total is 167h 59m 59s which appears in a cell as 167:59:59, I want this converted into seconds only.

Regards,

John

 

[fairwinds]

=(D1+C1-B1-A1)*86400

 

[Richard Schollar]

John

My apologies - I thought your start and finish values were inclusive date&times. It looks like Fairwinds' has provided a solution.

Richard

 

[barry houdini]

If 167:59:59 is in E1

=E1*86400

format as general

 

[kreases]

Thanks Guys,

I have used the method mentioned by Fairwinds but thanks Richard and Barry for the inputs, I have no doubt I will come back with another problem in the months to come.

Regards,


John