Traducir el blog

How to make an Excel calendar

Posted on mayo 14, 2010 by Excel Pedro Wave

To make it easy, I have prepared a video showing how to make a calendar in Excel. I used the latest version of Microsoft Office Excel 2010 Beta because it is free until October and is the one I have installed on my computer, but you can do it with any previous version.

It should be noted that this calendar is not as easy as those found in many pages of Excel tips because I used the logic of the date functions to represent the days optimally and easily reproducible once generated template of a particular month. It's about doing the calendar of any month, with the functions provided in Excel, changing the year and month's number to build the desired Gregorian month of any year.

IMPORTANT: The most enhanced feature that I have considered doing this calendar is that each day of the month is represented as an internal date number in Excel format, allowing you to play with the days of many possible ways, to compare them with other dates and visualized as month days (1, 2, .. 28, 29, 30, 31), days of the week (Monday, Tuesday, ..), month (January, February, ..) appearing in the language of the local configuration of the operating system of your computer.

NOTE: The representation of dates in Excel goes from number 1 by January 1, 1900, to number 2,958,465 by 31 December 9999 (Try entering 9999 as the year and 12 as the month to see what happens with the following months)

One of the improvements in 2007 and 2010 versions of Excel are the characteristics of conditional formatting, selecting the colors of the calendar, as seen in the last minutes of the video I prepared:


Except conditional formatting, the rest of the video can be followed with other computer programs such as OpenOffice Calc, that is free.
The formulas are written in English, which should not be an impediment to interpret or transform them to your language. I recommend you download the spreadsheet and open the calendar with the Office 2007 or 2010 program to see the formulas in your language. Download it with the link to the left.

If you open the calendar with Excel 2003 or earlier or OpenOffice Calc, you will not see in color because these versions do not support conditional formatting used, but is easy to add the colors you want easily.

In OpenOffice appears the 504 error in the calculation of the numbers of weeks. I leave for you to change given that the function used WEEKNUM_ADD(Date; ReturnType) designed to calculate exactly like Microsoft Excel, and not as estimated at ISO 8601, for which the function uses WEEKNUM(Number; Mode).

ATTENTION: Write the value of Mode and ReturnType to 1 (default value in Excel) or 2, depending on your calendar week starts on Sunday or Monday, respectively.

The following table shows the Excel date functions used to make the calendar:

EnglishSpanishDescription
DATE()FECHA()Calc the internal date value.
EDATE()FECHA.MES()Calc the internal date value before or after some months.
MONTH()MES()Month number of a date.
WEEKNUM()NUM.DE.SEMANA()Week number of a date.
WEEKDAY()DIASEM()Returns the day of the week as an integer (1-7).
EOMONTH()FIN.MES()Returns the last day of the month.

Don't have or want to install Excel or OpenOffice on your computer?
Well, no problem. If your PC has no memory, disk or power, you can practice for free with spreadsheets.

Where I can see and edit the calendar without download it to my PC?
The answer is in the clouds.

What are you talking about clouds?
About the Google Docs spreadsheets like this:



Click on the link below to view full screen:
How to make an Excel calendar

With what we already do not have to leave this blog to see the formulas and functions of this calendar, but suffers from the same errors mentioned for OpenOffice Calc, you can overcome if you want.

Why not create a copy of the calendar now?
Click on the menu: Archive y Create a copy...

Now you can customize your own calendar in the cloud and share it with everyone!

This has been an advance of the proposed Perpetual Calendar that you can read in future articles. If you like this, let me know posting a comment.
Traducción al español aquí.

No Response to "How to make an Excel calendar"

Leave A Reply

Dime si te gusta lo que lees y, si no te gusta, dime por qué. Tengo habilitada la moderación de comentarios. Tu comentario se publicará pronto.

Mi lista de blogs