Hot Topics

Excel minus hours formula problem

  recap 11:34 22 Jul 2018
Locked

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.

Any help would be appreciated. recap

  wee eddie 12:24 22 Jul 2018

You need to record time in the format hh:mm:ss

Then If you record the number of Hours done in column A, the number of hours not done in column B and in column C, you enter the formula =A-B

  recap 13:14 22 Jul 2018

Thank you wee eddie. I will give it a try later today at work.

  wee eddie 14:09 22 Jul 2018

I do, Start Time, Finish Time =Finish - Start. then subtract Time out

  wiganken2 16:43 22 Jul 2018

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.

  wiganken2 16:45 22 Jul 2018

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 17:02 22 Jul 2018

How about writing time as hh:mm:ss which is what excel understands.

  wee eddie 17:04 22 Jul 2018

A column that exhibit as ###### is because it's not wide enough for the answer

  wiganken2 18:17 22 Jul 2018

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.

  wee eddie 19:10 22 Jul 2018

This is probably because "Negative" Time is a concept that humanity has yet to embrace.

You need to convert "Time" into a "Number".

My guess would be to change the format of that column.

Sorry, I'm just guessing as I don't have Excel on my phone

  wiganken2 20:07 22 Jul 2018

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.

This thread is now locked and can not be replied to.

1x1 pixel
Elsewhere on IDG sites

Realme X50 5G lands in the UK and Europe for £299/€349

AMD and Lenovo have created the world’s first 64-core workstation

iPhone 12 series could have smaller batteries

Bon Plan Amazon : 46 % de réduction sur le bracelet HONOR Band 5