Traducir el blog

Skeleton of the Perpetual Calendar

Posted on mayo 24, 2010 by Excel Pedro Wave

Continuing the expansion of the waves in this blog, the skeleton will determine the place of user interface elements in a computer application and will be designed for optimal, effective and efficient use for the desired objective, that is not to extend this project until becoming skeletons.

The skeleton design should cover three different designs:
- Interface Design and its components that allow users to do things, such as buttons, input fields, etc.
- Navigation Design through the application information and the various worksheets in the calendar.
- Information Design to be complete, easily accessible and user friendly.

This is the outline of design for the Perpetual Calendar project, bearing in mind that the best design is one that is based on these three design aspects. Also you must use the same conceptual model in the entire application to be consistent and have a easy learning curve for users.

Skeleton Design of the Perpetual Calendar

  • Interface Design

1) To enter the year, the annual calendar has a drop-down list from 1900 to 9999, making it easy to change from year to see another calendar.

In a hidden worksheet called Years,
will create a drop-down list of 8100 rows with all years and origin
=Years!$A$1:$A$8100
as seen in this screenshot.

Of course you can type directly into the cell the desired year.


2) In the Holidays worsheet you can select the country with their celebration dates by a drop-down list of countries.


The list can be edited on the same Holidays worksheet, in rows 58-87 of the first column as the origin:
=$A$58:$A$87


3) To move around the 17 calendar sheets have been chosen the following colors: light blue for the annual calendar, dark blue for the day, red for the holidays, green for the events, yellow for the 12 months sheets and blue for the mini-calendar.


To go to a worksheet you click on one of the tabs above, you can edit to change the name of the months without affecting the calculations. The following is an example of what not to do:

2010 Calendar – Excel Template [Downloads] by Chandoo.org

Because the individual months are calculated from the month name written on the tab, making it impossible to change that label to translate, eg "Jan" to "January" or to Spanish "Enero".


4) Any time you can change the calendar so the week starts on Sunday or Monday, and you can even show twice on Sunday (left and right) and hide on Saturdays and/or Sundays to show the working week, from Monday to Friday.


As shown in the image on the left, check it with a mark value to 1 on Saturdays or Sundays as visible.

Checking with a value 0 is marked as invisible.

This is achieved through a custom cell format type:
[=0]"r";[=1]"a"
representing a value of 0 as the letter "r" and a value of 1 as the letter "a", being the Webdings font which display checked and unchecked marks.

Another way to create cells to select or deselect actions can be viewed at:

How to get tick marks in Excel? [custom cell formatting] by Chandoo.org

Editing a conditional formatting rule, which uses a formula to determine the cells to format, you can hide or show on Saturdays and Sundays.

The custom cell format type must be made with 4 semicolons ;;;; (when the formula is true is made invisible, when it is false are visible)

5) In the three worksheets of Holidays, Daily and Events has been used the same technique to select or deselect the dates you want to make visible or invisible.


  • Navigation Design

1) The navigation between worksheets are done with hyperlinks in Excel:

From the sheet with the calendar year may go to other sheets, simply clicking on the hyperlink marked in blue and underlined as usual. For example, to go to the month of January, click on the name of that month.

From spreadsheets of the Holidays, Diary and Events can go back to the yearly calendar by clicking on the year number.

In the 12 sheets of each month, if you click on the name of the month, go to the mini-calendar and, if you click on the year, go to the yearly calendar.

In the mini-calendar, if you click on the year number, go to the yearly calendar, if you click on one of the month name, you are going to this month sheet.

  • Design Information

1) In all calendars shows year, month, weekdays, Saturdays and Sundays, today in yellow, holidays in red, events in green and calendar diary in blue, as you can see on this prototype of a mini-calendar:


In this and other calendars you can hide both Saturdays and Sundays, as explained above.

As planned, in the next issue will surface (such as oil spill) the outcome of the project...
Traducción al español aquí.

No Response to "Skeleton of the Perpetual 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