Whole numbers set in seconds to Hour Format "00:00:00"

[Newbie987654321]

Hi. I need your to convert whole numbers in seconds in a time format. May I know an easier way to do this with this format "hh:mm:ss"? I tried to manually calculate. 3600 (secs) is already equivalent to 1 (hr), but if I format it to time, I can't seem to find "1:00:00" there. It appears as 24:00:00. Please help.

 

[Colo87x]

Hi

I found when coverting whole numbers to time format simply create another column (Column B) and use this formula in the new column =(A1/24), this should then give you the desired time format you're looking for, let me know if this works?

 

[DRSteele]

Welcome to the forum.

Date & time formatting and maths can be confusing. Perhaps if you watch this educational video, it would be easier than my writing a tract to explain it all.

https://www.youtube.com/watch?v=5jnQCQAzDWo&list=PLrRPvpgDmw0kMy6GdNvsecPSOP5Tc_ydL&index=30

 

[jtakw]

Hi,

If you're trying to convert Seconds to Hours, you can do this, Column B Cells formatted as Time (either 12 or 24 Hour format), if you may have More than 24 hours, than Custom Format as [h]:mm:ss

Book1
	A	B	C
1	3600	1:00	1:00:00
2	9876	2:44	2:44:36
3	86400	0:00	24:00:00
4	900002	10:00	250:00:02
5
6		Formatted as 24 hour	Custom formatted [h]:mm:ss
Sheet28

Cell Formulas
Range	Formula
B1	=A1/3600/24
C1	=A1/3600/24

 

[joeu2004]

@Newbie.... If time in decimal seconds is in A1 (e.g. 1234 for 1234 seconds), ostensibly use A1/86400 formatted as [h]:mm:ss .

However, it would be prudent to use =--TEXT(A1/86400,"[h]:m:s") formatted as [h]:mm:ss , especially if you might compare the result with time that you enter manually.

The use of TEXT should avoid anomalies due to binary floating-point arithmetic. The double negate ("--") returns numeric Excel time.

The use of [h] instead of h is a "good programming practice" when formatting calculated time. It allows for the possibility, no matter how unlikely, that the result is 24 or more hours.

If the original decimal seconds are accurate to milliseconds, use --TEXT(A1/86000,"[h]:m:s.000").

And do not use TEXT at all if the original decimal seconds are accurate to greater precision (e.g. microseconds).

 

[Newbie987654321]

Hi,

If you're trying to convert Seconds to Hours, you can do this, Column B Cells formatted as Time (either 12 or 24 Hour format), if you may have More than 24 hours, than Custom Format as [h]:mm:ss

Book1
	A	B	C
1	3600	1:00	1:00:00
2	9876	2:44	2:44:36
3	86400	0:00	24:00:00
4	900002	10:00	250:00:02
5
6		Formatted as 24 hour	Custom formatted [h]:mm:ss
Sheet28

Cell Formulas
Range	Formula
B1	=A1/3600/24
C1	=A1/3600/24

Hi Guys. Im good with this. I guess i was looking for the formula 3600/24. Thank you for all of your help guys!

 

[Newbie987654321]

I am good with this guys. Thank you so much for all of your help. You are awesome!

 

[jtakw]

Hi Guys. Im good with this. I guess i was looking for the formula 3600/24. Thank you for all of your help guys!

You're welcome, welcome to the forum.