Minus figs help

[Fester675]

I have a Result cell for carried forward annual leave (I14) but I need to be able to add in the ability to use/calculate minus entries. I'm unsure how to go about this! So any help is greatly appreciated.

The calc converts DAYS into HH:MM, based on location, and contract type. The user enters their CF balance into cell I10.

=IF(I2="REGION","",IF(AND(I2="NATIONAL",I6="FULL TIME"),(I10*7.4)/24,IF(AND(I2="NATIONAL",I6="PART TIME"),((S6/S10)*I10)/24,IF(AND(I2="LONDON",I6="FULL TIME"),(I10*7.2)/24,IF(AND(I2="LONDON",I6="PART TIME"),((S6/S10)*I10)/24)))))

 

[jmacleary]

Hi there. Your formula actually produces the correct value that you want, but formatting the cell as HH:MM results in a load of # signs because that's what excel displays for negative times. So, you can cheerfully use the result in any calculation. There is help available on how to display negative times, this link shows you 2 ways (I suggest for your purposes to use the alternative 1904 date option suggested by Allen. Displaying Negative Times

Alternatively if you just want to display the time, then replace I10 with ABS(I10) in the formula and it will always come out positive. You could then use conditional formatting to colour it red, or whatever you desire to indicate to the user that its actually negative.

 

[Fester675]

Thanks J - but it comes back with ERROR when I try to use ABS on (I10).

Will I need to use it on every mention of I10 or...?
Should ABS have its own parentheses or not?

Thanks again - really appreciate it!

 

[jmacleary]

Yes, you will need to use ABS on all - here is the amended formula: =IF(I2="REGION","",IF(AND(I2="NATIONAL",I6="FULL TIME"),(ABS(I10)*7.4)/24,IF(AND(I2="NATIONAL",I6="PART TIME"),((S6/S10)*ABS(I10))/24,IF(AND(I2="LONDON",I6="FULL TIME"),(ABS(I10)*7.2)/24,IF(AND(I2="LONDON",I6="PART TIME"),((S6/S10)*ABS(I10))/24)))))