Tag: Calendars

Posted on

A Spreadsheet is Creative (Part 5)

While the content of the spreadsheet is complete, the design isn’t finished yet. I think the best spreadsheets are “scannable”, in other words the reader can scan the sheet and get a sense of the pattern of information, before they even read it. I want the reader to be able to see at a glance whether it is a Facebook or Twitter Post, and to have a sense of the time of day the post will occur. Colour is going to make this happen. Since I have no intention of manually formatting the calendar, Conditional Formatting will make this happen.

A weekly calendar view of the same information.

I am a big fan of conditional formatting, but the working dialog where formulas are added leaves a lot to be desired. So when I base a conditional format on a formula, I will typically build the formula in the spreadsheet so it can be tested and then copy it out of the formula bar and paste it into the working dialog. In this spreadsheet I have 12 (TWELVE) conditional formats, so I ended up pasting the formulas into notepad, so that I could jump back and forth without having to close/reopen the dialog box repeatedly.
I thought quite a bit about how I wanted different times to be represented by colour. I eventually decided on 6 different time “zones”.

  • 10 pm to 7 am
  • 7 am to 9 am
  • 9 am to 12 pm
  • 12 pm to 3 pm
  • 3 pm to 6 pm
  • 6 pm to 10 pm

I needed to find the excel versions of those, so I went to an empty spreadsheet, typed in the time and removed the time format. This is what I found:

Excel time format, converted to the underlying number value

Here are the series of formulas I used for conditional formatting, the first in each group is the default which will format the 10 pm to 7 am time entries.

=(C5=”FB”)
=AND(C5=”FB”,B5>=0.292,B5<0.375)
=AND(C5=”FB”,B5>=0.375,B5<0.5)
=AND(C5=”FB”,B5>=0.5,B5<0.625)
=AND(C5=”FB”,B5>=0.625,B5<0.75)
=AND(C5=”FB”,B5>=0.75,B5<0.917)

=(C5=”TW”)
=AND(C5=”TW”,B5>=0.292,B5<0.375)
=AND(C5=”TW”,B5>=0.375,B5<0.5)
=AND(C5=”TW”,B5>=0.5,B5<0.625)
=AND(C5=”TW”,B5>=0.625,B5<0.75)
=AND(C5=”TW”,B5>=0.75,B5<0.917)

I chose two different colours, based on the http://colorbrewer2.org/ color blind safe palettes and then picked increasingly darker shades of each colour.
And that wraps up this spreadsheet build! I hope you found this look at my process interesting, and I hope that you find building your spreadsheets as creatively satisfying as I do.

I offer Excel template design services and training. Feel free to send me an email – catharine@mytechgenie.ca
Headline image by Kelly Sikkema

Posted on

A Spreadsheet is Creative (Part 4)

After the first test of the mega formula that will create content of our calendar, I spotted a problem:

Oops, it just repeats the first matching record

Some formula tweaking is in order. I’m also going to trim the length of the Post Type entry to 2 characters, so only FB or TW will appear, And the Image (column E) will be trimmed to the last 3 characters, so only the file type will be visible.
I will use the current row number ROW() – 5 (the number of rows before the entries start) to help count down the entry list. I’ve also replaced the “stop” label with “” with empty quotes. Now when there is no matching entry, nothing will appear.

Adjusting the rows captured using the ROW function

As I mentioned earlier, I’m trimming the Post Type entry to 2 characters using LEFT. I also decided to force those two characters to uppercase using UPPER. So the previous formula will be nested inside the LEFT and UPPER functions:

=UPPER(LEFT(IF(ROW()<5+(COUNTIFS(Content!$E:$E,”>=”&B$4,Content!$E:$E,”<“&B$4+1)),INDEX(Content!$C:$C,(MATCH(B$4,Content!$E:$E,0)+ROW()-5),1),””),2))

While it has been a long haul building this spreadsheet, now is not the time to stop. The next step will be adding the conditional formatting to this spreadsheet.

I offer Excel template design services and training. Feel free to send me an email – catharine@mytechgenie.ca

 

headline image via Kelly Sikkema

Posted on

A Spreadsheet is Creative (Part 3)

Now, I need to start to pull the post type, time of post and post content from the Content sheet. Starting with the post type (which resides in Column C), the following INDEX function in cell B6 will do the trick:
=INDEX(Content!$C$1:$C$1187,B5,1)
This index formula uses the value from B5 to find the entry in row 530. And yes, at this point that is only the first entry for the day.

Index and If function results

Now, I’ll use an IF statement to build the formulas you see in column C6:
=IF(ROW()<6+C$5,”yes”,”stop”)
At this stage in the process I usually use the IF statement to display a label. “Yes”, means there is matching data in the source sheet. “Stop” means there is no longer matching data. The test in column F shows the formula is working.
Now I’ve proven that the each formula works in turn, I can combine them into a larger “mega” formula. Don’t forget that when building these larger formulas you can; expand the formula bar and use Alt+Enter to force the formula to wrap for easier reading.
Like this:
=IF(ROW()<5+(COUNTIFS(Content!$E:$E,”>=”&B$4,Content!$E:$E,”<“&B$4+1)),
INDEX(Content!$C:$C,(MATCH(B$4,Content!$E:$E,0)),1),”stop”)
You’ll notice that I’ve also stopped referring to a specific range in column E/C and instead reference the entire column. It doesn’t make any difference to the result of this formula and makes it a bit simpler to read. Also the Row adjustment number changes from 6 to 5 as I play with the layout of the spreadsheet. Changing the layout includes reordering the data, so that the time of the post comes first and adding a column that shows the path to any images used in the post.

Here’s a shot of the results of that formula:

The formula is expanded to more columns Now I’m capturing more columns of data

You’ve undoubtedly spotted that only the first entry is being captured and repeated. I’ll adjust the formula to correct that problem in my next post.

I offer Excel template design services and training. Feel free to send me an email.

Headline Image from William Iven

Posted on

A Spreadsheet is Creative (Part 2)

Continuing from my previous post, showing my process in designing a spreadsheet.

Its’ starting to look a little bit like a calendar

Here I’ve placed each Day/Date heading over 3 columns (later I figured out I needed 4). Its’ starting to look a little bit like a calendar.

One of the things making the design of this calendar a little more challenging is that each day has a different number of posts. Anywhere between 2-20 depending on the days activities (library author quizzes, anyone?). So my formula needs to account for a variable number of entries each day, I can’t simply copy 20 lines and call it done. So, how will I do that?

Finding where each day starts and the number of entries on that date

I’ve put the following formula into B5, it will find the row that each days’ entries begin on:
=MATCH(B$4,Content!$E1:$E1187,0)
Here the MATCH function does the trick of looking on the Content sheet (where my database lives) and counting down the rows to find the date that matches, then it returns the value of the rows counted.
In C5 I’m using the following formula to count the number of entries on that date:
=COUNTIFS(Content!$E$1:$E$1187,”>=”&B$4,Content!$E$1:$E$1187,”<“&B$4+1)
The reasoning behind using COUNTIFS this way is because when dates are entered in the content sheet, sometimes a time is entered as well. The time remains unseen because of the formatting applied to the date column. I need to ensure I get every entry regardless of whether a time is present or not. Using COUNTIFS to count in a range will do this.

I’ll continue with the design process in my next post.

I offer Excel template design services and training. Feel free to send me an email.