Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert timespan d:hh:mm:ss to number
Hi,
I have a field that is imported from Siebel to CSV in the format d:hh:mm:ss. I need to convert this back to seconds. I have tried changing the format of the cells but this does not work. Can anyone help? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert timespan d:hh:mm:ss to number
Assuming the date is text, try this UDF:
Function changeit(r As Range) As Long Dim nv As Long s = Split(r.Value, ":") nv = 24# * 60# * 60# * s(0) nv = nv + 60# * 60# * s(1) nv = nv + 60# * s(2) nv = nv + s(3) changeit = nv End Function This can also be performed directly on the worksheet, without any VBA: =LEFT(C4,1)*24*60*60 + MID(C4,3,2)*60*60+MID(C4,6,2)*60+RIGHT(C4,2) -- Gary''s Student - gsnu200760 "Enigo" wrote: Hi, I have a field that is imported from Siebel to CSV in the format d:hh:mm:ss. I need to convert this back to seconds. I have tried changing the format of the cells but this does not work. Can anyone help? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert timespan d:hh:mm:ss to number
It's probably text.
Suppose your data is in column A and the columns B:F are free. DataText to columns, delimited, check Other and use the colon symbol Now you should have days in A, hours in B, minutes in C and seconds in D. In E1: =((A1*24+B1)*60+C1)*60+D1 This is seconds as a number. If you require seconds as real Excel time: in F1: =E1/24/60/60, Format Custom as [s] -- Kind regards, Niek Otten Microsoft MVP - Excel "Enigo" wrote in message ... | Hi, | | I have a field that is imported from Siebel to CSV in the format d:hh:mm:ss. | I need to convert this back to seconds. I have tried changing the format of | the cells but this does not work. | | Can anyone help? | | Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert number to % using only custom number format challenge | Excel Discussion (Misc queries) | |||
Convert a number formatted as text to a number in a macro | Excel Programming | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
not able to convert text, or graphic number to regular number in e | Excel Worksheet Functions | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) |