Use Excel’s DATE, WEEKDAY, IF, and IF/OR to make weekly schedules by task, event and team member - allisonwitheat
Excel's SUM, DATE, WEEKDAY, IF, Nested IF, and IF/OR functions came to mind as I was watching a 1969 film called If Information technology's Tuesday, This Must Be Belgium, about American tourists connected a whirlwind tour of EEC. IT occurred to me that companies are a great deal required to create schedules based on the weeks (arsenic opposed to the years) in a month—for example week1, week2, week3, etc. This would entail using a go out formula to identify every week in a given calendar month, plus a series of nested IF/OR statements to allot tasks and team members to complete those tasks. Using Excel A a scheduling tool is a peachy skill to induce 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 agenda for the journalists and videographers that traverse these events. For instance, on the third base Tuesday of a given calendar month, the event is figure skating and the locating is Belgium; on the fourth Tuesday, it's bobsledding in Scotland; on the 1st Thursday, it's golf game in Ireland; and along the last Thursday, it's rugby in New Zealand. The formulas below stool turn this cumbersome, sentence-consuming chore into a simple, quick and effortless task.
We've enclosed a downloadable spreadsheet you can usance to recitation these skills:
Spreadsheet for learning the following functions: DATE, WEEKDAY, IF, and IF/OR. JD Sartain
Formulas/functions used in this article
1. SUM
Syntax: =SUM(A2:A10); =SUM(4+5); =SUM(A2+A3)-A4; =Summate(A2-A3)+(A4-A5)
Define: The Tot function is quite versatile. It can misused to ADD, subtract, reproduce, divide, and/or perform dozens of past calculations.
2. DATE
Syntax: =See(year,calendar month,Day)
Define: Provides a date supported leash values: year, month, day.
3. WEEKDAY
Syntax: =WEEKDAY(serial_number, [return_type])
Define: This function provides an Excel serial add up for the time unit and replication type is a number between 1 and 7 that represents each sidereal day of week. For example:
| Code | = | Day of Hebdomad |
| 1 | = | Sunday |
| 2 | = | Monday |
| 3 | = | Tuesday |
| 4 | = | Wednesday |
| 5 | = | Th |
| 6 | = | Friday |
| 7 | = | Sat |
JD Sartain / IDG Worldwide Codes for days of the week and months of the year
4. IF assertion
Syntax: =IF(logic_test, value_if admittedly, value_if_false)
Define: If the instruction is true, then do A; other/other than do B. For example, if it's raining, then neighbouring the Windows, else/otherwise leave the windows open. (To watch more examples, visit our story on getting started with Excel IF statements.)
5. Nested IF statement
Phrase structure: =IF(logic_test, value_if true, IF(logic_test, value_if apodictic, IF(logic_test, value_if true, value_if_false)))
Define: If the instruction is true, and then do A, If the statement is true, and then do B, If the statement is true, then perform C, other/otherwise do D.
6. IF statement with OR condition
Syntax: =IF (see above)
Syntax: =Beaver State(logical1,[logical2]…)
Define: =OR(is condition 1 avowedly, OR condition 2 true, OR condition 3 true; etc.
=IF(Operating theater(A2=6,B2=11,C2=9,D2=2),"YES","NO")
Musical note: Remember, OR means if condition A or B or C is typical, then answer YES; if no of these are reliable, answer NO. AND means A, B, and C must ALL be faithful get a YES; but if only unrivalled is honorable and the other two are non true (imitation), 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) Particular date, (F) EVENT, (G) Locating—(H) LOCATION, (I) Diary keeper/ VIDEOGRAPHER. Columns C and D are merged with the one newspaper 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 Spreadsheet column headers
2. In column A, enter the year 2019, from A2 through A20, skipping every fifth row; that is, every fifth row is lacuna (for aesthetics only). In column B: Enter the number 1 for Jan 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 1st , 2nd , 3rd , 4th in from each one four-row freeze; that is D2:D5; D7:D10; D12:D15; and D17:D20. The leftover columns are formulas except pillar H, which could be a formula, or you can evenhanded 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 circumspection. Realize if you can mold what the best formula for this column would be and and then enter it in column G.
Insert the formulas
1. The rul for column C (WEEK) is a Pith function, which defines the week number in each month and can personify entered in any matchless of the four different sentence structure statements: =1+7*1; or =Heart and soul(1+7*2); or =SUM(7*3+1); or =Join(7*4)+1.
2. In English: one plus vii, times one, equals 8, which corresponds to the initial week of the calendar month; one plus septet, multiplication two, equals 15 (second hebdomad); cardinal plus cardinal, times 3, equals 22 (tierce week); and one plus seven, times cardinal, equals 29 (fourth week); and so on if there are five weeks.
3. Enter these foursome formulas in the first tetrad-row block; that is C2:C5. The result testament be 8, 15, 22, and 29. Copy these 4 rows down to rows C7:C10; C12:C15; and C17:C20.
Observe: Column D, which you sustain already entered (instructions higher up), is unnecessary for the calculations or savvy of this worksheet. It's there for aesthetics only.
4. The formula in column E (Particular date) determines the DATE from columns A, B, and C, so subtracts the WEEKDAY DATE A, B, and C minus the Day of the Week number; i.e., 3 for Tuesday, 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: =Day of the month(A7,B7,C7)-WEEKDAY(See(A7,B7,C7-4))
Enter this formula in E12:E15: =DATE(A7,B7,C7)-WEEKDAY(Appointment(A7,B7,C7-5))
Put down this formula in E17:E20: =DATE(A7,B7,C7)-WEEKDAY(Particular date(A7,B7,C7-6))
JD Sartain / IDG Universal Formulas-Hebdomad of month column C, Date for column E
Now that you have the genuine 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 result, and which diarist/videographer team is regular to cover those events.
6. The formulas for column F (EVENT) change with monthly.
Infix this formula in F2:F5:
=IF(C2=8,"Glass Hockey gam",IF(C2=15,"Snow Skiing",IF(C2=22,"Figure Skating",IF(C2=29,"Bobsledding",0))))
Enter this convention in F7:F10:
=IF(C7=8,"Speed Skating",IF(C7=15,"Curling",IF(C7=22,"Dogsled Races",IF(C7=29,"Biathlon",0))))
Enter this formula in F12:F15:
=IF(C12=8,"Golf",IF(C12=15,"Sawhorse Races",IF(C12=22,"Fencing",IF(C12=29,"Rugby",0))))
Get in this formula in F17:F20:
=IF(C2=8,"Association football",IF(C2=15,"Tennis",IF(C2=22,"Softball",IF(C2=29,"Basketball",0))))
7. Enter the formulas for column G, the cities/Localisation 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 upon this rul in G12:G15:
=IF(C12=8,"Dublin",IF(C12=15,"Melbourne",IF(C12=22,"Sochi",IF(C12=29,"Auckland",0))))
Figure this formula in G17:G20:
=IF(C2=8,"London",IF(C2=15,"Paris",IF(C2=22,"Eau de 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 editorial G surgery work out a chemical formula yourself to automatically record the country that matches the city. Even so, you MUST enter the commensurate countries in column H operating theater the formulas in editorial I (Diarist/VIDEOGRAPHER) will fail.
HINT: For starters, you should create a put of off to the slope that lists all the countries and, for prospective formulas, come the countries from 1 through 14.
9. The finally formula (column I) reveals which team up (JOURNALIST/VIDEOGRAPHER) will overlay which events; for example Team 1 covers Germany, Norway, the Netherlands, and Belgium.
Enter this (comparable) formula in all of the four-block rows in editorial I (yes, it is one long formula):
=IF(OR(H2="Germany",H2="Norway",H2="Netherlands",H2="Kingdom of Belgium"),"Team 1",IF(OR(H2="Switzerland",H2="Italy",H2="Russia"),"Team 2",IF(Oregon(H2="Scotland",H2="Ireland",H2="England",H2="French Republic"),"Team 3",IF(OR(H2="Canada",H2="Australia",H2="New Seeland"),"Team 4",0))))
JD Sartain / IDG Ecumenical Formulas for the teams that cover each event
10. Use the countries shelve 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 durable, but so much shorter than the original). Billet that formulas can only be 8,192 characters long, which really is a great deal, but managing and/OR editing extremely long-term formulas is a nightmare. Reckon sifting through 8000 characters to find and true an error.
11. First, you must enter the correct country number in column J. You can also compose a custom formula to perform this project as well. Banker's bill that all IF statement is followed past 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 up 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))))
Distinction: It's forever advisable and much more efficient to produce tables with numbered entries every bit anti to "hardcoding" the data into the formulas. Away using country numbers instead of country names, you can ADHD, erase, or change countries by just modifying the state table.
For example, in 2020, the event held in Canada moved to Sweden. Alternatively of editing all of your formulas to replace Canada with Sweden, you impartial figure Sweden into the slot (number 3) where Canada wont 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 Nation and team up tables
12. Consider color-coding certain elements of your spreadsheet sol information technology's easier to scan the data quickly for immediate retrieval when presenting your ideas to clients, co-workers, and corporate executives. Creating charts for your spreadsheets likewise help to carry your message.
Source: https://www.pcworld.com/article/402780/excel-date-weekday-if-and-ifor-make-weekly-schedules-by-task-event-and-team-member.html
Posted by: allisonwitheat.blogspot.com
0 Response to "Use Excel’s DATE, WEEKDAY, IF, and IF/OR to make weekly schedules by task, event and team member - allisonwitheat"
Post a Comment