# hh:mm:ss converted to seconds

#### kreases

##### Board Regular
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.

### 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
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
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

##### MrExcel MVP
=(D1+C1-B1-A1)*86400

#### Richard Schollar

##### MrExcel MVP

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

##### MrExcel MVP
If 167:59:59 is in E1

=E1*86400

format as general

#### kreases

##### Board Regular
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

Replies
4
Views
78
Replies
3
Views
83
Replies
11
Views
56
Replies
8
Views
251
Replies
3
Views
35