Forum Discussion
IF function with multiple logic tests
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!
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!
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!!
