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

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 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: 

obtain

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

01 codes for days of the week and months of the year JD Sartain / IDG International

Codes for days of the week and months of the yr

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.

02 spreadsheet column headers 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 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 1st, 2nd, 3rd, 4th 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))

03 formulas week column c date column e 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))))

04 formulas for events event locations 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))))

05 formulas for teams cover event 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.

06 country and team tables 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.

To comment on this article and other PCWorld content, visit our Facebook page or our Twitter feed.

http://platform.twitter.com/widgets.js

About thebreakingnewsheadlines

Check Also

1542439061 pokemon go meltan research quest and lets go pikachu eevee launch event now live - Pokemon Go: Meltan Research Quest And Let's Go Pikachu / Eevee Launch Event Now Live

Pokemon Go: Meltan Research Quest And Let's Go Pikachu / Eevee Launch Event Now Live

The most recent pair of Pokemon adventures, Let’s Move Pikachu and Let’s Move Eevee, are …

Leave a Reply

Your email address will not be published. Required fields are marked *

Congratulations! You have successfully installed the Catfish Manager plugin!

Go to the administration panel to change this text, the options and to set custom CSS to change how the advert looks.

Close
%d bloggers like this: