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:

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.)

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.

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

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.

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
Formulas-Week of month column C, Date for column E

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
Formulas for the events & the event locations

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
Formulas for the teams that cover each event

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
Country and team tables

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.