Search code examples
excelif-statementexcel-formulaweekday

Excel: I have two working formulas. Need to get them to run based on =IF(WEEKDAY)

Im building a schedule rotation in which a group of employees are either Primary oncall, Secondary oncall, or not OnCall at all.

I have a row in my spreadsheet that validates if both primary and secondary are covered on any given day, however secondaries are not used on weekends. (P=primary, WP=Weekend Primary, HP=Holiday Primary, same with secondaries for tracking purposes)

In the examples below row 3 houses the dates, beginning in column B as the first of the month, B3=5/1/2020 (which is a Friday), C3=5/2/2020 (Saturday), etc

This works for Monday through Friday: =IF(AND(SUM(COUNTIF(B5:B11,"p")+(COUNTIF(B5:B11,"wp"))+(COUNTIF(B5:B11,"Hp")))=1)*(SUM(COUNTIF(B5:B11,"s")+(COUNTIF(B5:B11,"ws"))+(COUNTIF(B5:B11,"Hs")))=1),"G","B")

This works for Weekends: =IF(AND(SUM(COUNTIF(C5:C11,"p")+(COUNTIF(C5:C11,"wp"))+(COUNTIF(C5:C11,"Hp")))=1),"G","B")

The problem is every month when I go to do the next schedule, i have to rearrange the the formulas in this row so they pine up below weekdays or weekends, respectively. Id like to combine these formulas into an =IF(Weekend,,2)<6,weekday formula, else weekend formula

My Attempt =IF((WEEKDAY,B$3,2)<6,IF(AND(SUM(COUNTIF(B5:B11,"p")+(COUNTIF(B5:B11,"wp"))+(COUNTIF(B5:B11,"Hp")))=1)*(SUM(COUNTIF(B5:B11,"s")+(COUNTIF(B5:B11,"ws"))+(COUNTIF(B5:B11,"Hs")))=1),"G","B"),IF(AND(SUM(COUNTIF(B5:B11,"p")+(COUNTIF(B5:B11,"wp"))+(COUNTIF(B5:B11,"Hp")))=1),"G","B"))

Solution

  • You have wrong brackets in the IF condition.

    yours =IF((WEEKDAY,B$3,2)<6,

    correct =IF(WEEKDAY(B$3,2)<6,

    Background: The default for the Weekday() function returns 1 for Sunday, 2 for Monday, etc and 7 for Saturday.

    If you want to run a formula if the week day is Monday through Friday, then you need to test if the returned number is greater than 1 and also smaller than 7. You can wrap an AND() function around two Weekday() functions for the IF condition like this:

    =if(AND(Weekday(A1)>1,Weekday(A1)<7)),WeekdayFormula,WeekendFormula)

    To avoid the complicated AND construct for the IF condition, you may want to use the parameter 2 in the Weekday() function. This will return Monday as 1 and Sunday as 7 and all you need to check for a weekday is if the number is smaller than 6. Like this:

    =if(Weekday(A1,2)<6,WeekdayFormua,WeekendFormula)