[Solved] Decimal number convert to time format

[baldysm]

Hi all:

I need to convert a decimal number to time.

Searching through the forum, I found thus formula:

=TIMEVALUE(TEXT(TRUNC(G3/J3); "00") & ":" & TEXT(((G3/J3)-TRUNC(G3/J3))*60; "00"))

If (G3/J3) provides the value of 11.40175425099, I get the result of 5:42:00, which is almost correct. The correct result is 5:42:03.

I'm pretty noob, and I played with the formula for awhile. I couldn't get it right.

How do I change the formula to correctly display the seconds?

Thanks!

[Villeroy]

Never work with text in a spreadsheet when it is possible use numbers.
If 11.40175425099 is meant to be an amount of hours, then hh/24 gives the amount of days. Since all spreadsheets work with time scales in unit "Days" you can format HH/24 as time and get the desired display 11:24:06 of the value 0.47507 (0.47507... of one day).

off topic:
=TIME(hh;mm;ss)
gets a time from 3 numbers which is perfectly equivalent with:
=hh/24+mm/1440+ss/86400
converting all 3 numbers hh,mm,ss to days and adding them.

[baldysm]

Still over my head.

I understand that it makes more sense to take the decimal number, divide by 24 to get hours take the remainder, divide by 1440 to get minutes, take the remainder, divide by 86400 to get seconds. The remainder of that is insignificant for me. That seems alot simpler than the equation I gave.

I see where your going, but I'm doing something wrong.

I use the formula:

=(G3)/24+(G3)/1440+(G3)/86400

where G3= 11.40175425099. It gives the result of 11:35:42, which isn't right, but I'm moving in the right direction.

Can you nudge me in the right direction?

Thanks

[Villeroy]

I still can not see what the number in G3 is supposed to be? And what is the J3 in your original posting?
If G3 is meant to be an amount of hours and you want to see kind of "time value", simply convert hours to days =G3/24 and format the day-fraction to your liking.

There are no dates and times in a spreadsheet. There are only numbers which represent a point on a time scale. The point zero on the assumed scale is 1899-12-30 00:00:00 and the scaling is in unit "Days".
Every number can be shown as date-time, every date-time is a number. For instance, =PI() [3.14159...] formatted as date-time shows 1900-01-02 03:23:54 (day number 3 plus about 1/7th of a day). Formatting does not influence any calculation result. You get the 3.14159 back when you change the formatting without any conversion taking place nor modification of a single value. 1900-01-02 03:23:54 is just another way to display the result of the PI() function.

[jamesptrk5]

baldy - It's much easier than you think, just: (G3 / 24). Just divide the numeric value, which expresses hours, by 24, since OO-Calc treats all time values as units of days. Format the result as HH:MM:SS. Your desk calculator shows you the dividend of G3/24 is about 0.475073 - days that is; and formatting the cell as HH:MM:SS expresses that value into its hours, minutes, seconds as 11:24:06. You can easily format it to include decimal portion of seconds too - HH.MM.SS.000

Your TIMEVALUE() function is a valid approach, if a bit of a bulldozer. It gave you only HH:MM:00 because you fed it just
two TEXT() strings with the hours: TEXT(TRUNC(G3/J3); "00") and minutes: TEXT(((G3/J3)-TRUNC(G3/J3))*60; "00"). If you're determined, append one more colon then the seconds, calculated as:
TRUNC(((G3/J3-TRUNC(G3/J3))*60 - TRUNC((G3/J3-TRUNC(G3/J3))*60))*60) --- this just removes the 11 hours and then the 24 minutes, leaving the 6.3153... seconds

Again, by desk calculator: 11.4017525099 hours /24 = 0.475073 days.
Set aside the 11 full hours: 11.4017525099 - 11 = 0.4017525099 hours x 60 = 24.10515 minutes.
Set aside the 24 full minutes, and remains 0.10515 minutes x 60 = 6.315 sec.

If you want lots of examples of arithmetically manipulating values of miles, duration times, miles-per-hour, and minutes-per-mile. I can send you some.

[Cat101]

Okay, I just had to do this today for a payroll system..
Old system did hours & minutes - new system did fractions..

Ohhhh, how simple..
I divided the cell by 24 (P2/24)
Then formatted the cell as [HH]:MM:SS

Also, since the cell already had a formula, I did it all as a conditional format.
And established a style that used the format of [HH];MM;SS

Ohhhh I love this forum, ya make life sooo much easier..
Thank you!!!