peRoster Dilemna: calculate hours worked for scific shifts on public holidays using tables

[Faboolous]

Hi Guys,

Sorry to ask again for support. There are great minds on this forum that helped me in a heartbeat in the past and I hope I'll have the same luck this time. I am struggling a bit to figure out the logic on my problem.

In a roster, I'd like to figure out how to count total hours worked on public holidays for the staff. Taking into account the specific shift worked.
In an ideal scenario, the formula will figure if the day worked is a public holiday (PH), if it is what is the shift and the hours corresponding, and sum up all hours worked during those public holidays.

I've attached the excel example, in yellow the outcome I'm seeking. I have one roster with people and 2 tables containing my shift and my public holidays. For the sake of example I put everything on the same sheet.

I'm sure it is an easy fix for some but not for me. Would definitely appreciate a hand on this one.

Thanks,
Fab

[zbor]

Try this:

Please Login or Register  to view this content.

[Faboolous]

Hi zbor,

Thanks for the quick response. My bad I don't think my example was clear enough. I have updated the file to make it be clearer.

I had a similar formula as yours to calculate the Total of hours worked during a week.
I'd like to figure out, how many hours worked are solely on Public Holidays (PHW).

Thoughts?

[oeldere]

if you restore your data in a table you can use pivot table to analyse your data.

A name
B day
C hour
D criteria

[zbor]

Try this:

Please Login or Register  to view this content.

Confirmed with ctrl+shift+enter (not just enter)

[Faboolous]

Hi zbor,

Thanks a lot for this. It works like a charm, goes bit beyond my knowledge but it's exactly what I was looking for.
Thanks again, I'll pay attention to my subject/description on my next post

Fab

[zbor]

For new holidays just add dates below in A column.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.