I am creating a route card for an upcoming thru-hike. In estimating my time for each day, I’m using the Naismith Rule adjusted for my fitness/ability and the result is a time in decimal format (i.e., 3.47 for 03 hours, 28 minutes, 12 seconds). I would like to display this decimal duration in time format (i.e., 03:28:12–or 03:28 as I don’t really care about seconds). I tried using =TIME and =HOUR but neither provide an accurate result.
Any ideas?
On the toolbar the second section is “formats”. Click the “more formats” button (looks like “123” with a little arrowhead) and pick “time” from the dropdown menu.
I know how to format for hours, but I don’t know how to accurately convert decimals to hour:minute. =TIME requires 3 different values and I am only generating one value. =HOUR converts 3.47 to 244:00.
This doesn’t quite work, since Google (like most spreadsheet programs) converts numbers into time at a rate of 1 = 1 day. So 3.47 would get translated into 3.47 days, or a little over 83 hours. Also, “Time” gives you a time of day with an AM/PM attached to it. “Duration” is more of what you want.
The easiest way to work around this is to just divide the results of the Naismith Rule by 24. So the cell would contain =[Naismith Rule formula]/24, and then you reformat the cell as “Duration”. The formatting will take care of converting the fractional part of the hour into minutes and seconds.
I get HOUR(3.47) == 11.
I think the standard “time interval format” for Google sheets is fractional days, not fractional hours. So as far as it is concerned 3.47 means “approximately 3 and a half days”. But if you divide everything by 24 (to convert to fractional days) you get the expected answers.
e.g. HOUR(3.47/24) == 3, MINUTE(3.47/24) == 28, SECOND(3.47/24) == 12, so 3.47 hours == 3h 28m 12s which looks about right.
Boom! 
I was using duration for the formatting but couldn’t quite figure out how to get the formula to work. /24 was the magic solution. Much love for **MikeS **and SDMB!
The reason for inaccuracy was each field of TIME( HH,MM,SEC) will use the integer value only.
suppose your value was in X33 ,
=TIME(X33,0,0) only gives the hours rounded down, but as a decimal fractions
=TIME(0,X33*60,0) only gives correct hours and minutes rounded down … time( ) does not give any days at all. (so its between 0 and 1… )
but not the arguments of time can’t go over 32656, so don’t try to shove a whole day into the seconds field !
If you use /24…
=X33/24 will preserve the number of days , so it can contain 1,244, and however many of days since 1970. that means if you do maths,you get days included…wednesday 10 am minus tuesday 9am is 1 day, 1hour… Note that the 1 day value may be stored and hidden by your formatting. if you show hours only… you can get what looks correct in formatted view, but causing a long value to be used later.
= remainder(x33,24) …now you get only the decimal fraction of the day, and no days.so as to be sure that its a time of day VALUE only, so you can do maths with times from different days