I'm trying to create a sheet which will automatically calculate both the time worked and any flexi time earned. If my working day is 7 hours and 24 minutes, see example below.
| Date |
Start |
Lunch |
End |
Total |
Flexi |
| 07-Jan |
9:00 |
0:30 |
17:20 |
7:50 |
0:26 |
| 08-Jan |
8:00 |
0:30 |
16:20 |
7:50 |
0:26 |
| 09-Jan |
7:30 |
0:30 |
16:30 |
8:30 |
1.06 |
| 10-Jan |
9:30 |
0.30 |
17:00 |
7:00 |
########## |
The formula in the Total column is simply D-C-B but after some issues with this field not calculating correctly, I'm using =MAX(D-C-B,0) instead.
The formula in the flexi column is the Total column minus another cell which contains 7:24 (daily hours).
Where the result is a positive figure, there is no problem and the formula works correctly. Where the result is a negative, I get ########, instead of -0.24.
I've been able to resolve this by using - =IF(E14-$K$2>0, E14-$K$2, TEXT(ABS(E14-$K$2),"-h:mm"))
Where column E is the Total column and K2 is the column which contains the 7:24 figure. Cells are formatted as [h]:mm;@
However, what I then want to do is sum the figures to give an overall total of the flexi time adding positives but subtracting negatives e.g. 0.26+0.26+1.06-0.24 but all I get is #VALUE! error
Can anyone help with a formula which will allow positive values to be added and negative values to be subtracted?
Thanks in advance
Lori