Originally Posted by

**joeu2004**
First, it appears that C19 is time in decimal hours. It is unclear whether C19 has been rounded explicitly to 3 decimal places. If not, as I assume, it is improper for you to use decimal fractions like 0.083 (really 5/60) and 0.583 (really 35/60).

It is rounded to 2 but I could change it to three, although fractions is probably better.

Second, it is unclear why you use 2.xxx in your formula, if you simply want to ROUNDDOWN or ROUNDUP based on fractional hours. I will rely on your verbal description.

The 2 part is unimportant. This excel sheet is for invoice purposes. We offer 5 minute grace periods on our charging rate and we charge by the half hour. So, for example, if we work for 3 hours and 4 minutes we want to only charge for 3 hours. If we work for 3 hours and 10 minutes we want to charge for 3.5 hours and if we work for 3 hours and 38 minutes we want to charge for 4 hours.

Ostensibly, we would like to write:

=ROUND(L19*(INT(C19)+IF(MOD(C19,1)>35/60,1,IF(MOD(C19,1)>5/60,0.5))),2)

MOD(C19,1) returns the fraction of an hour. INT(C19) is the same as ROUNDDOWN(C19,0). Adding 1 is the same as ROUNDUP(C19,0).

However, if L19 is 100 and C19 is =12+5/60, for example, that formula results in 1250 instead of 1200 as intended. (Obviously, 5/60 is not >5/60.)

That is due to internal (binary floating-point) arithmetic anomalies. I could explain further, if you wish.

The following formula is more reliable:

=ROUND(L19*(INT(C19)+IF(C19>INT(C19)+35/60,1,IF(C19>INT(C19)+5/60,0.5))),2)

It looks like this formula could work! Thanks!

## Bookmarks