Let's jump in and look at a very simple project that really is nothing more than presentation. The project makes its own information out of thin air. Many people like to have a small calendar on their spreadsheet or report that always shows the current date. This project will make a live calendar that can be included in any spreadsheet in just seconds after you learn how.

This is an example of what the calendar can look like. It can be placed anywhere on a worksheet. While simple to construct, it does use some concepts you may not have heard of.

**SEVEN QUICK STEPS**** TO ****BUILD**** A ****LIVE CALENDAR**

- Define three named ranges using the Define Name dialog:
- DaysAndWks ={0,1,2,3,4,5,6} + {0;1;2;3;4;5} * 7
- DateOfFirst =Date(Year(Now()),Month(Now()),1)
- Calendar =DaysAndWks + DateOfFirst - Weekday(DateOfFirst,2)
- Select cells C3:I8. Press F2 on the keyboard and array enter the formula: =Calendar
- Center format those cells and apply the cumstom number format of "d".
- In C1 and C2 enter the formula: =C5
- Apply the custom number format of "ddd" and center text to C2. Apply the custom number format of "mmmm yyyy" to C1.
- Copy C2 to C2:I2. Apply "Center Across Selection" and a background color to C1:I1.
- Adjust the column widths to your preference and format a box around the calendar.

If that's all Greek to you, don't despair. Someone with the experience to understand those instructions could complete them in less than a minute! If you're in the Greek camp, keep reading...

Start a new workbook and right-click on the Sheet3 tab at the bottom of the window. Select Rename and change the name of the tab to Scratch. Select columns C, D, E, F, G, H, and "I" at the top of the sheet by clicking on C and before releasing the click, drag to the right until the mouse pointer is hovering over I. Now let go of the click and right-click anywhere in the shaded, selected area. Select Column Width... from the context menu and type the figure 4.29 into the box. Click on OK. To make this calendar we first need a grid of numbers ranging from 0 to 41. The first thing you need to learn is the Auto-Fill feature of Excel. Instead of typing 0,1,2,3,4,5,6 in the first row and 7,8,9,10,11,12 in the next and continuing that for a total of six rows, the Auto-Fill will make quick work of this task. Just enter 0 and 1 into cells C3 and D3, and enter 7 and 8 into cells C4 and D4.

Now just below the 8 and slightly to the right you'll find a tiny, black, square anchor. Click on this and drag to the right to include everything through the "I" column and let go of the click. Now click the anchor again and drag down to include all rows through 8 and let go of the click. It should look like this:

- There are many ways to do just about anything in Excel.
- Better methods are faster, shorter, easier to maintain, or simpler, and in that order.
- Excel can auto-fill cells by recognizing a pattern. This is done by clicking and dragging the anchor.
- Formulas should always start with an equal sign.
- Excel has dozens of built-in formulas.
- These can be combined to create countless custom formulas.
- CSE or array formulas are potent and are entered by Control-Shift-Enter.
- Arrays of constants need to be surrounded by fancy parentheses.
- Horizontal elements are comma separated; vertical elements are semicolon separated.
- The date system used by Excel makes date arithmetic easy.
- Formatting can dramatically change the way information looks.
- Conditional Formatting takes presentation to the next level.

- DaysAndWks ={0,1,2,3,4,5,6} + {0;1;2;3;4;5}*7
- DateOfFirst =Date(Year(Now()),Month(Now()),1)
- Calendar =DaysAndWks + DateOfFirst - WEEKDAY(DateOfFirst,2)

Hello. This is kind of an "unconventional" question , but have other visitors asked you how get the menu bar to look like you've got it? I also have a blog and am really looking to alter around the theme, however am scared to death to mess with it for fear of the search engines punishing me. I am very new to all of this ...so i am just not positive exactly how to try to to it all yet. I'll just keep working on it one day at a time Thanks for any help you can offer here.

@pop - I just selected the Professional Theme in MovableType 5 for the blog.

Nice approach to the calendar; you really pushed my understanding of array formulas with this one.

For a short month such as this one (February 2010), the resulting calendar looks a little odd. Since the month ends in week #5, the entire 6th week of the calendar is dimmed (because it's really March). Ideally, week #6 would disappear entirely in a situation like this.

Sounds like a job for a bit more conditional formatting...

A thoughtful opinion and ideas I will use on my blog. You've obviously spent some time on this. Congratulations!

You sate that a picture of the calendar is live and will be updated along with the original. This is not the case in Excel 2003.

@Terry -

Did you use the Camera Tool to make the picture?

No, Daniel, I used the Shift/Edit/Copy Picture feature. Just tried it with the Camera Tool (never used that before) and it works as it says on the tin (and as you said). Nice blog, by the way. Thanks

@Terry -

You are welcome.

Glad you were able to get it working. The Camera Tool can be used in many a creative situation...

Regards,

Daniel Ferry

excelhero.com

EHA student read and understood :-)

@Terry, you don't have to use the Camera tool to create a dynamic image. Use the Copy Picture feature, paste the picture, then select the picture, click in the formula bar and type the reference to the cells you want the picture to show, e.g.

=$A$1:$D$5

cheers,

I have been practicing this at least once a week for well over a month to help drill it in and understand it better - long before starting the EHA

Oli

I have been practicing this at least once a week for well over a month to help drill it in and understand it better - long before starting the EHA

Oli

EHA student

After skimming the article and viewing the video - I was able to follow the "Seven Step" program and create a minimually formatted calendar in 8 minutes. Another 10 minutes got the formating and the picture. I will have to start practicing like music43. Not possible pre-EHA.

3 things:

1. I like how you can resize the picture and Excel tries to keep the proper proportions.

2. This exercise re-emphasized the comma-semi-colon distinction (horizonital/vertical portions of an array).

3. You should add to you list of items learned (and you memtioned it in the multiplication tables module) - where the cursor is when the formula is created matters. Conditional formatting is another great example of that statement.

Thank you excelhero.com.

OK, I see what you are doing and it makes sense as shown, but I still don't think I'm 100%. That is, the light bulb is still pretty dim.

Maybe some additional examples might help?

EHA Student

For those using the non-english version of excel you might get some confusion as I did.

The "\" sign is used in the array constants formula to identify the Column while the ";" sign is used for the row identification

In Daniel's Name formula "DaysAndWks" you should type the following to make it work:

DaysAndWks={0\1\2\3\4\5\6}+{0;1;2;3;4;5}*7

Hope it helps

EHA Student

I have always struggled with understanding arrays. It is getting clearer now, but still some work to do. I finally have the "," vs ";" distinction straight now.

Todays short lesson was very good. I actually liked todays appproach (shorter videos and assignments). However, I am going to let our master what he thinks is best for all. In any case, I did well replicating the the calender. I still need to touch it up a bit format wise.

@ Michael Pierce

The conditional formatting necessary to remove the last week if the whole week is grey, would be to select the entire 6th week, and add the conditional formatting formula of =MONTH(NOW())>MONTH($C$8). Now choose white for the font color, choose a solid black line for the top boarder, and remove the bottom and side boarders. Make sure that this is the first rule in the set of rules. Unfortunately, conditional formatting does not allow for the thick black line used for the outside boarder of the original calendar, so a thin line would be one option. Alternatively you could add the thick black line to the first 5 weeks and add one more line of conditional formatting to the 5th week to change the bottom boarder to the thin grey line when a 5th week is required.

Even more fun can be had using ingeborg.hawighorst's suggestion of typing in the range for the picture link. Amazingly, this range can use Named Formula's, which means we can create a picture that will expand and contract based on the number of weeks required.

EHA student

EHA Homework.

I've played with this a few times over the last few months and will no doubt carry on until the concepts are cemented in my mind.

Excellent little tutorial.

EHA Homework

Another example of how to use array formulas and conditional formating to produce dynamic results with very little effort.

EHA Homework

Fantastic. I stumbled upon this a month or so ago. Makes a heck of a lot more sense now.

Also, never even knew about the camera function in excel until I just read this now. Holy useful!

EHA. I feel like this could be an exercise like multiplication tables - rehearse it every day or every week for a while until the primary concepts are second nature. It's nice that it the project itself is contained / limited (can be done in a few minutes), but involves a variety of useful skills. Daniel, if you can come up with more of these kinds of exercises, we could add them to our multiplication table repertoire!

Well I've built it using the instructions - with the 3 range names slightly amended - using TODAY() instead of NOW().

NOW() I have to sit and think how and why it works.

EHA student

I think I get it

I modified one of the names to make more sense to me. The DaysAndWeeks is a 7 x 6 matrix of sequential numbers that are added to the current date and you deduct the weekday number of the current date so that you can guarantee that the first of the month is always on the first row.

Pretty neat!

Calendar = DaysAndWeeks+DateOfFirst-WEEKDAY(DateOfFirst,2)

DateOfFirst = DATE(YEAR(TODAY()),MONTH(TODAY()),1)

DaysAndWeeks = {0,1,2,3,4,5,6}+{0;7;14;21;28;35}

EHA student

This is a great post - helps my understanding of array formulas after watching module 3 for EHA. The comma / semicolon distinction is one of those simple things that i wish i had known years ago.

I agree with @terry that remembering that where you are when you create a named formula matters. The 1st module of EHA makes this point much better, and its start to become a pervasive thought in my work as i go through EHA.

Im liking the 'all cell references are relative to where I am' so much that im starting to work in r1c1 notation - something i never ever thought i would do.

keep it coming, Daniel - you're pushing me hard but i love it.

Jesse Warburg

Hi Daniel,

I had read this at the same time as your sumproduct article.It is a very good example as what arrays are and how to use them.

Being so small and compact makes it easier to

analyse,play with it and understand the concept.

A few more of these practical examples would certainly help to digest arrays and array formulas.

Martin L (EHA student)

Read and understood. I walked through the steps. I didn't get why you set the title of the days/month to C5. So I set mine to C3. And found out why - C3 is not typically going to represent the current month.

I did a little experiment --- I was wondering what would happen with the conditional formatting if the first day of the month was a Sunday. If I replace DATE(YEAR(NOW()), MONTH(NOW()), 1) with DATE(2010,8,1) and adjust the conditional formatting similarly, it still works just fine --- the first Sunday shows up in the second row of dates. Which means that C3 can never be the current month. Very cool.

Janice (EHA)

Useful explanation and applicatio of arrays.

This was very helpful. I now need to practice the concepts daily.

Plis, help me. I'm noob. I select cells c3:i8 and press f2 do input ={0,1,2,3,4,5,6}. But when i press Ctrl+Shit+Enter, return the comand in all cells that i selected. Don't return 0 1 2 3 4 5 6 like example. I'm using excel 2003.

Hi, I followed this so carefully but my live date is a week behind the actual date. How can I fix this??

I work with a Dutch Language version of Excel 2010.

Therefore the seperators had to be adjusted for a bit.

When using the Name

DaysAndWks = {0\1\2\3\4\5\6} + {0;1;2;3;4;5} * 7

and replace the comma with the backslash, it works fine.

Regards

Gerard

Very good example of your use of named formulas and arrays and really helped me understand arrays. Daniel I was trying to figure out how you could have a blank cell under each day (by modifying your array formula to be wither non contiguous or incorporate blank cells into the array)for notes pertaining to that day. I could figure it out but then I just entered 90% transparent text boxes for each day and right aligned/top the cell to accomodate these boxes and this works better anyway - what a useful tool!

Hi Daniel!

Your writings here about live calendar 2013, is a kind of a very usful and practical informations for me. Clear serene way of explaination.

1.- Regarding to others peoples comments: I made excel 2013 understand the two kind of array formulas by changing Regional settings list separation to ","(commas).

2.- John H(18th of Feb 2012) is asking about how to use the benefits of an array formula feature(if I´m right) simulationally to extra empty row(and/or column) next to(in between) the resaults of an array formula. In other words: How to make array, multiply/calculate itself to every second row and/or column. Is there anyone who knows how?

3.- I´m designing a 12 mounth cal, with extra row and column for evry day, all the year/sheets in a workbook and need to connect the 12 mounths/12 sheets. How´d you design the continuation?

4.- Can someone tell me the logic/method or the code behind a dropdown-list(menu) which affects on all other 12 mounths/sheets in your workbook?

-------

My way so far to create extra empty cells for notes is to create a new space for every mounth which I make visual, including that extra space for notes. I just used equal-sign(=) for every cell in my/your cal and inserted extra column and rows after the calculation(the Dummy way). The image of my calendar, so far: https://www.dropbox.com/s/l1nme95t0ywrnm0/Dagatal.png