Counting Hours Worked Excluding Public Holidays

[Joeyhoyw]

Untitled.png

Hello,

I have a few formulas to figure out but I am not that good at Excel.
1) To calculate the hours worked, excluding public holidays, to give the result 138:00.
2) To calculate the hours worked on public holidays in the next row 6:00.
3) To calculate the number of weekend days worked (could be zero hours e.g. off day on the weekend), and to return the value "1" if the number of days worked is more than 4 but less than 8, and to return the value "2" for the number of days being 8 and above.
4) I have managed to do conditional formatting for weekend days, but I would need to apply that for public holidays as well.

Thanks in advance!!

[ben_hensel]

Probably I'd add a helper column with a formula that was something like:

Please Login or Register  to view this content.

Note that a public holiday on a weekend would be reported as a "weekend", not a "public holiday"; you can which IF is tested first if you want to assess it the other way. (Conversely, we could rebuild with a CHOOSE if you want to return "Holiday Weekend" as it's own option).

Then you could just run something like

Please Login or Register  to view this content.

to deliver your requirement (2); while requirement (1) would just be SUM(F2:F25) minus the formula above, (3) would be a COUNTIF() against then number of weekend days worked or something.