Excel’s SUM, DATE, WEEKDAY, IF, Nested IF, and IF/OR capabilities got here to thoughts as I used to be watching a 1969 movie known as If It’s Tuesday, This Should Be Belgium, about American vacationers on a whirlwind tour of Europe. It occurred to me that firms are sometimes required to create schedules based mostly on the weeks (versus the times) in a month—for instance week1, week2, week3, and many others. This may entail utilizing a date method to determine every week in a given month, plus a collection of nested IF/OR statements to assign duties and group members to finish these duties. Utilizing Excel as a scheduling instrument is a superb ability to have below your belt. Think about that you just work for that covers sporting occasions everywhere in the world, and it’s your job to create the schedule for the journalists and videographers that cowl these occasions. For instance, on the third Tuesday of a given month, the occasion is determine skating and the placement is Belgium; on the fourth Tuesday, it’s bobsledding in Scotland; on the primary Thursday, it’s golf in Eire; and on the final Thursday, it’s rugby in New Zealand. The formulation under can flip this cumbersome, time-consuming chore right into a easy, fast and straightforward job.We’ve included a downloadable spreadsheet you should utilize to apply these abilities:  obtain

Spreadsheet for studying the next capabilities: DATE, WEEKDAY, IF, and IF/OR. JD Sartain
Formulation/capabilities used on this article1. SUMSyntax: =SUM(A2:A10); =SUM(Four+5); =SUM(A2+A3)-A4; =SUM(A2-A3)+(A4-A5)Outline: The SUM perform is sort of versatile. It could used so as to add, subtract, multiply, divide, and/or carry out dozens of different calculations.2. DATESyntax: =DATE(yr,month,day)Outline: Supplies a date based mostly on three values: yr, month, day.Three. WEEKDAYSyntax: =WEEKDAY(serial_number, [return_type])Outline: This perform offers an Excel serial quantity for the weekday and return kind is a quantity between 1 and seven that represents every day of week. For instance:
Code

=

Day of Week

1

=

Sunday

2

=

Monday

Three

=

Tuesday

Four

=

Wednesday

5

=

Thursday

6

=

Friday

7

=

Saturday

JD Sartain / IDG Worldwide
Codes for days of the week and months of the yr
Four. IF statementSyntax: =IF(logic_test, value_if true, value_if_false)Outline: If the assertion is true, then do A; else/in any other case do B. For instance, if it’s raining, then shut the home windows, else/in any other case depart the home windows open. (To see extra examples, see our story on getting began with Excel IF statements.)5. Nested IF statementSyntax: =IF(logic_test, value_if true, IF(logic_test, value_if true, IF(logic_test, value_if true, value_if_false)))Outline: If the assertion is true, then do A, If the assertion is true, then do B, If the assertion is true, then do C, else/in any other case do D.6. IF assertion with OR conditionSyntax: =IF (see above)Syntax: =OR(logical1,[logical2]…)Outline: =OR(is situation 1 true, OR situation 2 true, OR situation Three true; and many others.=IF(OR(A2=6,B2=11,C2=9,D2=2),”YES”,”NO”)NOTE: Keep in mind, OR means if situation A or B or C is true, then reply YES; if none of those are true, reply NO. AND means A, B, and C should ALL be true to get a YES; but when just one is true and the opposite two aren’t true (false), then the reply is NO.Construct the Spreadsheet1. First, let’s shortly construct the spreadsheet. Enter the next headers in columns A by way of 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 identical (one merged column header titled LOCATION). See spreadsheet under for particulars. JD Sartain / IDG Worldwide
Spreadsheet column headers
2. In column A, enter the yr 2019, from A2 by way of A20, skipping each fifth row; that’s, each fifth row is clean (for aesthetics solely). In column B: Enter the #1 for January in B2:B5; the quantity 2 for February in B7:B10; the quantity Three for March in B12:B15; and the quantity Four for April in B17:B20.Three. In column D, enter 1st, 2nd, third, 4th in every four-row block; that’s D2:D5; D7:D10; D12:D15; and D17:D20. The remaining columns are formulation besides column H, which could possibly be a method, or you’ll be able to simply manually enter the nation that matches the town in column G. As a result of we now have greater than sufficient formulation for this spreadsheet, I’ll depart this column to your discretion. See in the event you can decide what one of the best method for this column can be after which enter it in column G.Enter the formulas1. The method for column C (WEEK) is a SUM perform, which defines the week quantity in every month and will be entered in any one of many 4 totally different syntax statements: =1+7*1; or =SUM(1+7*2); or =SUM(7*Three+1); or =SUM(7*Four)+1.2. In English: one plus seven, instances one, equals eight, which corresponds to the primary week of the month; one plus seven, instances two, equals 15 (second week); one plus seven, instances Three, equals 22 (third week); and one plus seven, instances 4, equals 29 (fourth week); and so forth if there are 5 weeks.Three. Enter these 4 formulation within the first four-row block; that’s C2:C5. The end result will probably be eight, 15, 22, and 29. Copy these 4 rows all the way down to rows C7:C10; C12:C15; and C17:C20.NOTE: Column D, which you may have already entered (directions above), is pointless for the calculations or understanding of this worksheet. It’s there for aesthetics solely.Four. The method 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 quantity; i.e., Three for Tues, Four for Wed, and many others. (see chart above below Formulation/Capabilities, #Three Weekday). Keep in mind to enter, then copy.5. Enter this method in E2:E5: =DATE(A2,B2,C2)-WEEKDAY(DATE(A2,B2,C2-Three))Enter this method in E7:E10: =DATE(A7,B7,C7)-WEEKDAY(DATE(A7,B7,C7-Four))Enter this method in E12:E15: =DATE(A7,B7,C7)-WEEKDAY(DATE(A7,B7,C7-5))Enter this method in E17:E20: =DATE(A7,B7,C7)-WEEKDAY(DATE(A7,B7,C7-6)) JD Sartain / IDG Worldwide
Formulation-Week of month column C, Date for column E
Now that you’ve got the precise occasion dates for the 4 weeks of the month, you’ll be able to enter formulation that reveal which occasion is scheduled for every of these 4 weeks, the placement of every occasion, and which journalist/videographer group is scheduled to cowl these occasions.6. The formulation for column F (EVENT) change with every month.Enter this method in F2:F5:=IF(C2=eight,”Ice Hockey”,IF(C2=15,”Snow Snowboarding”,IF(C2=22,”Determine Skating”,IF(C2=29,”Bobsledding”,zero))))Enter this method in F7:F10:=IF(C7=eight,”Velocity Skating”,IF(C7=15,”Curling”,IF(C7=22,”Canine Sled Races”,IF(C7=29,”Biathlon”,zero))))Enter this method in F12:F15:=IF(C12=eight,”Golf”,IF(C12=15,”Horse Races”,IF(C12=22,”Fencing”,IF(C12=29,”Rugby”,zero))))Enter this method in F17:F20:=IF(C2=eight,”Soccer”,IF(C2=15,”Tennis”,IF(C2=22,”Softball”,IF(C2=29,”Basketball”,zero))))7. Enter the formulation for column G, the cities/LOCATION the place every occasion is held.Enter this method in G2:G5:=IF(C2=eight,”Montreal”,IF(C2=15,”Zermatt”,IF(C2=22,”Brussels”,IF(C2=29,”Edinburgh”,zero))))Enter this method in G7:G10:=IF(C7=eight,”Amsterdam”,IF(C7=15,”Edinburgh”,IF(C7=22,”Finnmark”,IF(C7=29,”Oberhof”,zero))))Enter this method in G12:G15:=IF(C12=eight,”Dublin”,IF(C12=15,”Melbourne”,IF(C12=22,”Sochi”,IF(C12=29,”Auckland”,zero))))Enter this method in G17:G20:=IF(C2=eight,”London”,IF(C2=15,”Paris”,IF(C2=22,”Cologne”,IF(C2=29,”Rome”,zero)))) JD Sartain / IDG Worldwide
Formulation for the occasions & the occasion places
eight. For column H (the nations/LOCATION), you’ll be able to manually enter the nations that match the cities in column G or work out a method your self to mechanically enter the nation that matches the town. Nonetheless, you MUST enter the corresponding nations in column H or the formulation in column I (JOURNALIST/VIDEOGRAPHER) will fail.HINT: For starters, you must create a desk off to the aspect that lists all of the nations and, for future formulation, quantity the nations from 1 by way of 14.9. The final method (column I) reveals which group (JOURNALIST/VIDEOGRAPHER) will cowl which occasions; for instance Staff 1 covers Germany, Norway, the Netherlands, and Belgium.Enter this (identical) method in the entire four-block rows in column I (sure, it’s one lengthy method):=IF(OR(H2=”Germany”,H2=”Norway”,H2=”Netherlands”,H2=”Belgium”),”Staff 1”,IF(OR(H2=”Switzerland”,H2=”Italy”,H2=”Russia”),”Staff 2”,IF(OR(H2=”Scotland”,H2=”Eire”,H2=”England”,H2=”France”),”Staff Three”,IF(OR(H2=”Canada”,H2=”Australia”,H2=”New Zealand”),”Staff Four”,zero)))) JD Sartain / IDG Worldwide
Formulation for the groups that cowl every occasion
10. Use the nations desk you created in quantity eight above to simplify the method in column I; for instance 1= Australia, 2 = Belgium, Three = Canada, and many others. (it’s nonetheless lengthy, however a lot shorter than the unique). Observe that formulation can solely be eight,192 characters lengthy, which actually is lots, however managing and/or enhancing extraordinarily lengthy formulation is a nightmare. Think about sifting by way of 8000 characters to seek out and proper an error.11. First, you could enter the right nation quantity in column J. You too can write a customized method to carry out this job as nicely. Observe that every IF assertion is adopted by a collection of OR situations, which lets you assign a number of nations to every group.=IF(OR(J17=6,J17=11,J17=9,J17=2),”Staff 1”,IF(OR(J17=14,J17=eight,J17=12),”Staff 2”,IF(OR(J17=13,J17=7,J17=Four,J17=5),”Staff Three”,IF(OR(J17=Three,J17=1,J17=10),”Staff Four”,zero))))NOTE: It’s all the time advisable and far more environment friendly to create tables with numbered entries versus “hardcoding” the info into the formulation. By utilizing nation numbers as a substitute of nation names, you’ll be able to add, delete, or change nations by simply modifying the nation desk.For instance, in 2020, the occasion held in Canada moved to Sweden. As a substitute of enhancing your entire formulation to exchange Canada with Sweden, you simply enter Sweden into the slot (quantity Three) the place Canada was. And, in the event you assign the journalists and videographers to numbered groups, you’ll be able to simply change the members of every group with out re-writing your formulation. JD Sartain / IDG Worldwide
Nation and group tables
12. Take into account color-coding sure components of your spreadsheet so it’s simpler to scan the data shortly for fast retrieval when presenting your concepts to purchasers, co-workers, and company executives. Creating charts on your spreadsheets additionally assist to convey your message.

To touch upon this text and different PCWorld content material, go to our Fb web page or our Twitter feed.

Shop Amazon