Need assistance converting times stored in date format to number format.

One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it. Any help doing so would be greatly appreciated.

Thanks.

Edit: Further information. If I highlight the field the edit box displays |12:09:16 AM|

If the original value is in A1 this might do what you want. I can't be sure as you don't supply any actual values in a worksheet.

Formula:

Please Login or Register  to view this content.

Hi and welcome to the forum

excel treats time as a decimal of 1 (day), so 9 minutes and 16 seconds is actually 0.00643460648148148 if you format it to general. Not sure if that will help you.

If you have the decimal value as you show then with the value in A3

Formula:

Please Login or Register  to view this content.

Using 0.00643460648148148 this works out to be 555.95000 seconds or 556 seconds to the nearest second formatted as GENERAL.

There is no way that I know of that will allow you to select the whole worksheet and apply a formula to it.

If the values are in a column, enter the formula at the top of a neighbouring column and copy down the column. Or if the values are in a row, enter the formula in a row and copy across.