Difference between revisions of "OOoES/Traduccion/Calc: Funciones Fecha Hora"

Revision as of 16:19, 28 April 2008

Lista de las funciones Fecha & Hora de Calc

Información General Fecha y Hora

In Calc, dates and times are represented by numbers. For example the number 39441 may represent the date 25Dec07. We may enter 39441 in a cell, and then (selecting Format - Cells...) choose a date format to display the number as a date.

Although a number representing a date / time is the same as any other number (except that we choose to display it as a date or time) it may be helpful to use the term 'date-time serial number'. The date-time serial number is simply the number of days that have passed since a set starting date. The default (normal) starting date is 30 December 1899; choose Tools - Options - OpenOffice.org Calc - Calculate to change this to 1 January 1904 for Apple software, or 1 January 1900 for old StarCalc 1.0 software if necessary.

Times are represented as a fraction of a day - for example 0.5 is half a day, or 12 hours, or 12 noon; 0.25 is a quarter of a day, or 6 am. Thus 39441.25 represents 6am on 25Dec07.

It would be extremely inconvenient if, in order to enter a date in a cell, we had to work out the date-time serial number, enter it and then change the display format. Therefore Calc tries to do this for us: if we enter 25Dec07, Calc recognises that this is a date, converts it to the date-time serial number, and sets the format to display as a date. Calc recognises a wide range of possible date entries - for example 25-Dec-07, 25 December 2007, Dec 25 07, and 25/12/07 or 12/25/07 (these last two depend on the date system you use - whether days or months are written first).

If we only enter 2 digits of the year (for example 07 instead of 2007) Calc needs to know whether we mean 2007 or 1907. In Tools - Options - OpenOffice.org - General you may set the range of years for which Calc will recognise years given 2 digits.

Some of Calc's date and time functions return a 'date' or a 'time' ( for example TODAY() ). This is simply the date-time serial number, but if the cell is unformatted, Calc will display the date or time rather than a number format.

The descriptions of the Calc functions use the international standard ISO date format for clarity, because this does not depend on locale; for example, 23rd May 2009 is shown as 2009-05-23.

Tips y Trucos

All major spreadsheets (including Calc) handle dates and times in a similar way, for historical reasons, but this can be a bit cumbersome. Here are some helpful examples:

The date two weeks from now

TODAY()+14. Simply add 14 days, and format the cell as a date to display it correctly.

How many days between two dates

DAYS("23Jan08"; "9Jan08") returns 14, the number of days - however you can simply subtract one date from the other; for example if cell A1 contains 23Jan08 and cell A2 contains 9Jan08, A1-A2 also returns 14.

Use NETWORKDAYS if you wish to know how many working days; for example NETWORKDAYS(A2; A1) returns 11, the number of whole workdays (including both end days but excluding weekends). NETWORKDAYS can also take account of other non-working days.

A column with dates a week apart

Format the column to display dates as you prefer. Enter the first date at the top of the column (let's say that is cell A2). Enter =A2+7 in cell A3. Copy A3, and paste down the rest of the column.

A column showing the 15th day of consecutive months

Format the column to display dates as you prefer. Enter the first date at the top of the column (say cell A2). In cell A3 enter =EDATE(A2; 1). Copy A3, and paste down the rest of the column.

A column showing the 15th day of months, quarterly (eg 15Jan, 15Apr, 15Jul, 15Oct)

Format the column to display dates as you prefer. Enter the first date at the top of the column (say cell A2). In cell A3 enter =EDATE(A2; 3). Copy A3, and paste down the rest of the column.

A column showing the last day of consecutive months

Format the column to display dates as you prefer. Enter the first date at the top of the column (say cell A2). In cell A3 enter =EOMONTH(A2; 1). Copy A3, and paste down the rest of the column.

The last working day of the month

WORKDAY(EOMONTH(A1;0)+1;-1) finds the date of last weekday (Monday-Friday), where cell A1 contains the month or a date in that month. See WORKDAY() for how to cater for holidays and other non-working days.

Summing hours and minutes (more than 24 hours)

Select the cells to sum; from the menu: Format - Cells...; Category = User-defined; Format code = [HH]:MM. You can now enter hours and minutes, for example 15:15, 25:30. Summing them gives 40:45, displayed correctly.

Summing minutes and seconds (more than 60 minutes)

Use the example for hours and minutes above, but pretend that you are entering minutes and seconds, rather than hours and minutes. So entering 15:15 means 15 minutes 15 seconds.

See also

Functions listed by category