Excel formula to count how many times work was carried out on a weekend between the hours of 17:00 and 19:00

Question

I have a spreadsheet for working weekly on-call and I'm trying to create a formula for counting how many times work was carried out on a weekend between 17:00 and 19:00 hours.

I tried something like this, which is not working:

=COUNTIFS(Oncall[Date],WEEKDAY(2>=6),
Oncall[Time Start],">=17:00",Oncall[Time Start],"<=19:00")

Oncall is the name of the spreadsheet, thanks.

Answer 1

You can't use the WEEKDAY criterion like that in COUNTIFS, try using SUMPRODUCT function like this:

=SUMPRODUCT((WEEKDAY(Oncall[Date],2)>=6)*(Oncall[Time Start]>="17:00"+0)*(Oncall[Time Start]<="19:00"+0))

Answer 2

You need to add a column to your data with a formula like this

=if(and(or(weekday([Date])=1,weekday([Date])=7),or(and([TimeStart]<"17:00",[TimeEnd]>"17:00"),and([TimeEnd>"19:00",[TimeStart]<"19:00"),and([TimeStart]>="17:00",[TimeStart]<"19:00"))),1,0)

To break this down: (or(weekday([Date])=1,weekday([Date])=7) is just the test that it was a weekend day

and([TimeStart]<"17:00",[TimeEnd]>"17:00") you started work before 17:00 but continued that work past 17:00

and([TimeEnd>"19:00",[TimeStart]<"19:00") you started work before 19:00 but kept on working past 19:00

and([TimeStart]>="17:00",[TimeStart]<"19:00") you started and finished the work entirely within the time frame.

That will generate a 1 or 0 and then you just sum up this column.