Formula Help - Excel

Question

Hello! Can anyone please assist with creating formulas for the following table?

Table - Sorry for the link. The form wouldn't upload the image.

For the table on the left, I am trying to get formulas for the following:

If location London has the pool closed on Wednesday, Thursday then the closure column should reflect YES

If the day falls on a holiday - 3/17, 11/27, 12/25, 1/1 then the closure column should reflect HOLIDAY

If the day does not apply to the above, the closure column should reflect NO

If Cuba is closed on Wednesdays during January & February, the column should reflect YES with 11/27, 12/25, & 1/1 reading HOLIDAY & all other dates reflecting NO

I would like the table on the right to automatically tally up the yes, no, & holiday counts. I have been doing everything manually at the moment. I have other locations & dates, but I think if its possible to get formulas for the above, then I can plug in the other information. I appreciate all feedback & assistance. 🙂

Answer 1

=IF([@[Item N/A]]="Pool",IF(OR([@[Date N/A]]=DATE(YEAR([@[Date N/A]]),3,17), [@[Date N/A]]=DATE(YEAR([@[Date N/A]]),11,27), [@[Date N/A]]=DATE(YEAR([@[Date N/A]]),12,25), [@[Date N/A]]=DATE(YEAR([@[Date N/A]]),1,1)),"HOLIDAY",

IF(AND([@Location]="London", OR(WEEKDAY([@[Date N/A]], 2)=3, WEEKDAY([@[Date N/A]], 2)=4)),"Yes","NO")),"")

What you want to show when Item NA is hot tub?

Answer 2

This is excellent! Thank you. If I included 'hot tub' under the item N/A column, would there be away to add something into the formula that only pinpoints the data for the pool item? I added some changes if you would be so kind & take a look. :) I really appreciate your help.

Answer 3

=IF(OR([@[Date N/A]]=DATE(YEAR([@[Date N/A]]),3,17), [@[Date N/A]]=DATE(YEAR([@[Date N/A]]),11,27), [@[Date N/A]]=DATE(YEAR([@[Date N/A]]),12,25), [@[Date N/A]]=DATE(YEAR([@[Date N/A]]),1,1)),"HOLIDAY",

IF(AND([@Location]="London", OR(WEEKDAY([@[Date N/A]], 2)=3, WEEKDAY([@[Date N/A]], 2)=4)),"Yes","NO"))

Answer 4

I'm sorry. I'm not sure how to do that. Does this work?
Pool N.A Report.xlsx

Answer 5

Could you share a test file? Then I can put formula on it and send back to you.

=IF(OR(C2=DATE(YEAR(C2),3,17), C2=DATE(YEAR(C2),11,27), C2=DATE(YEAR(C2),12,25), C2=DATE(YEAR(C2),1,1)),"HOLIDAY",

IF(AND(A2="London", OR(WEEKDAY(C2, 2)=3, WEEKDAY(C2, 2)=4)),"Yes","NO"))