Formula 2 – Use Excel’s DATE, WEEKDAY, IF, and IF/OR to make weekly schedules by task, event and team member

Formula 2 – 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 functions came to mind as I was watching a 1969 film called If It’s Tuesday, This Must Be Belgium, about American tourists on a whirlwind tour of Europe. It occurred to me that companies are often required to create schedules based on the weeks (as opposed to the days) in a month—for example week1, week2, week3, etc. This would entail using a date formula to identify each week in a given month, plus a series of nested IF/OR statements to assign tasks and team members to complete those tasks. Using Excel as a scheduling tool is a great skill to have 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 schedule for the journalists and videographers that cover these events. For example, on the third Tuesday of a given month, the event is figure skating and the location is Belgium; on the fourth Tuesday, it’s bobsledding in Scotland; on the first Thursday, it’s golf in Ireland; and on the last Thursday, it’s rugby in New Zealand. The formulas below can turn this cumbersome, time-consuming chore into a simple, quick and easy task.

We’ve included a downloadable spreadsheet you can use to practice these skills: 

download

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; =SUM(A2-A3)+(A4-A5)

Define: The SUM function is quite versatile. It can used to add, subtract, multiply, divide, and/or perform dozens of other calculations.

2. DATE

Syntax: =DATE(year,month,day)

Define: Provides a date based on three values: year, month, day.

3. WEEKDAY

Syntax: =WEEKDAY(serial_number, [return_type])



Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.