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

    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!

Register Yourself Login