Formula sometimes works using [h]:mm; cell time format

[rkoch123]

Have a column to check if timesheet add-up correct. Am getting a ##### error sometimes and othertimes get what 0:00 result when using formula: =IF(G21="","",G21-SUM(I21:P21))
G21 is the sum of start, end, & breaks times. Columns I-P are the division of total work hours (G21). The problematic QC formula is the check if G21 and the sum of projects are equal & equal 0:00 hours. Note: All cells are in custom cell format: [h]:mm;
EXAMPLE:
cell G21 formula: =IF(D20="","",($E20-$D20)-F20)
cell G21 result: 8:30
cells I21-P21 values: 0:30/1:00/1:30/0:30/1:00/1:30/2:00/0:30
cell I21-P21 result: 8:30
cell Q21 checking formula: =IF(G21="","",G21-SUM(I21:P21))
Sometimes given Q21 result of 0:00 and other times #######

Please advise

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[MrShorty]

I expect that the problem is simple floating point error. ####### is technically not an error, but simply Excel's way of saying that it cannot display the cell's value using the assigned number format. Excel's time formats cannot display negative times -- even if the negative value is something like -1.1E-15 (which, if it could be displayed as 0:00:00 would display as -00:00:00).

As a debugging step, I would change the number format of the cell to scientific 0.00E+0 and see what the actual value of the calculation is. I expect you will find a tiny negative number that, for all practical purposes, is 0.

Solutions to floating point errors is usually to use one of Excel's rounding functions to force an exactly 0 result when the result suggests that it should be exactly 0.

More information that you probably want to know about floating point error here: https://www.excelforum.com/groups/ma...nd-errors.html

[rkoch123]

Thanks Ford. I've uploaded an example spreadsheet. Please note that the cells are custom format is [h]:mm; (with an 'at' symbol at the end)
Struggling with the formula in column Q.

[rkoch123]

Hi MrShorty,
I believe you are correct. The value returned is -5.55111512312578E-17
If this is the case, how would I force excel to round the time?

[rkoch123]

I think I solved it by investigating the floating point error mentioned by Mr.Shorty!!

For this, I put a ROUND() to two digits within the IF(). And it works!!!

Corrected Formula:
=IF(G21="","",ROUND(G21-SUM(I21:P21),2))

[AliGW]

Yes, it will be the floating point error!

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

[MrShorty]

Glad you got something that works. The only thing I might add is that you are rounding to the nearest 0.01 (1 hundredth of a day or 14.4 minutes). This means that you are never expecting to reconcile times closer than about +/- 15 minutes. If the sums are within about 15 minutes of each other, you are forcing them to be seen as the same. If that is your intention, then great. If not, you may want to think more carefully about how closely you expect the times to reconcile and choose a "fraction of day" that better reflects your need to reconcile times.

[rkoch123]

Glad you got something that works. The only thing I might add is that you are rounding to the nearest 0.01 (1 hundredth of a day or 14.4 minutes). This means that you are never expecting to reconcile times closer than about +/- 15 minutes. If the sums are within about 15 minutes of each other, you are forcing them to be seen as the same. If that is your intention, then great. If not, you may want to think more carefully about how closely you expect the times to reconcile and choose a "fraction of day" that better reflects your need to reconcile times.

Good point. Yes, I noticed that was an issue when a 1/2 hour was represented as 00:28 when rounding to two decimal places. I've extended the decimal places to 5 decimal places resulting in 00:30 which reflects the true value.