Work time spreadsheet - Values of negative and positive time

[LauraCLee]

Hi,

First timer here so please be kind

I'm trying to set a formula within a time sheet so I can establish how many extra hours a day I work or don't for days I'm on holiday.

I can get the formula to work for either but not both using the below and changing the <> & -+ but I can't get it do both

=IF(H159-G159<0,"-"&TEXT(ABS(H159-G159),"hh:mm"),H159-G159)

For example on Monday I am contracted to work 09:30 - 17:00 but I took holiday so worked 00:00:00. The above formula worked fine and returned -07.30.

However on Tuesday I am contracted to work 09:30 - 17:00 but actually worked 17 hours (05:00 - 22:00) as I was travelling. The same formula returned 0.4. When I changed the < to > and - to + it shows correctly as +09:30

I've tried to add the 2 together but it keeps coming up with an error so wondered if just me or whether this wasn't possible?

Any help offered, gratefully received. Thanks in advance

 

[Zakkaroo]

I think you just need both sides of your equation to have absolute etc. i.e.

=IF(H1-G1<0,"-"&TEXT(ABS(H1-G1),"hh:mm"),"+"&TEXT(ABS(H1-G1),"hh:mm"))

It seems to work for me

 

[LauraCLee]

I think you just need both sides of your equation to have absolute etc. i.e.

=IF(H1-G1<0,"-"&TEXT(ABS(H1-G1),"hh:mm"),"+"&TEXT(ABS(H1-G1),"hh:mm"))

It seems to work for me

Perfect, thanks Zakkaroo