Hi
I'm devising a time log for agency staff in my workplace, so that I can monitor their logged-on periods on our call-handling system to match up with claimed hours on their timesheets. The intention is to determine if they are working the full 7.5hrs (8hrs minus unpaid 30mins lunch) and making themselves available for 7.0hrs (taking into account their set tea breaks).
I have checked and rechecked the formulae, and the cell format settings, and everything appears to be correct and consistent, with no errors or circular formulae, or anything else that might incorrectly result in a negative time. When all times are entered, everything seems to work fine, except in two columns with formulae - between both, there are six instances of 0hrs (formatted [hh]:mm:ss;@) with three of these showing as -00:00:00 and three as 00:00:00. They are all using the same formula pointing to the appropriate relative and absolute cell references (in the attachment, see cols K and M).
Cells I2 to M22 are formula-based, with a 'ISBLANK' function built in to keep the table tidy. I would prefer to keep this in, so I have tried to nest another IF function in cols K and M to see if I could just make the 0hrs blank to remove the issue, but I can't get this to work.
Is there something I am missing? The inputted times in D2 to H22 are all precise, there are no hidden digits etc that might cause a sneaky decimal somewhere to throw of the calculation, as it might do if they were ordinary numbers.
Cells D2 to M22 are all custom-formatted with [hh]:mm:ss;@ so I can't think of what else is the cause; the negative issue was occurring when it was in the Time format of hh:mm:ss as well.
At the end of the day I could live with the issue, however I'm at a loss to understand why it is happening even if it can't be fixed.
Many thanks for your help in advance.
Bookmarks