Creating a calendar

[MTC2016]

Hi all,

I have a yearly planning schedule that I am trying to adapt so when you select the year, it automatically changes the dates of that year to the correct days. I'm sure this may be super simple to most but I have been playing with ideas for hours and getting nowhere fast!

The format I need is pictured below. The form runs the entire year and I would like to be able to quickly change the days/dates by simply selecting the year (year selector needs to be added)

2016-03-28.png

If anyone could provide some assistance with this I will be eternally grateful

[Ochimus]

Col O is range of years.

Set the year you want from the DropList in B1

A3 is the first day of the year ("=B1")

A4 - A370 then list the rest of the days as follows:

=IFERROR(IF(A3+1>DATE(YEAR(A3),12,31),"",A3+1),"")

(Cell remains blank if it is later than the last day of the year, or the row above is blank. Otherwise it adds one day to the date in the row above)

Cells are then Conditionally Formatted as follows:

=WEEKDAY($A5)>5

(f the day is more than the fifth day in the week, fill in Cols A - G)

So as you change the year in B1, the "filled rows" will change with it.

Hope this helps

Ochimus

[BadlySpelledBuoy]

Well I was beaten to it, but no point in wasting effort, so attached is my version.
A similar approach, just laid out similar to your example.

BSB

[MTC2016]

WOW! as easy as that! Many thanks, both options work equally well.

However it has now generated another issue. I can have multiple things going on in one day which I need to account for. This can be upto 5 things per day. Is it possible to update the form in the same format but having 5 open rows per day?

Really appreciate your assistance with this!

[BadlySpelledBuoy]

Is this what you had in mind?

BSB

[MTC2016]

Is there a way of merging the date cells so it only shows one large cell for the date ?

Being picky now aren't I

[Pete_UK]

You can do it by applying two conditional formatting rules, as in the attached file.

Hope this helps.

Pete

[BadlySpelledBuoy]

Or another approach attached.

I like Pete's better though.

BSB

[MTC2016]

Seriously appreciate this guys! exactly what I am looking for!

Now I just need to figure out how to link it to my outlook calendar, and link it to various other documents!

but that will be another day I think!

Thanks again

[Pete_UK]

Glad to hear that you got what you wanted.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[BadlySpelledBuoy]

Happy to help

Don't forget to mark the thread as SOLVED if that takes care of your original question(s).

Good luck with the Outlook linking!

BSB