Excel project: weekly calendar Aug04 '04

A brief explanation of the project

I recently had to create a schedule for employees – one that utilizied Excel’s "spreadsheet look," along with automatic calendar information. The person in charge of releasing the schedule didn’t want to worry about filling in the dates for each week.

"It would be much easier to have the current dates for each day already be there," they said. "I don’t want to have to type them in every week."

The schedule was requested to look something like this:

What I’m trying to accomplish

The only things that should be manually entered are the employee names on the left, and the weekday NAMES at the very top. The DATES right below the weekday names should NOT have to be manually entered. They should automatically be generated.

I asked myself how I could do this.

Excel’s dynamic functions

Among Excel’s hundreds of built–in functions, there is one that displays the current date. By simply typing the following function into the formula bar:

=TODAY()

... the current cell will display the current date, as such:

This is just one example of a dynamic function, which will always be accurate. In this case, the function will always display the current date, without any user intervention. And this is the goal of this project – to have each day of the current week dynamically displayed.

First problem

So it’s easy to display the current date. What about the other days AROUND the current date? And, by having access to the current date, how would I know what DAY OF THE WEEK it referred to? These issues I needed to resolve first.

The main question was straight–forward: How could I display the current DATE in the cell below the corresponding WEEKDAY?

In other words, how could I tell Excel which CELL to display the TODAY() function?

Another helpful function

In order to do this, I had to use another helpful Excel function. WEEKDAY() returns the NUMBER of the current weekday. In other words, Sunday is "1," Monday is "2," etc. By having access to the current WEEKDAY, I could somehow tell Excel to display the current DATE underneath that current weekday, in my schedule.

In order to use the WEEKDAY() function correctly, you have to "feed it" a date to work with. For example, if I typed this into the formula bar:

=WEEKDAY("04/07/04")

The corresponding cell will display: 4. This is because April 7, 2004 is (was) a Wednesday. Thanks to the WEEKDAY() function, we immediately know this.

But I’m not looking for the weekday number of some arbitrary date. I am looking for the weekday number of the CURRENT date. And the example above required me to manually type in the date, inside the parenthesis. I don’t want that. What I want is this:

=WEEKDAY(TODAY())

Since TODAY() contains the value for the current date, we can automatically grab the current WEEKDAY number.

Today is Wednesday, so we also get a 4.

Since this information is valuable, and would have to be re–used frequently, I decided to immediately store it in a variable, so I wouldn’t have to type it out each time I needed it. I went to Insert > Name > Define... I then entered the exact formula, =WEEKDAY(TODAY()), and gave it a user–friendly name. I called it weekday. So each time I wanted to reference the current weekday number, in my spreadsheet, I could just type "weekday," and in it’s place will appear the current weekday number.

Next problem

So I am able to figure out the current date, AND the current day of the week. Now... how could I display the current DATE, underneath the current DAY OF THE WEEK in my schedule? Remember, the weekdays of my schedule appear as such:

Since today is Wednesday, I would like to display the TODAY() function in the cell underneath "Wednesday." Except, each time I open this file, I want this to automatically happen. I don’t want to manually type in the date. How could I possibly do that?

Enter "IF" statements

In order to have the date dynamically display underneath the current weekday, I would have to invoke some testing. Inside each cell that sit underneath a weekday, I wrote out this statement:

=IF((weekday=2), TODAY(), (IF((2>weekday), (B4+1), IF((2<weekday), (D4-1), (D4+1)))))

First, we test to see if the value for weekday is equal to the value for that week day. So, for example, the example above would be in the cell underneath Monday, since Monday is the second day of the week. See that part that says, =IF((weekday=2)? This is testing to see if our weekday variable (see above) is equal to 2.

If this first test returns TRUE, and today is actually Monday, then Excel displays the result of the TODAY() function in that cell.

If it is false, and today is NOT Monday, then more tests are conducted.

I test to see if "Monday" is GREATER THAN the current weekday number. If it is, we simply add 1 from the cell to the left of it. If "Monday" is NOT greater than the current weekday number, then we do one final IF test.

We test to see if "Monday" is LESS THAN the current weekday number. If THIS is true, then we simply subtract 1 from the cell to the RIGHT of it.

It’s all about linear order. The goal is to supply the dates AROUND the current date.

The code example above will work, if it is put inside every cell underneath the weekdays:

And, of course, you have to adjust the numbers inside the formula to match the weekday that you are testing for.

It may be sloppy

Keep in mind, this project was my first attempt at a "calendar" in Excel. The formulas I created certainly need some "cleaning up." I did the best I could to quickly gather the results, but with more time, and more research, the formulas could become much easier to read and understand.

As with everything – there is not just one way to do it. I’m sure many people have tried doing something similar, with different formulas. This was just my way of doing for the time being.

Related

I explained "Naming cells and ranges" a while back.

Categories: Software , Tutorials

Add Feedback (view all)