Excels SUM, DATO, WEEKDAG, IF, Nested IF and IF / OR features came in mind when I watched a 1969 movie called If it's Tuesday, this must be Belgium, about American tourists on a whirlwind tour in Europe. It occurred to me that companies often have to create schedules based on the weeks (as opposed to the days) for a month – such as week1, week2, week3, etc. This would mean using a date formula to identify each week in a given month, plus a series of recent IF / OR rates to assign tasks and team members to complete these tasks. Using Excel as scheduling tools is a great skill to wear under your belt.
Imagine working for a magazine that covers sporting events all over the world, and it's your job to create schedules for journalists and videographers that cover these events. For example, on the third Tuesday of a certain month, the event is ice skating and the place is Belgium; The fourth Tuesday is the bobsledding in Scotland; The first Thursday there is golf in Ireland; and last Thursday it is rugby in New Zealand. The formulas below can make this difficult and time-consuming task a simple, quick and easy task.
We have included a downloadable spreadsheet that you can use to exert these skills:
Formulas / Features Used In This Article
Syntax: = SUM (A2: A10); = SUMMARY (4 + 5); = SUMMARY (A2 + A3) -A4; = SUM (A2-A3) + (A4-A5)
Define: The SUM function is quite versatile. It can be used to add, subtract, multiply, divide and / or perform dozens of other calculations.
Syntax: = DATE (year, month, day)
Define: Provides a date based on three values: year, month, day.
Syntax: = WEEKDAY (serial_number, [return_type])
Define: This feature provides an Excel serial number for the weekday and the return type is a number between 1 and 7 representing each day of the week. For example:
=    =  Wednesday  5  =  Thursday  6  =  Friday  7  =  Saturday
4. IF statement
Syntax: = IF (logic_test, value_if true, value_if_false)
Define: If the statement is true then make A; otherwise, or else B. If, for example, it is raining, close windows, or else you'll leave windows open. (For more examples, see our story about 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 statement is true then make A, If Statement is true, then B, if the statement is true, then do C, otherwise / else D.
6. IF mood with OR conditions
Syntax: = IF (see above)
Syntax: = OR (logical1, [logical2] …)
Define: = OR, or condition 3 true, etc.
= IF (A2 = 6, B2 = 11, C2 = 9, D2 = 2), "YES", "NO")
OR means that condition A or B or C is true, then answer YES, unless none of these is true, answer NO. AND means A, B and C, ALL must 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. Let's first build the spreadsheet. Enter the following headings in columns A through I: (A) YEAR, (B) MONTH, (C) WEEK (D) WEEK, (E) DATE, (F) EVENT, (G) PLACE Columns G and H is the same (a compound column heading titled LOCATION). See the spreadsheet below to check the columns C and D. Details.
2. In column A, enter the year 2019 from A2 to A20, skip every fifth row; that is, every fifth line is empty (only for aesthetics). In column B: Enter number 1 for January in B2: B5; number 2 for february in b7: b10; number 3 for March in B12: B15; and number 4 for April in B17: B20.
3rd In column D, you indicate 1 st 2 nd 3 rd 4 19659062] in each quadrilateral block; it's D2: D5; D7: D10; D12: D15; and D17: D20. The remaining columns are formulas except column H, which may be a formula or you can only enter the country that matches the city in column G. Since we have more than enough formulas for this spreadsheet, I leave this column for 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 that defines the week number of each month and can be entered in any of the four different syntax sets: = 1 + 7 * 1 ; or = SUM (1 + 7 * 2) ; or = SUM (7 * 3 + 1) ; or = SUM (7 * 4) +1 .
2nd In English: a plus seven, times one, corresponds to 8, which corresponds to the first week of the month; one plus seven, twice two, corresponds to 15 (second week); a plus seven, times 3, equals 22 (third week); and a plus seven, four times, corresponds to 29 (fourth week); and so on if there are five weeks.
3rd Enter these four formulas in the first four block; it's C2: C5. The result will be 8, 15, 22 and 29. Copy these four lines down to the lines C7: C10; C12: C15; and C17: C20.
NOTE: Column D, which you have already specified (instructions above), is unnecessary for calculations or understanding of this worksheet. There is only for aesthetics.
4th The formula in column E (DATE) determines DATE from columns A, B and C, then subtract WEEKDAY DATE A, B, and C minus the weekday number; ie 3 for Tues, 4 for Wednesday, etc. (see diagram above under Formulas / Functions, # 3 Weekday). Remember to enter, then copy.
5th 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) -WEEKDAG (DATE (A7, B7, C7-4))
Enter this formula in E12: E15: = DATE (A7, B7, C7) -WARNING DAY (DATE (A7, B7, C7-5))
Enter this formula in E17: E20: = DATE (A7, B7, C7) WEEKDAY (DATE (A7, B7 , C7 -6))
Now that you have current event date for During the four weeks of the month, you can specify formulas that indicate which event is scheduled for each of the four weeks, each of the event's location and which journalist / videographer team is scheduled to cover these events.
6th The formulas for column F (EVENT) are changed with each month.
Enter this formula in F2: F5:
= IF (C2 = 8, "Ice Hockey", IF (C2 = 15, "Snow Skiing", IF (C2 = 22, "Ice 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" 15, "Tennis", IF (C2 = 22, "Softball", IF = 29, "Basketball", 0))))  7. Enter the formulas for column G, cities / PLACE 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))) 59035]
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 = 29, "Auckland", 0)))
Enter this formula in G17: G20:
= IF (C2 = 8, "London", IF (C2 = 15, "Paris", IF (C2 = 22, "Cologne", IF (C2 = 29, "Rom", 0))]]
8. For column H (countries / LOCATION) you can manually enter the countries that match the cities in column G or create 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.
TIP: To begin with, you should create a table for the page listed all countries and, for future formulas, number the countries from 1 to 14.
9. The last formula (column I) reveals which team (JOURNALIST / VIDEOGRAFER) will cover which events; For example Team 1 covers Germany, Norway, the Netherlands and Belgium.
Enter this (same) formula in all four block rows in column I (yes it is a 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 = "Scotland", H2 = "Ireland", H2 = "England", H2 = "France"), "Team 3", IF OR (H2 = "Canada", H2 = "Australia", H2 = "New Zealand" ), "Team 4", 0))]]
10. Use the table countries you created in number 8 above to simplify the formula in column I; for example 1 = Australia, 2 = Belgium, 3 = Canada, etc. (it is still far but much shorter than the original). Note that the formulas can only be 8,192 characters long, which is really a lot, but handling and / or editing extremely long formulas is a nightmare. Imagine looking through 8000 characters to find and correct an error.
eleventh First, you must enter the correct area code in column J. You can also type a custom formula to perform this task as well. Please note that each IF statement is followed by a series of OR Terms, which allow you to assign multiple countries to each team.
= IF (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 is always advisable and highly efficient to create tables with numbered entries as opposed to "hard encoding" of data to formulas. By using country code instead of country name, you can add, delete or change countries by just changing the country table.
For example, in 2020, the event moved to Canada to Sweden. Instead of editing all your forms to replace Canada with Sweden, you only enter Sweden to the place (number 3) where Canada used to be. And if you assign journalists and videographers to numbered teams, you can easily change the members of each team without rewriting your forms.
12. Consider color coding of some sections in the spreadsheet so it's easier to scan the information fast for instant download when you present your ideas for customers, coworkers and business executives. Creating charts for your worksheets also helps to convey your message.