Excel’s SUM, DATE, WEEKDAY, IF, Nested IF, and IF/OR purposes got here to thoughts as I used to be looking at a 1969 movie known as *If It’s Tuesday, This Will have to Be Belgium,* about American vacationers on a whirlwind excursion of Europe. It happened to me that businesses are ceaselessly required to create schedules in response to the weeks (versus the times) in a month—as an example week1, week2, week3, and so forth. This may entail the usage of a date components to spot each and every week in a given month, plus a chain of nested IF/OR statements to assign duties and workforce individuals to finish the ones duties. The usage of Excel as a scheduling device is a smart talent to have underneath your belt.

Believe that you simply paintings for that covers wearing occasions far and wide the sector, and it’s your activity to create the time table for the newshounds and videographers that duvet those occasions. For instance, at the 3rd Tuesday of a given month, the development is work skating and the positioning is Belgium; at the fourth Tuesday, it’s bobsledding in Scotland; at the first Thursday, it’s golfing in Eire; and at the remaining Thursday, it’s rugby in New Zealand. The formulation underneath can flip this bulky, time-consuming chore right into a easy, fast and simple process.

We’ve integrated a downloadable spreadsheet you’ll use to follow those talents:

Spreadsheet for finding out the next purposes: DATE, WEEKDAY, IF, and IF/OR. JD Sartain

## Formulation/purposes used on this article

### 1. SUM

Syntax: =SUM(A2:A10); =SUM(four+five); =SUM(A2+A3)-A4; =SUM(A2-A3)+(A4-A5)

Outline: The SUM serve as is relatively flexible. It may possibly used so as to add, subtract, multiply, divide, and/or carry out dozens of alternative calculations.

### 2. DATE

Syntax: =DATE(yr,month,day)

Outline: Supplies a date in response to 3 values: yr, month, day.

### three. WEEKDAY

Syntax: =WEEKDAY(serial_number, [return_type])

Outline: This serve as supplies an Excel serial quantity for the weekday and go back kind is a bunch between 1 and seven that represents every day of week. For instance:

Code |
= |
Day of Week |

1 |
= |
Sunday |

2 |
= |
Monday |

three |
= |
Tuesday |

four |
= |
Wednesday |

five |
= |
Thursday |

6 |
= |
Friday |

7 |
= |
Saturday |

### four. IF commentary

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

Outline: If the commentary is right, then do A; else/another way do B. For instance, if it’s raining, then shut the home windows, else/another way go away the home windows open. (To peer extra examples, see our tale on getting began with Excel IF statements.)

### five. Nested IF commentary

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

Outline: If the commentary is right, then do A, If the commentary is right, then do B, If the commentary is right, then do C, else/another way do D.

### 6. IF commentary with OR situation

Syntax: =IF (see above)

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

Outline: =OR(is situation 1 true, OR situation 2 true, OR situation three true; and so forth.

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

**NOTE:** Be mindful, **OR** manner if situation A or B or C is right, then resolution YES; if none of those are true, resolution NO. **AND** manner A, B, and C will have to ALL be true to get a YES; but when just one is right and the opposite two aren’t true (false), then the solution is NO.

## Construct the Spreadsheet

1. First, let’s temporarily construct the spreadsheet. Input the next headers in columns A via 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 only column header WEEK. Columns G and H are the similar (one merged column header titled LOCATION). See spreadsheet underneath for main points.

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

^{th}## 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))**

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

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

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.

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.