Count Duty Fall in Holiday List

[limtat]

Hi,

I making calendar by month for scheduling our employee duty. i used letter "D". I manage counting total letter "D" during weekday and weekend by using =sumproduct((weekeday(range_date,2)>5*(range_cell="D")). but my problem is i cannot count if employee is on duty during public holiday. someone can help me to solve this problem.

Thanks in advance.

 

[abmati]

Why you do not mark "D" if any employee works on Holiday ? This will solve your problem easily.
If the rate for working on Holiday is higher, instead of "D", use another letter, so can use formula accordingly.
Regards,

 

[Aladin Akyurek]

Hi,

I making calendar by month for scheduling our employee duty. i used letter "D". I manage counting total letter "D" during weekday and weekend by using =sumproduct((weekeday(range_date,2)>5*(range_cell="D")). but my problem is i cannot count if employee is on duty during public holiday. someone can help me to solve this problem.

Thanks in advance.

Let Holidays refer to a range housing public holiday dates.

=SUMPRODUCT(
  --(weekeday(range_date,2)>5),
  --(range_cell="D"),
  1-ISNUMBER(MATCH(range_date,Holidays,0)))

Is this what you are after?

 

[limtat]

Let Holidays refer to a range housing public holiday dates.

=SUMPRODUCT(
  --(weekeday(range_date,2)>5),
  --(range_cell="D"),
  1-ISNUMBER(MATCH(range_date,Holidays,0)))

Is this what you are after?

Im still cant get the value if i enter "D" on holiday cell. i create range of holiday date as "Holidays". any other solution?

 

[Aladin Akyurek]

Im still cant get the value if i enter "D" on holiday cell. i create range of holiday date as "Holidays". any other solution?

I presume that

=SUMPRODUCT(
  --(weekday(range_date,2)>5),
  --(range_cell="D"))

gives the same result as the formula you had, which counts weekend days that are marked with D. The Holidays range is supposed to contain dates which are public holidays. So:

=SUMPRODUCT(
  --(weekday(range_date,2)>5),
  --(range_cell="D"),
  1-ISNUMBER(MATCH(range_date,Holidays,0)))

excludes days from the count, which are public holidays. I don't understand what you mean by "enter "D" on holiday cell". Recall that the Holidays range is a separate range in which you enter DATES on which public holidays are held. So try to describe what you did get before you issue a call for "any other solution".