• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Excel Data to Calendar

M

mayhem

New Member
Dear Chandoo Gurus

I have Excel 2007 and am trying to creating a calendar from data. As I will need to modify the data on a regular basis I need to create a calendar that amends automatically with each update to data.

Please see attached and advise what is the most efficient way to create one with conditional formatting based on my legends.

Thank you
Mayhem :)
 

Attachments

  • Sydney 2015 Spring Sample Calendar.xlsx
    8.9 KB · Views: 14
  • Calendar_with_Events.png
    Calendar_with_Events.png
    35.9 KB · Views: 10
Hi Hui & Narayan

Thank you for your advice. The link from Narayan was sadly Greek to me and the link from Hui was fabulous but once I'd downloaded the calendar and populated with my dates, I still didn't know how to automate the conditional formatting for my events which fall under 4 different colour coded legends.

I attempted to manipulate them but stopped as it's time consuming and my original objective was to have it automatically update as I will need to amend the events and categories within this calendar going forward on a regular basis.

Does anyone know how I can achieve this please? Please see attached, I welcome any advice but please bear in mind that my skill set is basic whilst my ambition may be intermediate/advance :)
 

Attachments

  • LATEST Sydney 2015 Spring Events Calendar.xls
    98.5 KB · Views: 10
Hi ,

I am not able to understand what you want to do.

Secondly , I cannot cater to urgent requests , sorry.

If you can wait for a solution , and if you can explain your requirement in a lot of detail , I can certainly help.

Narayan
 
Hi Narayan

I appreciate you provide advice, I hope I didn't sound demanding :) Apologies, my articulation was poor at best.

- I have 2 tabs: Calendar and Setup.
- Under Setup I have populated dates and legend that I need reflected in Calendar tab (Anything else in Setup tab, I don't understand as I copied this calendar from Hui's suggested link)
- As example, an Aussie Public Holiday as per Setup tab legend, should appear in Calendar tab with the same Fill colour fomatting for that date.

I hope this is clearer and look forward to your advice when you get a chance

Thank you,
Mayhem
 
Hi ,

There seem to be 2 aspects to your calendar :

1. The populating of the dates with the event descriptions that you have entered in the Setup tab ; is this being done automatically at present ?

2. The coloring of the individual cells in the calendar based on the colors which have been used for events that fall on particular dates in the Setup tab.

As such , unless we use older Excel 4 macro functions or VBA , it is not possible to recognize a cell color ; if the cell has been conditionally formatted , it becomes more difficult.

So , on the Setup tab , how are you going to color the event cells ? Is it manually , or is it through conditional formatting rules ?

Is the calendar going to have the remaining months of the year , or is it just the months that you have shown ?

Narayan
 
Thanks for the quick response.

1. The date population in Setup is manual
2. The calendar I copied, used conditional formatting. If manual is easier, I could use that instead unless there's another way to distinguish legends i.e. coloured text instead of cells
3. I'd like the calendar to display remaining months, though I won't need them at this stage as my event calendar is to be submitted every 4 months.

Thank you,
M
 
Hi ,

I have not made myself clear.

1. You have populated the entire Setup tab yourself manually. This is clear.

2. You have got the Calendar tab with its layout and its named ranges from Daniel Ferry's site. This is also clear.

3. As far as I can see , the event descriptions which appear against any date on the calendar are being taken from what has been entered against that date in the Setup tab.

4. The calendar does not seem to have provisions for multiple entries against one date , though I may be wrong on this ; I tested by making two entries on the Setup tab against the same date ; they did not appear against that date on the Calendar.

5. At present , there are the desired colors for the populated cells in the calendar ; have you colored these cells manually ? Is this what you want to automate ?

If so , I suggest that you have a table of the 4 colors , and give them 4 numbers or 4 letters of the alphabet ; e.g. yellow can be Y , magenta can be M , blue can be B , cyan can be C , green can be G , and so on.

Then , against each entry in the Setup tab , in the column next to the entry , just enter the associated letter. In conditionally formatting the cells in the calendar , we can look at this letter to color the corresponding entry.

Narayan
 
4. That's right, thank you for checking, I can live with that
5. I did manually start to colour some of the Calendar but stopped when I realised the scale of the task at hand. Yes I'd like to automate the Calendar tab based on the colours in Setup.

I've done as you suggested, giving each colour a letter, as per attached
 

Attachments

  • 171214 Sydney 2015 Spring Events Calendar.xls
    99 KB · Views: 7
Hi ,

Great. Can you confirm whether you want weekends to be colored a separate color ? Suppose a date in the Setup tab falls on a weekend , should it be colored according to the setup table legend or should it be colored as a weekend day ?

Either way , this is going to take some time , so please check back later.

Narayan
 
Hi ,

I suggest that you use a different calendar , if that is possible ; this calendar may be a tour-de-force as Daniel says , but he also says it is maintainable ; it is laughable to say this calendar is maintainable.

Everything on the calendar is done using named ranges , with the result , if you have to understand just one aspect of the calendar , you have to understand everything. The dates in each calendar cell are not dates , but named ranges , with a formula such as this :

=CHOOSE(date_types_selected,date_formatted,date_ordinal,date_ordinal_custom,date_days_to_end_of_custom_year,date_days_to_end_of_year,date_julian,"")

I challenge anyone to understand this formula and explain how it can be modified , without probably spending at least a couple of hours ; if this is what is called maintainability , Daniel is welcome to call it so.

If you are willing to wait , I am willing to spend the time and effort that will be required to understand this calendar enough to modify it.

Narayan
 
I'd be overwhelmed with gratitude and would love to learn from you how it could be achieved. Shukria, Gracias, Merci, Arigatou :)

Mayhem
 
Hi ,

See if this is OK. And please don't ask me to do it for the remaining months !

Narayan
 

Attachments

  • 171214 Sydney 2015 Spring Events Calendar.xlsx
    35.3 KB · Views: 25
But you LOVED helping me out! ;)

Thank you so much for your help with this, it is much appreciated. I can do the remaining months myself. Have a beautiful week ahead and Happy Holidays

Mayhem
 
You must log in or register to reply here.
Back
Top