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

  csqwared 20:33 22 Jul 2018

As wee eddie has said, since time doesn't have a negative concept formatting as TIME won't work.

Is this workaround acceptable (formatted as time):-

A1=12:00 B1=14:00 C1=If(b1>a1,"-","+") d1=If(b1>a1,b1-a1,a1-b1)

This gives an extra cell (centred if necessary) with the required 'minus/plus' sign. You could also use Conditional formatting on cells C1 & D1 to show red i.e C1 condition "Cell value is equal to "-" and set font to red. D1 condition is "Formula is B1>a1" and set font to red.

Hope that's of help.

  wee eddie 22:52 22 Jul 2018

VOG would have been proud of you

  recap 07:27 23 Jul 2018

Thanks everybody. I will give your suggestions a try on Wednesday when I'm next in that particular job.

  csqwared 08:59 23 Jul 2018

VOG!! What a star he was.

  recap 14:29 25 Jul 2018

Hi folks,

I used part of csqwared formula '=If(b1>a1,"-","+")' to get what I required.

Thanks everyone for your time

  csqwared 19:41 25 Jul 2018

Glad to be of help. :-))

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

Elsewhere on IDG sites

Honor unveils special edition Magic Watch 2 with artists and designers

Adobe CC buying guide: January deals, pricing, new features & free trials

The 29 best Apple Watch apps

Les meilleurs aspirateurs robots (2020)