Documentation/How Tos/Calc: Date & Time functions
List of Calc Date & Time functions
| TODAY | returns today's date. |
| NOW | returns the current date and time. |
| YEAR | returns the year of a given date. |
| MONTH | returns the month of a given date. |
| DAY | returns the day of a given date. |
| WEEKDAY | returns the day of the week for a given date. |
| HOUR | returns the hour of a given time. |
| MINUTE | returns the minute of a given time. |
| SECOND | returns the seconds of a given time. |
| DATE | returns the date, given the year, month and day of the month. |
| DATEVALUE | returns a date-time serial number from a text date. |
| TIME | returns the time, given hours, minutes and seconds. |
| TIMEVALUE | returns a date-time serial number from a text time. |
| EOMONTH | returns the date of the last day of a month. |
| EDATE | returns a date a number of months away. |
| WORKDAY | returns a date a given number of workdays away. |
| NETWORKDAYS | returns the number of workdays between two dates. |
| DAYS | returns the number of days between two dates. |
| DAYS360 | returns the number of days between two dates, using the 360 day year. |
| WEEKNUM | returns the ISO week number of a given date. |
| WEEKNUM_ADD | returns the non-ISO week number of a given date. |
| EASTERSUNDAY | returns the date of Easter Sunday in a given year. |
| YEARFRAC | returns the number of years including fraction between two dates. |
Date and Time overview
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, 25/12/07 or 12/25/07, and 25-12-07 or 12-25-07 (these last two depend on the date system you use).
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.
Tips and Tricks
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.
