Hi folks, long time since I was on here.
I have a problem with calculating minus hours in my spread sheet.
what I am doing is calculating lieu time. I have calculated the hours accrued and hours taken from the accrued hours, that was easy to do until; one colleague took more hours than they had accrued.
I am using the custom time of hh:mm in the cells. I have thirteen work books, one per colleague, with each sheet being for a month to the end of the financial year.
My question is how do I show that a colleague has taken more hours than they have accrued. With the formula I have it is just shows example: 23.15, I would like this to show -00:45.
Wee Eddie's answer didn't work for me. All I got in column C was a row of #######. My answer works but I can't show a picture in TA (unless someone tells how to do that) and it's difficult to put into words but here goes: -
Hours:minutes 'Accrued' in cell A1 - 20:45
Hours:minutes 'Taken' in B1 - 21:00
Formula in Cell C1 - IF(B1>A1, "EXCEEDED", "OK") will show "EXCEEDED"
In Cell D1 - =B1-A1 will show 00:15
Header to Cell C1 can be "Result"
Header to Cell D1 can be "Exceeded by"
The only negative is that if Cell A1 has not been exceeded then you get a row of ##### in Cell D1. Otherwise it works because the "Exceeded" times are shown.
Darn it! That post I just did didn't display exactly how I typed it in. The TA display formatting is wrong. It doesn't look anything like I typed it in so sorry if you can't follow it. It's not my fault. It's TAs.
Wee Eddie - Normally I would agree with you but not in the case of negative dates and times. These show as ##### and the #s go on forever so the column width can never be wide enough. It is Excel's way of showing negative times.
I don't know if I am allowed to do this but, because TA does not allow pictures to be uploaded, I have posted the question on another forum with a picture included (hopefully TA will not delete this post) and this will help people on this forum. The link is here: - click here . I hope it helps.