• 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.

Dynamic whole year Calendar

K

Krysta

New Member
Hey there Chandoo!

I recently downloaded your 2013 calendar template. Very helpful, Thank you. I would have a few question if you can assist me.

1) I was wondering instead of a regular calendar format (7 columns x 6 rows), can we have a calendar showing all the 31 days of a month (or whole year) in one row?

2) I want to know how (what's the formula) you used to have the calendar changed dynamically when I changed the year..?

Thanks in advance for your help,

Krysta
 
Hi Krysta,
Is this the calendar you are referring to?
http://chandoo.org/wp/2012/12/26/download-free-2013-calendar/

If so, the "Mini" worksheet should have the format that you are looking for, at least by month. I'd discourage having a whole year in one row...that's a lot of columns, and would be non-compliant for users with XL 2003. As for how it works, if you go to the "Daily" sheet, cell C5 (valYearStart) uses a formula to determine the first day of the year, and then all the other cells below that just count by adding 1.

After that, various VLOOKUP and INDEX functions put the dates in the correct spot.
 
Hi there Luke!

First, Thank you very much for your reply!
Yes, the mini calendar is the format that I was looking for. If you dont recommend the whole year, how about just for 2-3 months?
So basically, I'm looking to accomplish a reusable minicalendar with 2-3 months view
So when I change the "2013" to 2014, the May-June dates dynamically change to May-June 2014 dates

2013 May June
T F S S M T W T F S S M T W T F S
Luke 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Mary 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Candy 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Sushi 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Alex 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Can you show me how I can accomplish that?

Thanks in dvance!
 
From CHandoo's example, you should be able to see different ways to manipulate dates into any layout you want. Here's an example that I whipped up to show you a simple format as you showed. Hopefully it helps.
 

Attachments

Brilliant!!! Exactly that!!! OK. I'l try to understand the formulas and stuff! THANK YOU very much for your fast assistance!
Krysta
 
Hello there,

I have another question. I want to have my weekend highlighted dynamically in the calendar. Do I work with the function "Workday"?

Thank you!!!
 
Not quite, you'll want to use the WEEKDAY function. Assuming you define the weekend as Saturday and Sunday, you could do:
=WEEKDAY(MyDate,2)>=6
This will evaluate to TRUE if the date is a weekend.
 
Hi Luke,

As always, you are impressive with quick replies!
How do you insert that formula based on the calendar you presented earlier?
I highlighted the dates in sheet 1 (1 2 3 4 5 6 7 ....) and created a new rule in the Conditional Formatting. Didn't work.
I tried the column F in the Inputs tab and also created a new rule in the Conditional Formatting. Didn't work.
I dont receive any error.. Just It doesn't seem to do anything...
What did I do wrong?
Thanks in advance and Have a nice weekend!! =)

Krysta
 
Hi, Krysta!
As Luke isn't yet here and I assume that until Monday he won't, I tried to fix it. Give a look at the uploaded file.
I have to do 2 things:
a) Apply the CF formula to the range C5:IU9 as follows:
=DIASEM(C$5)>=6 -----> in english: =WEEKDAY(C$5)>=6
b) Change your row 5 formulas from:
=TEXTO(INDICE(yDates;COLUMNA(A$1));"ddd") -----> in english: =TEXT(INDEX(yDates,COLUMN(A$1)),"ddd")
since Luke M formula for CF work on cells that contain a date value and not a string as yours.
Hope it helps.
Regards!
 

Attachments

Hi, Krysta!
Luke M is right, my mistake, use his CF formula.
Regards!

@Luke M
Hi, buddy!
Thanks for the catch, but it wouldn't have been necessary if I shouldn't have been forced to continue with what you started... nothing related to have it been a Friday afternoon, had it? ... :P
Regards!
 
Hey guys,

Sorry, I was away sick last week. Now I'm back on Excel.
I tried your formulas... It doesn't seem to do anything for me.. Maybe I'm not a good student and didn't do exactly what you mentors instructed?
Here is my calendar. You can see the CF I inputted.
Also, is there a way to populate the month dynamically? In my calendar, in row 5, I have May as a start Month. But then, the following month, it's still May... I would like it to update to June, etc?

You guys are very hopeful. You dont know how much I learned from you guys froma short amount of time!!!

Thanks!!!

Krysta
 

Attachments

Hey guys!
I got it! =)
I tried with the =INDEX(yDates,COLUMN(A$1)) ... To compensate the part that removed ("TEXT"), I went in the cell properties to set the cells to show the days... It worked!!! Youpi! I'm so happy.. I feel like accomplishing something big! =) But I can't do that without your help though!!
Now, I'm still working on how to dynamically change the month...

Krysta
 
Hello Krysta,
To increment the months, try
=EOMONTH(A1,0)+1

where A1 has the date you want to increment by a month.

You can then format the date as you wish.

Cheers,
Sajan
 
Thanks Sajan!
I tried that but maybe I did it wrong?...
I have attached again my calendar.
It changed for the first month after the start date (AG5:BH5 cell).. After that it doesn't work. I didn't select the right Start date maybe?
Also, If I changed the Start Date (B2) cell, my first month (B5:AF5) will change.. but not the month that I added the formula (AG5:BH5 cell) doesn't change...
Help? :)
I'm looking at the http://chandoo.org/wp/2010/04/06/rolling-months/ technique.

Thanks,

Krysta
 

Attachments

Hi Krysta,
The initial date value in cell B2 was a text string. To display dates, we will need to convert it to a date first, and apply formatting to show just the month name. Then the EOMONTH formula would work.

See attached doc.

Cheers,
Sajan.
 

Attachments

Hello Sajan,

Your solution it's working. Thanks alot. But I noticed that my rules in the CF don't apply correctly anymore. In your previous document attached, the CF didn't apply. On my document, the CF did work but not to the right day.. Before I added the EOMONTH formula, I got the hifglight on the good day which is Saturdays and Sundays. Now they highlight Fridays and Saturday
Do you have an idea why?

Thanks,
 

Attachments

Hi Krysta,
Instead of repairing the current approach, I would suggest using a slightly different approach for handling dates, with actual date values. i.e. instead of using 1,2,3, etc., I would suggest using actual date values that are formatted to display just the day value, etc. That should simplify the formatting of cells, since we would be able to leverage Excel's date functionality.

I will try to create a view later today. (It is early AM here now.)

Cheers,
Sajan.
 
Hi Krysta,
Attached is a simple linear calendar representation that leverages Excel's date functionality. Play around with it and let us know what works, what you would like changed, etc.


Cheers,
Sajan.
 

Attachments

Hey Sajan,

I played with your version. It blended in very well with the document!
THANK YOU very much the whole team for the wonderful help. This site is surely the most useful site that I ever visited!

Thank you!

Krysta
 
Hi Krysta,
Thanks for the feedback! Glad to help! Come back any time!

Your Chandoo.org team!
-Luke, SirJB7 and Sajan
 
hey guys,

I want to add another challenge to my calendar. :)
What if I want my Excel calendar to show the fiscal year (i.e April 1, 2014 - March 31, 2015) instead of the calendar full year (January 1, 2014 - December 31, 2014)?
Right now, If i slect April as the Start Date, Year: 2014 and End Date: March, the document doesn't understand it...
Any idea how I can do that? to play in the Inputs tab?

Thanks!

Krysta
 
Hi Krysta,
The format I suggested (where Excel's date logic is leveraged) would allow you to copy the last column to the right to create additional columns. By copying in that manner would extend the timeline for the calendar.

(The date for the first calendar column is based on the start month you selected. i.e. it ignores the end-month you specified.)

Cheers,
Sajan.
 
You must log in or register to reply here.
Back
Top