12 April 2014: The OpenOffice Wiki is not, and never was, affected by the heartbleed bug. Users' passwords are safe and wiki users do not need take any actions.

Documentation/How Tos/Calc: Date & Time functions

From Apache OpenOffice Wiki
Jump to: navigation, search


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.
DAYSINMONTH returns the number of days in the month of the given date.
DAYSINYEAR returns the number of days in the year of the given date.
WEEKS returns the number of weeks between two dates.
WEEKSINYEAR returns the number of weeks in the year of the given date.
WEEKNUM returns the ISO week number of a given date.
WEEKNUM_ADD returns the non-ISO week number of a given date.
MONTHS returns the number of months between two dates.
YEARS returns the number of years between two dates.
ISLEAPYEAR tests if a date is in a leap year.
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, 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 in these help pages 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 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.

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.


Years before 1925

Advanced topic:


Gregorian and Julian calendars


The calendar system we use today is called the Gregorian calendar. Every leap year, the month of February has 29 days instead of 28. We often think that leap years occur every four years (which works well for present day dates), but in fact with our Gregorian calendar the years 1800, 1900, 2100, 2200 ... are not leap years; a leap year occurs if the year is divisible by 4 and also not divisible by 100, or if it is divisible by 400. This is simply a convention that tries to take account of the time it takes the earth to go round the sun.
The Gregorian calendar was adopted in different countries at different times. In Italy and Spain the day after 4th October 1582 was 15th October 1582. In Great Britain the change was made in 1752. The USA changed between 1582 and 1867 (different areas at different times). The last countries (for example Greece) changed as late as the 1920s.
Calc calculates all dates back to 15th October 1582 using the Gregorian calendar. The day before this is 4th October 1582, and Calc uses the Julian calendar for dates before this.
In the Julian calendar used before 4th October 1582, leap years occur if the year is divisible by 4 - that is every 4 years; for example 1500 is a leap year.
However Calc functions should not be used with dates before 15th October 1582 - for example DAY("1582-10-04") returns 14 rather than 4.
Obviously some care is needed when interpreting early dates, even with dates as recent as 1924, to ensure that the dates are relevant to the country or situation.
Very early dates, for example 8AD and before, should not be used in any event, as the calendar systems were uncertain.
The external webpage Frequently Asked Questions about Calendars has more information.


Portability


Excel states that the first date in its date system is either 1st January 1900, or 2nd January 1904 (depending on settings); earlier dates are not valid.
There is a known issue in Excel - it calculates year 1900 as a leap year, for historical reasons (apparently for compatibility with an earlier spreadsheet), whereas in the Gregorian calendar 1900 is not a leap year. This only affects the months of January and February in 1900, as Excel's date system is not valid before that. Later years such as 2100, 2200 are correctly calculated.
Calc correctly identifies that 1900 is not a leap year, and Calc's date system is valid before that date, as described above.

Financial date systems

Advanced topic:
In the days before computers became widespread, various systems were devised to make manual date calculations easier. Some of these are still in use, and are available in Calc, notably in Financial Functions. This unfortunately makes life today a little harder.


Days between two dates; days in a year


One issue here is to determine the number of days between two given dates, date1 and date2 (date2 after date1), respectively day1, month1, year1 and day2, month2, year2, according to a basis of calculation as follows:
0: - US method (NASD), 12 months of 30 days each (30US/360)
NASD was the forerunner to FINRA (both are US institutions). The system is:
1. If both date1 and date2 are both the last day of February, day2 is changed to 30.
2. If day1 is 31 or date1 the last day in February, day1 is changed to 30.
3. If day1 is now 30 and day2 is 31, day2 is changed to 30.
Each month is now assumed to have 30 days, and the result calculated.
Unfortunately Excel implements this system incorrectly - it omits step 1. For compatibility Calc now gives the same result as Excel.
Notice that under this system the (number of days between date1 and date2) is not necessarily the same as -1 * (number of days between date2 and date1).
1: - Actual number of days in months, exact number of days in year
This system is simply the normal calendar; 28 days in February or 29 days in a leap year. The result is DAYS(date2; date1). The number of days in a year is 365, 366 or somewhere in between (to be advised).
2: - Actual number of days in month, year has 360 days
The number of days between two dates is calculated as for basis 1. Whole years are counted as 360 days each. For example, the number of days between 2008-01-01 and 2009-01-01 is 366 (a leap year); the year between those dates has 360 days.
3: - Actual number of days in month, year has 365 days
The number of days between two dates is calculated as for basis 1. Whole years are counted as 365 days each. For example, the number of days between 2008-01-01 and 2009-01-01 is 366 (a leap year); the year between those dates has 365 days.
4: - European method, 12 months of 30 days each (30E/360)
If either day1 or day2 is 31, it is changed to 30. Each month is now assumed to have 30 days, and the result calculated.
Other financial date systems are in use, but have not been implemented in Excel or Calc.


Whole months before or after a given date


Another issue is to determine a date that is a whole number of months or years before or after another date. This is important for example in the COUPDAYBS function.
The rule seems to be that if the original date is the last day of a month, then the new date is also the last day of the month; otherwise the same day of the month is used (or the nearest possible). Thus:
6 months before 2008-09-15 is 2008-03-15
6 months before 2008-08-31 is 2008-2-29 (leap year)
6 months before 2008-2-29 is 2007-08-31
6 months before 2007-08-29 is 2007-2-28 (not leap year)
1 year before 2009-02-28 is 2008-02-29

See Also

Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_Date_%26_Time_functions&oldid=227653"
Views
Personal tools
Navigation
Print/export
Tools