Understanding A Calendar Excel Template

[kaleonard08]

Hi,

My question is more about trying to understand a template that Excel has. There is a community event planner template that I downloaded and I need to understand how the formulas work and what they mean. I would like to understand it enough to replicate it. If anyone could help me break it down to beginner level, that would be fantastic! I believe I attached the template too.

Thanks!

[6StringJazzer]

This is a fancy template and I would not consider it a beginner project. It's got a lot more going on besides just formulas; there are array formulas, controls, slicers, hyperlinks, filters, and graphics.

It would be better if you focus on two or three things in particular that you are interested in and ask about those, rather than taking an open-ended approach of "explain everything." I realized after opening this file that I could explain everything but it could take hours for me to break it down and write up explanations for how all of these features are being used, which is probably why you haven't had a response for a couple of days.

[kaleonard08]

Thank you for your suggestion.

I do understand how some of the file works like the hyperlinks and slicers. I am struggling with the formulas and what they mean.

Would you be able to explain how the calendar was made (Sheet2)? I tried researching it but I am lost when it comes to the if formulas. I wish I could find a video online to learn formulas in excel.

Thank you for your help. I really, really appreciate it.

[6StringJazzer]

Here are a few formulas:

CALENDAR!F3

Please Login or Register  to view this content.

The DATE function simply takes a year, month, and day, and returns an Excel date. In this case, ViewYear is a named formula; in this case it is a reference to a specific cell (sometimes this is called a named range). If you click on the address box, you will see a list of range names, and you can click on ViewYear to find that it is cell C2. This cell contains the year, which the user can change by using the spin button. MonthNum is also a named range, which refers to C3. C3 contains the month number, which is set by the spin button located in L2. So cell F3 contains the date representing the first day of the month for whatever month and year the user has selected.

CALENDAR!C6

Please Login or Register  to view this content.

This is a technique I haven't seen before.

First is StartDate. StartDate is a named range referring to cell F3. I showed the breakdown of F3 above; it's simply the first day of the month.

WEEKDAY returns a number that represents the day of the week for the given date. There is a parameter missing which can tell what numbering scheme to use. When that is missing, the default is to use 1=Sunday through 7=Saturday. So WEEKDAY(StartDate) is the number 1-7 of the day of the week of the first day of the month.

I do not know what {0;1;2;3;4;5} means in this context. When I isolate this is always returns 0, and {1,2,3,4,5,6,7} always returns 1. Normally this is a vector that is used when a vector is expected (e.g., the LOOKUP function). Here is doesn't make sense to me.

The reference to COLUMN returns the column number of the cell. If you look across the sheet, the formulas for each day of the week reference B1, C1, D1, etc., so it's just a clever way to use the numbers 2, 3, 4....

This whole formula is a way to number the days in the week containing the first day of the month. It will go back to numbers from the prior month if the first day of the month is not on Sunday. For example, if the month is June 2016, the formula for Sunday is parsed as

=6/1/2016 - (WEEKDAY(6/1/2016)-1) + 0 + 1 - 3 + 2
=6/1/2016 - (6-1) + 0 + 1 - 3 + 1
=6/1/2016 - 5 + 0 + 1 - 3 + 1
=6/1/2016 -6
=5/26/2016

which is the date for Sunday. This formula is necessary only for the first week, because we have to set dates relative to the first date of the month. The subsequent formulas in C11 going forward simply add 1 to the previous day.

The event formulas for each day (e.g., C7) are array formulas. An array formula is a way to tell Excel to repeat an operation on every cell within a given range. You indicate to Excel that a formula is an array formula by typing the formula then hitting CTRL+SHIFT+ENTER instead of just ENTER. When you do this you will see the formula enclosed in {braces} in the formula bar (you cannot type the braces in).

Please Login or Register  to view this content.

This is a bit complicated. Let's start inside out.

Please Login or Register  to view this content.

Dates is a named range for an array of the dates listed in the events table on the EVENTS sheet. For now let's just deal with the first date, then we'll go back and show how this works in an array formula. So if the date is equal to C6, which is the date for that day in the calendar, this IF will return the row number in EVENTS that has this event. Otherwise it will return FALSE; it doesn't explicitly show this but that's the default. FALSE will be treated as a 0 if it is used arithmetically. Now because this is an array formula, Excel will perform this on every element of the array Dates.

Then we build up to

Please Login or Register  to view this content.

SMALL takes two arguments; the first is an array of values and the second is an ordinal k telling that you want the kth smallest number in the array. Here the k value is ROW(1:1). I don't know why they are using ROW(1:1) because it is always exactly equal to 1. So the above finds the smallest value resulting from processing this function for the array Dates. That is, it will find the first row containing an event for this date.

Once we have the sequence number, that is, the position within the array where this event occurs, the rest of the formula is INDEX to go that row and retrieve the value that is four columns to the right, which is the event name. That is what is displayed in the date block.

The next formula underneath that uses ROW(2:2) so will find the row for the second event for this date; the next formula finds the row for the third event. The fourth formula in each block uses ROW(#REF!) which is an error so it will never find the fourth event. Not sure how that snuck in there.

That's all I got for now.