How to Create a Perpetual Yearly Calendar in Excel

a-foton » How to Create a Perpetual Yearly Calendar in Excel

a-foton » How to Create a Perpetual Yearly Calendar in Excel — Fri, 08 Aug 2008 09:37:39 GMT

PingBack from http://blog.a-foton.ru/2008/08/how-to-create-a-perpetual-yearly-calendar-in-excel/

re: How to Create a Perpetual Yearly Calendar in Excel

Sean — Fri, 08 Aug 2008 11:20:55 GMT

I found that the formula falls over when using a different date locale. Over here in the UK we use the "dd/mmm/yyyy" format and this means that all the Names listed are working on January, so that for April, DATEVALUE("4/1/"&TheYear) would equate to 4th Jan of TheYear.

I believe this can be solved by replacing:

DATEVALUE("4/1/"&TheYear)

with:

VALUE(""&DATE(TheYear,4,1))

so that:

=DATEVALUE("4/1/"&TheYear)-WEEKDAY(DATEVALUE("4/1/"&TheYear))+1

becomes:

=VALUE(""&DATE(TheYear,4,1))-WEEKDAY(VALUE(""&DATE(TheYear,4,1)))+1

I have included extra functions in the above as I originally evaluated this in a cell. In the Name definition ths formula could reduce to:

DATE(TheYear,4,1))-WEEKDAY(DATE(TheYear,4,1))+1

as the each cell formula should be able to work with the date as either a date or a numeric value.

I think this should always work as the DATE() function should be independant of date locale regarding the position of the arguments.

I haven't fully tested the above, so if there are any mistakes, my apologies!

Sean

re: How to Create a Perpetual Yearly Calendar in Excel

John Walkenbach — Fri, 08 Aug 2008 19:53:31 GMT

There are lots of ways to create a calendar. I prefer a method that uses a single multi-cell array formula to display a month. Named formulas are not required. I also created named styles so the user can easily change the look of the calendar elements.

I describe my perpetual calendar workbook here:

http://spreadsheetpage.com/index.php/blog/displaying_an_annual_calendar/

re: How to Create a Perpetual Yearly Calendar in Excel

Joan — Mon, 11 Aug 2008 18:02:45 GMT

I don't have Excel 2007... is there something comparable for Excel 2003?

Thanks!

re: How to Create a Perpetual Yearly Calendar in Excel

Joseph Chirilov — Wed, 13 Aug 2008 20:12:14 GMT

(I'm posting this on behalf of Mark Gillis who is having some trouble with his blog account)

Sean,

Thanks for pointing this out and providing a solution. I'll look at it and update the calendar to make it more "locale-agnostic".

John,

Yes there are different ways of doing a perpetual calendar and yours is a great solution. One of my goals was to showcase the new name manager feature and explain a mega-formula in a more digestible way to the uninitiated.

Joan,

No plans for a 2003 version. However, I recommend downloading a free trial of 2007 ( See

http://us20.trymicrosoftoffice.com/default.aspx?re_ms=oo&culture=en-US ) or buy it a great price ( See http://office.microsoft.com/en-us/FX102798291033.aspx ).

re: How to Create a Perpetual Yearly Calendar in Excel

Steven — Thu, 14 Aug 2008 20:35:17 GMT

I have downloaded your perpetual calendar and it works great. I have one question, I am guessing the JanSun1 function is a product of Excel 2007, but I am using 2003 and it is still working, is there a special add-in or something like that which allows you to use that function in 2003?

Thanks

SVANO

re: How to Create a Perpetual Yearly Calendar in Excel

Markgi — Sat, 16 Aug 2008 03:40:37 GMT

SVANO,

JanSun1 is a named formula. You can use these when running this calendar in Excel 2003, after having converted the 2007 format to 97-2003 format. There's a slight loss of fidelity, bit the named formulas still work.

Mark

re: How to Create a Perpetual Yearly Calendar in Excel

Brzak — Tue, 26 Aug 2008 16:04:32 GMT

For anyone else who may be trying to use this in a region where date format is dd/mm/yyy (as opposed to mm/dd/yyyy) - i got this calendar to work for me by editing the formaule of the named ranges [mmm]Sun1 (i.e. JanSun1 - DecJan1) so that the hard-coded part of the date is in your local format.

e.g. taking DecSun1 as an example:

in the formula: =DATEVALUE("12/1/"&TheYear)-WEEKDAY(DATEVALUE("12/1/"&TheYear))+1

change all instancves of "12/1/" to "1/12/".

One question for you Joseph - how did you get the cloud fill effect? (I know how to do it for a chart, but it seems as though a different method was used here?)

Cheers

brz

re: How to Create a Perpetual Yearly Calendar in Excel

Markgi — Wed, 27 Aug 2008 18:56:35 GMT

Brzak,

I used a background image on the worksheet:

Page Layout | Page Setup | Background | Sheet Background dialog box

The picture is standard on Vista in the Pictures folder.

Mark