Format time properly?

[Geuis]

I have a report that is generated for the total amount of time that an employee works. The number is imported in raw # of seconds.

I am having a huge problem properly converting that # into a normal hh:mm:ss format for displaying the number of hours, minutes, and seconds properly.

For example:

Data Sheet:
(G3) # of seconds
47017

Formula Sheet:
(A1) #human readable time
=TEXT(TIME(0,0,VLOOKUP(D11,Data!B:N,6,FALSE)),"hh:mm:ss")

The formula =TEXT(... has been working fine for numbers under 12000. For some reason when we plug bigger numbers of seconds the formatting goes crazy and just says #NUM.

I would really appreciate some help! Thanks.

[AlfD]

Hi!

Try turning the number of seconds into a fraction of a day and using format to show it as hh:mm:ss.

Number of seconds in A1


In B1 put = A1/60/60/24. (Or =A1/86400 to save overheads).

Now for B1 format>cells>number>custom and put [hh]:mm:ss in the type as: box.
The [ ] deal with situations where the amount exceeds 24 hours. May be a "don't care" for you.

Alf