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

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

It's probably text.
Suppose your data is in column A and the columns B:F are free.
Data>Text 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" <(E-Mail Removed)> wrote in message news:91F288F8-07AC-4236-9659-(E-Mail Removed)...
| 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