topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • March 21, 2019, 05:19 PM
  • Proudly celebrating 13 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Year Calendar on a Single Page - Excel-2007 Template  (Read 13670 times)

AEngineer

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 54
    • View Profile
    • James E. Mitchell - Drexel University
    • Read more about this member.
    • Donate to Member
Year Calendar on a Single Page - Excel-2007 Template
« on: August 30, 2008, 03:21 PM »
David Seah produced a very nice Excel template that allows one to print out a year's worth of calendar on a single piece of paper that will fold to fit with 3x5 cards.  It's great for planning because it's continuous, not starting a new line for each month.
http://davidseah.com...og/compact-calendar/
(Images of my version at the bottom of this post 9/1/2008)

I took that idea, with his blessing, and gussied it up to be more automatic and to allow the user to pick any start date.  It uses Excel-2007 conditional formatting so wouldn't work very well with Excel-2003.

Here's a link to my blog where I posted it.
http://aedesigndu.bl...-calendar-excel.html

There's no money involved, I just got a kick out of generalizing it.  I hope it's useful to others.

Jim Mitchell



9/1/2008 At the request of others I've added the following information and pictures of how the end product and the data entry portion of the sheet look - there's also a sheet of instructions.

What I did:
David Seah's version had to be manually updated whenever you changed the starting date of the calendar, picked a different starting day of the week, or decided to insert a holiday.

Month Labels:
If the end of the week is in a different month than the prior week then display the month for that line.
To get the banner effect I had to develop conditional formatting.  Since this was the first time I used it the "rules" that I used are a bit clunky, but they work.

Start of Year:

Make it a single named entry that is used in the first cell of the calendar.  All the other cells flow from that one (and did in David's).

Holidays:
Make a lookup based on the week of the year calculated as a simple  sequence starting on the first week of the calendar (the lookup is hidden in a grouped column).  This got a bit tricky because I had to write some logic to ensure that a holiday falling on a row that displayed the month name shows up in the next line.  To make this work I decided to sacrifice David's ISO calendar week numbering scheme.
It could produce problems if someone puts holidays in succeeding rows starting with a month name - otherwise it's fine.

Column Formatting

This is set to be formatted with conditional formatting using the "Weekday" function looking for either Saturday or Sunday.  This makes them move in the display depending on which day of the week you use to start the calendar.

Protection
I protected the sheets so that users couldn't inadvertently put entries in the wrong place.  There's no password so anybody who wants to modify the sheet can do so with no problems = opensource(?).

Hope this helps.
Jim Mitchell
« Last Edit: September 01, 2008, 05:37 AM by AEngineer »

cranioscopical

  • Friend of the Site
  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 4,701
    • View Profile
    • Donate to Member
Re: Year Calendar on a Single Page - Excel-2007 Template
« Reply #1 on: August 30, 2008, 05:51 PM »
Thanks for sharing, Jim

Darwin

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,984
    • View Profile
    • Donate to Member
Re: Year Calendar on a Single Page - Excel-2007 Template
« Reply #2 on: August 30, 2008, 09:06 PM »
Cool! Thanks for sharing this, Jim - this will come in very handy  :Thmbsup:

Deozaan

  • Charter Member
  • Joined in 2006
  • ***
  • Points: 1
  • Posts: 8,672
    • View Profile
    • Donate to Member
Re: Year Calendar on a Single Page - Excel-2007 Template
« Reply #3 on: August 31, 2008, 05:23 PM »
I'm too lazy to download the file and run Excel to find out how this works. :-[

Anyone want to put up a screenshot?


The first link (to the original) has a few screenshots. Looks pretty neat!

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 39,186
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: Year Calendar on a Single Page - Excel-2007 Template
« Reply #4 on: August 31, 2008, 06:35 PM »
Very nice Jim, thank you for sharing this. :up:

AEngineer

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 54
    • View Profile
    • James E. Mitchell - Drexel University
    • Read more about this member.
    • Donate to Member
Re: Year Calendar on a Single Page - Excel-2007 Template
« Reply #5 on: August 31, 2008, 07:29 PM »
Folks:

I'll write up a mini-review with pictures and explanation, but it seems like cheating to review your submission.  That's why I put it here.

Jim Mitchell
Jim Mitchell

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 39,186
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Read more about this member.
    • Donate to Member
Re: Year Calendar on a Single Page - Excel-2007 Template
« Reply #6 on: August 31, 2008, 07:46 PM »
go for it.. invent a new category "self-mini-review" :)

Darwin

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,984
    • View Profile
    • Donate to Member
Re: Year Calendar on a Single Page - Excel-2007 Template
« Reply #7 on: September 03, 2008, 02:03 PM »
Now featured on Lifehacker  :Thmbsup: Nice work Jim!

http://lifehacker.co...endar-excel-template