Use Excel’s DATE, WEEKDAY, IF, and IF/OR to make weekly schedules by task, event and team member

Excel’s SUM, DATE, WEEKDAY, IF, Nested IF, and IF/OR functions came to mind as I was watching a 1969 film called If It’s Tuesday, This Must Be Belgium, about American tourists on a whirlwind tour of Europe. It occurred to me that companies are often required to create schedules based on the weeks (as opposed to the days) in a month—for example week1, week2, week3, etc. This would entail using a date formula to identify each week in a given month, plus a series of nested IF/OR statements to assign tasks and team members to complete those tasks. Using Excel as a scheduling tool is a great skill to have under your belt. 

Imagine that you work for a magazine that covers sporting events all over the world, and it’s your job to create the schedule for the journalists and videographers that cover these events. For example, on the third Tuesday of a given month, the event is figure skating and the location is Belgium; on the fourth Tuesday, it’s bobsledding in Scotland; on the first Thursday, it’s golf in Ireland; and on the last Thursday, it’s rugby in New Zealand. The formulas below can turn this cumbersome, time-consuming chore into a simple, quick and easy task.

We’ve included a downloadable spreadsheet you can use to practice these skills: 

Formulas/functions used in this article

1. SUM

Syntax: =SUM(A2:A10); =SUM(4+5); =SUM(A2+A3)-A4; =SUM(A2-A3)+(A4-A5)

Define: The SUM function is quite versatile. It can used to add, subtract, multiply, divide, and/or perform dozens of other calculations.

2. DATE

Syntax: =DATE(year,month,day)

Define: Provides a date based on three values: year, month, day.

3. WEEKDAY

Syntax: =WEEKDAY(serial_number, [return_type])

Define: This function provides an Excel serial number for the weekday and return type is a number between 1 and 7 that represents each day of week. For example:

JD Sartain / IDG Worldwide

4. IF statement

Syntax: =IF(logic_test, value_if true, value_if_false)

Define: If the statement is true, then do A; else/otherwise do B. For example, if it’s raining, then close the windows, else/otherwise leave the windows open. (To see more examples, see our story on getting started with Excel IF statements.)

5. Nested IF statement

Syntax: =IF(logic_test, value_if true, IF(logic_test, value_if true, IF(logic_test, value_if true, value_if_false)))

Define: If the statement is true, then do A, If the statement is true, then do B, If the statement is true, then do C, else/otherwise do D.

6. IF statement with OR condition

Syntax: =IF (see above)

Syntax: =OR(logical1,[logical2]…)

Define: =OR(is condition 1 true, OR condition 2 true, OR condition 3 true; etc.

=IF(OR(A2=6,B2=11,C2=9,D2=2),”YES”,”NO”)

NOTE: Remember, OR means if condition A or B or C is true, then answer YES; if none of these are true, answer NO. AND means A, B, and C must ALL be true to get a YES; but if only one is true and the other two are not true (false), then the answer is NO.

Build the Spreadsheet

1. First, let’s quickly build the spreadsheet. Enter the following headers in columns A through I: (A) YEAR, (B) MONTH, (C) WEEK—(D)WEEK, (E) DATE, (F) EVENT, (G) LOCATION—(H) LOCATION, (I) JOURNALIST/ VIDEOGRAPHER. Columns C and D are merged with the one column header WEEK. Columns G and H are the same (one merged column header titled LOCATION). See spreadsheet below for details.

JD Sartain / IDG Worldwide

2. In column A, enter the year 2019, from A2 through A20, skipping every fifth row; that is, every fifth row is blank (for aesthetics only). In column B: Enter the number 1 for January in B2:B5; the number 2 for February in B7:B10; the number 3 for March in B12:B15; and the number 4 for April in B17:B20.

3. In column D, enter 1^st, 2^nd, 3^rd, 4^th in each four-row block; that is D2:D5; D7:D10; D12:D15; and D17:D20. The remaining columns are formulas except column H, which could be a formula, or you can just manually enter the country that matches the city in column G. Because we have more than enough formulas for this spreadsheet, I’ll leave this column to your discretion. See if you can determine what the best formula for this column would be and then enter it in column G.

Enter the formulas

1. The formula for column C (WEEK) is a SUM function, which defines the week number in each month and can be entered in any one of the four different syntax statements: =1+7*1; or =SUM(1+7*2); or =SUM(7*3+1); or =SUM(7*4)+1.

2. In English: one plus seven, times one, equals 8, which corresponds to the first week of the month; one plus seven, times two, equals 15 (second week); one plus seven, times 3, equals 22 (third week); and one plus seven, times four, equals 29 (fourth week); and so on if there are five weeks.

3. Enter these four formulas in the first four-row block; that is C2:C5. The result will be 8, 15, 22, and 29. Copy these four rows down to rows C7:C10; C12:C15; and C17:C20.

NOTE: Column D, which you have already entered (instructions above), is unnecessary for the calculations or understanding of this worksheet. It’s there for aesthetics only.

4. The formula in column E (DATE) determines the DATE from columns A, B, and C, then subtracts the WEEKDAY DATE A, B, and C minus the Day of the Week number; i.e., 3 for Tues, 4 for Wed, etc. (see chart above under Formulas/Functions, #3 Weekday). Remember to enter, then copy.

5. Enter this formula in E2:E5: =DATE(A2,B2,C2)-WEEKDAY(DATE(A2,B2,C2-3))

Enter this formula in E7:E10: =DATE(A7,B7,C7)-WEEKDAY(DATE(A7,B7,C7-4))

Enter this formula in E12:E15: =DATE(A7,B7,C7)-WEEKDAY(DATE(A7,B7,C7-5))

Enter this formula in E17:E20: =DATE(A7,B7,C7)-WEEKDAY(DATE(A7,B7,C7-6))

JD Sartain / IDG Worldwide

Now that you have the actual event dates for the four weeks of the month, you can enter formulas that reveal which event is scheduled for each of those four weeks, the location of each event, and which journalist/videographer team is scheduled to cover those events.

6. The formulas for column F (EVENT) change with each month.

Enter this formula in F2:F5:

=IF(C2=8,”Ice Hockey”,IF(C2=15,”Snow Skiing”,IF(C2=22,”Figure Skating”,IF(C2=29,”Bobsledding”,0))))

Enter this formula in F7:F10:

=IF(C7=8,”Speed Skating”,IF(C7=15,”Curling”,IF(C7=22,”Dog Sled Races”,IF(C7=29,”Biathlon”,0))))

Enter this formula in F12:F15:

=IF(C12=8,”Golf”,IF(C12=15,”Horse Races”,IF(C12=22,”Fencing”,IF(C12=29,”Rugby”,0))))

Enter this formula in F17:F20:

=IF(C2=8,”Soccer”,IF(C2=15,”Tennis”,IF(C2=22,”Softball”,IF(C2=29,”Basketball”,0))))

7. Enter the formulas for column G, the cities/LOCATION where each event is held.

Enter this formula in G2:G5:

=IF(C2=8,”Montreal”,IF(C2=15,”Zermatt”,IF(C2=22,”Brussels”,IF(C2=29,”Edinburgh”,0))))

Enter this formula in G7:G10:

=IF(C7=8,”Amsterdam”,IF(C7=15,”Edinburgh”,IF(C7=22,”Finnmark”,IF(C7=29,”Oberhof”,0))))

Enter this formula in G12:G15:

=IF(C12=8,”Dublin”,IF(C12=15,”Melbourne”,IF(C12=22,”Sochi”,IF(C12=29,”Auckland”,0))))

Enter this formula in G17:G20:

=IF(C2=8,”London”,IF(C2=15,”Paris”,IF(C2=22,”Cologne”,IF(C2=29,”Rome”,0))))

JD Sartain / IDG Worldwide

8. For column H (the countries/LOCATION), you can manually enter the countries that match the cities in column G or work out a formula yourself to automatically enter the country that matches the city. However, you MUST enter the corresponding countries in column H or the formulas in column I (JOURNALIST/VIDEOGRAPHER) will fail.

HINT: For starters, you should create a table off to the side that lists all the countries and, for future formulas, number the countries from 1 through 14.

9. The last formula (column I) reveals which team (JOURNALIST/VIDEOGRAPHER) will cover which events; for example Team 1 covers Germany, Norway, the Netherlands, and Belgium.

Enter this (same) formula in all of the four-block rows in column I (yes, it is one long formula):

=IF(OR(H2=”Germany”,H2=”Norway”,H2=”Netherlands”,H2=”Belgium”),”Team 1”,IF(OR(H2=”Switzerland”,H2=”Italy”,H2=”Russia”),”Team 2”,IF(OR(H2=”Scotland”,H2=”Ireland”,H2=”England”,H2=”France”),”Team 3”,IF(OR(H2=”Canada”,H2=”Australia”,H2=”New Zealand”),”Team 4”,0))))

JD Sartain / IDG Worldwide

10. Use the countries table you created in number 8 above to simplify the formula in column I; for example 1= Australia, 2 = Belgium, 3 = Canada, etc. (it’s still long, but much shorter than the original). Note that formulas can only be 8,192 characters long, which really is a lot, but managing and/or editing extremely long formulas is a nightmare. Imagine sifting through 8000 characters to find and correct an error.

11. First, you must enter the correct country number in column J. You can also write a custom formula to perform this task as well. Note that each IF statement is followed by a series of OR conditions, which allows you to assign several countries to each team.

=IF(OR(J17=6,J17=11,J17=9,J17=2),”Team 1”,IF(OR(J17=14,J17=8,J17=12),”Team 2”,IF(OR(J17=13,J17=7,J17=4,J17=5),”Team 3”,IF(OR(J17=3,J17=1,J17=10),”Team 4”,0))))

NOTE: It’s always advisable and much more efficient to create tables with numbered entries as opposed to “hardcoding” the data into the formulas. By using country numbers instead of country names, you can add, delete, or change countries by just modifying the country table.

For example, in 2020, the event held in Canada moved to Sweden. Instead of editing all of your formulas to replace Canada with Sweden, you just enter Sweden into the slot (number 3) where Canada used to be. And, if you assign the journalists and videographers to numbered teams, you can easily change the members of each team without re-writing your formulas.

JD Sartain / IDG Worldwide

12. Consider color-coding certain elements of your spreadsheet so it’s easier to scan the information quickly for immediate retrieval when presenting your ideas to clients, co-workers, and corporate executives. Creating charts for your spreadsheets also help to convey your message.