IF function with multiple logic tests

[Angela McGhin]

Hi - I currently have the following formula which works fine:

=IF(SUMIFS(Holidays[[Hours/Day]:[Hours/Day]],Holidays[[Resource Name]:[Resource Name]],Report!$B22,Holidays[[Start Date]:[Start Date]],"<="&Report!AE$3,Holidays[[End Date]:[End Date]],">="&Report!AE$3)=1, "H")

It basically pulls data from a list of travel dates (start date, end date and resource which is a staff member), and displays them in a 'calendar' view. If someone is scheduled to be on holiday on a particular day, the appropriate cell will show an 'H'.

However, I'd like to add a condition so that if that day happens to be a Saturday or Sunday, the cell is left blank. (I have a total formula at the end of each row which counts the H's as days holiday)

The day is actually available in row 2 of the spreadsheet, and shows as 'ddd'

Do I need to use the AND or OR function for this, or something completely different?

Happy to provide more detail if needed!

[#2]

Hey Angela-

Hope you're doing well today.  Would you happen to have a small non-sensitive example file that you could provide the community in order to test their solutions on?  Sometimes it's just a little easier to make the adjustments in an actual file rather than toying around with it in your head.  If you could just provide 5 or 6 rows of data with the result now and then the result that you expect with your new formula that would be helpful as well (basically a before and after).  Thanks for posting!

[#3]

Hi Matt

Thanks for your reply - apologies for the delay getting back to you.

Here's a sample file for you to look at. As you'll see there is:

 - a tab for Business Travel data

 - a tab for Holiday data

 - a tab showing the results (a month to view at a time, month controlled at the top of the sheet)

The dates and days of the week change according to the month selected.

Let me know if you have any questions!!