Formula converting hours into working days showing error with specific number of hours

[BlissC]

I have a spreadsheet that includes a "converter" where the user can enter the length of their work day and their leave entitlement in hours, which Excel then displays in work days, hours and minutes. As I'm just about ready to launch the spreadsheet on colleagues, I thought I'd just do some final checks and double-check the converter with different lengths of work day and leave entitlements...and discovered a problem. The formula works with all permutations of day lengths and leave entitlements, except for one - which is rather unfortunate because it just happens to be the most common working day length and leave entitlement!!

This is the formula I'm using: =IFERROR(INT($I10/I$8)&" days "&TEXT($I10-INT($I10/I$8)*I$8,"hh:mm"),"ERROR")

Cell I8 is the length of the work day in hh:mm and I10 is where the number of hours are entered, again in the format hh:mm.

The only values I'm getting an error with are with a day length of '7:24' and the number of hours '185:00'. It should show a result of 25 days, 00:00

With 180 hours it works fine, with 181:00, 182:00, 183:00, 184:00....186:00, 187:00, 188:00... All work fine, except with 185:00! 185:00 does work with other day lengths though. I can't for the life of me work out why this particular combination of numbers is causing an error. If anyone can shed any light on why it's exhibiting this strange behaviour with the 7:24/185:00 combo, I'd be most grateful.

[XLent]

rounding related -- and XL can't show negative time (the 185:00 will generate a -0.000000000000001)

given you don't need to worry about subtraction I would be inclined to just wrap a MAX, i.e.

MAX(0,$I10-INT($I$10/I$8)*I$8)

[maclearyj]

Hi there. Your problem is arising because of the way excel handles numbers. When storing decimal numbers (as you are aware, date/times are actually stored as fractions of a day) there are some fractions of a day that cannot be stored precisely (e.g. 6a.m. is 0.25 - a quarter of a day, but 8a.m. (a third of a day) is stored as 0.333333 recurring, and is therefore subject to a very small discrepancy when non-precise decimal values are used. In your case, you can fix this by introducing a very small addition to the sum in your formula like this:

Please Login or Register  to view this content.

Try it and see.

[BlissC]

Thank you both for such speedy responses, and they work perfectly. I've just tried both, and no more errors with either! Thanks again.

[maclearyj]

You're welcome and thanks for the feedback.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[BlissC]

Okay, so I thought I'd got this working until one of my colleagues messaged me to say that his leave card isn't working properly and isn't showing the right number of days of leave he has remaining. Having corrected the rounding problem, as above, I suspected that he'd possibly just entered a number incorrectly somewhere, but when I took a look at his leave card, it is indeed showing the number of days remaining incorrectly.

It's working correctly until about half the way down the column, where something strange has started to happen, and it appears to be a rounding problem again. I'm using the same formula:

Please Login or Register  to view this content.

His standard working day is 7:24. Formula works fine for several entries, but then, rather than showing the remaining number of days as "7 days 00:00" for example, it's showing the number of remaining days as "6 days 07:24".

It's like saying that the time is 10:60 rather than 11:00.

I've checked all the formulas again to check I've not mis-copied anything, and all the formulas look to be fine, and I think it's a rounding problem, but I've no idea why it only kicks in part way down the column, or what the fix is.

Could anyone advise please?

[XLent]

without a file to see it's hard to be sure but you could, perhaps, try to employ MROUND - e.g.

MAX(0,MROUND($K30-INT($K30/O$15)*O$15,1/86400))

[BlissC]

Unfortunately that hasn't worked - it just showed many of the values in that column as 'ERROR', but left the results that had been displaying incorrectly exactly the same.

I've attached my latest test version of the file. The column in question is Column L, the 'Annual Leave Remaining (Days)' column. (The leave card shows leave as both hours and days, as full-time employees get their leave entitlement in days, whereas part-time staff get their leave entitlement in hours. This way I can use the same leave card for both)

I've highlighted the two results displaying incorrectly in red, but when I tried adding more entries to the list, they too are displaying incorrectly.

[XLent]

try

Please Login or Register  to view this content.

need to round the INT element to get correct days.