Hot Topics

Excel minus hours formula problem

  recap 11:34 22 Jul 2018

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.

Elsewhere on IDG sites

What’s the difference between VPN and Antivirus and do you need both?

These are the best album covers of 2019

How to pick the best iPhone insurance

Les bons plans gadgets high tech (2020)