Hi all, I have a spreadsheet and am trying to calculate hours worked using 2 sheets.
sheet1 is entitlement calculation and sheet2 is public holidays

starting in sheet 1 cell b12 hours started is 01-oct-2015 then sum public holidays rage b2:b9 it just sums the holidays after 30-sep-2015 and get the correct answer which is 7 because the range is
Day Date Hours worked
Friday 03/04/2015 3.5
Monday 06/04/2015 0
Monday 04/05/2015 3.5
Monday 28/09/2015 0
Friday 25/12/2015 3.5
Monday 28/12/2015 0
Friday 01/01/2016 3.5
Monday 04/01/2016 0

however the next entry in cell b12 of sheet 1 may be 01-may-2015 in which case the answer would be 10.5 does this need vb code or does anyone know how to do this using a function. any help would be much appreciated

I'm not sure if I understood your question, but I think you just need to use SUMIF Function in Excel to add the hours worked for the holiday in question.


So if you have a designated Column for Holiday or not, then you can use SUMIF to check if a certain date is a holiday. Then proceed as is.

Hope this helps!