Time string to Time Format

[djeddie]

Hey All,

I have exported fields (time in seconds) from crystal reports to excel. I believe the field type is integer, so after exporting to excel i cant figure out how to make hh:mm:ss out of them.

I tried to change the format of the cells to [ss] but it doesnt work.

How can i use the existing number as seconds so i can then convert them to [hh]:mm:ss ??

Cheers

Eddie

 

[AnotherHiggins]

Have a look at faq68-5827 (Why do Dates and Times seem to be so much trouble?) for an explanation on how Excel deals with dates and times.

Here I'll just point out that dates are integers based on the number of days since 1/1/1900 (today, Sept 23 2005, is stored in Excel as 38618 because it has been 38,618 days since 1/1/1900). Times are stored as a fraction of a 24 hour period. 6 AM is .25, noon is 0.5, 6 PM (18:00) is 0.75, etc.

Add to that knowledge that there are 60 seconds in a minute, 60 minutes in an hour, 24 hours in a day.

So to convert the integer number of seconds to a fraction of a day, you would take
[blue]120 seconds / (60 Sec's per min * 60 min's per hour * 24 hours per day)[/blue]
=
[blue]120/86400[/blue]
=
[blue]0.001388889[/blue]
Which, when formatted properly, =
[blue]00:02:00[/blue]

It sounds like you are dealing with time durations. If so, they might extend over 24 hours at some point. So to take that into consideration and still have your answer display properly, you would format the cell like this:
[ul][li]go into format > Cells[/li]
[li]On the number tab, Select Custom from the list on the left[/li]
[li]In the Type box on the right, type [COLOR=black white][hh]:mm:ss[/color][/li]
[ul][li](The square brackets around the hours forces Excel to display the actual number of total hours, even if greater than 24 - again, see the FAQ cited above for details)[/li][/ul][/ul]

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.