hh:mm:ss converted to seconds

kreases

Board Regular
Joined
Oct 26, 2005
Messages
111
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
Oct 26, 2005
Messages
111
Office Version
  1. 2016
Platform
  1. Windows
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

John

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

Richard
 

kreases

Board Regular
Joined
Oct 26, 2005
Messages
111
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,201
Messages
5,527,386
Members
409,759
Latest member
KCH

This Week's Hot Topics

Top