Share via

Tracking Flexi Time in Excel

Anonymous
2020-01-14T15:26:29+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

1 answer

Sort by: Most helpful
  1. Anonymous
    2020-01-14T15:35:50+00:00

    =IF(B2<>"",B2,"") =IF(A7<>"",A7+1,"") =IF(((D7-C7)+(F7-E7))*24>8,8,((D7-C7)+(F7-E7))*24) =IF(((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-8,0)=SUM(G7:J7)=SUM(G7:G13) =IF(SUM(G14:J14)=SUM(K7:K13),SUM(G14:J14),"Error!")Try this code it might help!Regards,Lewis

    Was this answer helpful?

    0 comments No comments