Ask Your Question

Issue with calc hours with mins converting to hours with decimal

asked 2021-02-19 23:06:59 +0200

RonBaker gravatar image

updated 2021-02-22 20:52:18 +0200

image description

the value reads 48.233 when value should be 48.25

edit retag flag offensive close merge delete


Why would 15 mins account for 0.5 of an hour ? , 15 mins are a quarter therefor 0.25 take a few seconds off and you have 0.233

gkick gravatar imagegkick ( 2021-02-19 23:36:48 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2021-02-19 23:27:06 +0200

keme gravatar image

updated 2021-02-21 08:52:57 +0200

Should be 48.25, really, not 48.50.

48,233 is around 1 minute off. Looks like rounding error to me. Most likely, the times recorded are not exactly on the minute mark as displayed.

Instead of extracting days, hours and minutes to build the hour total, you could just use =H5*24 and format explicitly as decimal.

To eliminate the rounding error, try rounding in the H5 formula:

edit flag offensive delete link more



eliminate the rounding error, try rounding in the H5 formula

Not sure what is this trying to achieve. The time difference (duration), if formatted properly, has one of duration formats like [HH]:MM, with square brackets meaning not only "do not divide modulo 24", but also "round the result to the least shown part". This gives "15 minutes" for durations ending with e.g. "14 minutes 48 seconds", unlike in truncating "wall clock" formats HH:MM, which give "14 minutes" for times ending with "14 minutes 48 seconds". Where the rounding is needed is IMO in I5.

Mike Kaganski gravatar imageMike Kaganski ( 2021-02-21 09:20:02 +0200 )edit

This worked out. Thank you so much.

RonBaker gravatar imageRonBaker ( 2021-02-22 20:53:17 +0200 )edit

answered 2021-02-22 23:57:42 +0200

newbie-02 gravatar image

updated 2021-02-23 00:16:49 +0200

would suspect rounding in H5 is 'for display only' and the value taken for downstream calculations has some deviation,

formula applied to a 'keyed-in' value of 48:15 works correctly,

would like to have formula or 'real value' of I5 ...

assume calculated from 'G5-F5'? subtraction of similar values, operands 'big' relative to 'small' result -> 'cancellation', tried, couldn't repro, and res. should be rather too big than too small? inaccurate date-time values? old calc ver. with calculation errors?

just tried keyed-in values between 48:14:59,50 and 48:14:59,99 formatted to [HH]:MM, effect as in OP

48:14:59,49 is rounded to a display of 48:14 ... understand who wants ...

P.S. 'solved marks' and 'likes' welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the "^" above it if you 'like' the answer,
"v" if you don't,
do not! use 'answer' to add info to your question, either edit the question or add a comment,
'answer' only if you found a solution yourself ...

edit flag offensive delete link more


Date and time formats always display -whatever they display at all- rounded down, and this is constitutive for their functionality. Remember: they are made for points in flowing time with an exactly specified time zero (1899-12-30 00:00:00). What would you tell me if I made display a cell formatted DDDD "Tuesday" if the value is 44250.49866872690, but "Wednesday" for a value of 44250.5012800110? To show "Tuesday" in both cases is the way calendars work, and basically in the same way work time-pieces, if "digitalized" at all.

Lupp gravatar imageLupp ( 2021-02-23 12:05:20 +0200 )edit

@Lupp: your statement to always round time values - to the value of the 'already completely elapsed units' one is talking about would be understandable and logical in itself, but calc treats fractions of seconds differently,
I seem to remember that it is an old discussion to round fractions of seconds - different from other time values - and that this has led to much bigger deviations and irritations elsewhere,
anyway, if you have done it this way for a while, it becomes a 'standard' to which some users got used to and thus very hard to change, even if you should do it for 'consistency reasons', and maybe wanted to do it,
anyway, for once I didn't want calc to change anything - because I'm not through with the topic yet and don't know a better suggestion, I just wanted to explain in the answer what the OP's irritating ...(more)

newbie-02 gravatar imagenewbie-02 ( 2021-02-23 20:51:47 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-02-19 23:06:59 +0200

Seen: 75 times

Last updated: Feb 23