# Sum extra hours worked before today and subtract leave hours based on text string

• Hi All, I hoped someone may be able to help with the following. I want to calculate the running total of hours worked, day by day, above those contracted and then subtract set hours based on leave type which is a text string. The basic timesheet is on sheet1 and the lookup table of set leave hours is on sheet5. I have attached the workbook that hopefully gives enough information. Thanks in anticipation of any support

## Files

• Re: Sum extra hours worked before today and subtract leave hours based on text string

Try this:

[COLOR="#0000FF"]=M4+SUMIF(\$B\$2:\$B\$122,"<"&TODAY(),\$J\$2:\$J\$122)-SUMPRODUCT(--(ISNUMBER(MATCH(Table1[leavetype],\$H\$2:INDEX(\$H\$2:\$H\$122,INDEX(MATCH(2,1/(\$B\$2:\$B\$122<TODAY())),0)),0))),Table1[Time])[/COLOR]

although my result is 9:48 instead of 9:52. Please check your calculations.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Sum extra hours worked before today and subtract leave hours based on text string

Hi NBVC... thanks for taking the time to help. I thought I was close a couple of times, but seeing your solution I realise I was nowhere near. I got hung up on trying to make vlookup work. I'm gonna spend sometime trying to figure out your approach. Thanks again... greatly appreciated

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!