[Tutorial] Calc date formulas

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Tutorial] Calc date formulas

Postby MrProgrammer » Wed Mar 18, 2020 4:35 am

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.

Summary:
Section A. Functions which return dates
Section B. Functions which return information from dates
Section C. Incrementing and decrementing dates
Section D. Determine years/months/weeks/days between dates
Section E. Calculations for weeks that run Monday through Sunday (ISO8601 standard)
Section F. Calculations for weeks that run Sunday through Saturday
Section G. DATEDIF translations
Section H. Work days and leisure days
Section I. Days of the week before and after a date
Section J. Convert date to/from year yyyy and day-of-year nnn
Section K. UNIX timestamp conversions
SectioN L. Determine if a date is in a date range (start through end)
Section M. 1st/2nd/3rd/4th/last of a specific day of week, given yyyy and mm
Section N. 1st/2nd/3rd/4th/last of a specific day of week, given date of the first of the month
Section O. 1st/2nd/3rd/4th/last of a specific day of week, given date of the last of the month
Section P. Count specific days in a range of dates (start to end)
Section Q. Insert a static timestamp or datestamp in a cell
Section R. Determine dates common to two date ranges

Section A. Functions which return dates
Dates in Calc are integers as described in concepts 2. Controlling how data is displayed and 3. Dates in cells in Ten concepts that every Calc user should know.
DATE            Date, given year, month, and day
DATEVALUE Date, given a text value
EASTERSUNDAY Date of Easter, given a year
EDATE Adjusted date, given number of months
EOMONTH Date at the end of a month
TODAY Current date
WORKDAY Adjusted date, skipping leisure days
WORKDAY.INTL Adjusted date, skipping leisure days (LibreOffice)

Section B. Functions which return information from dates
Dates can be specified in formulas by referencing a cell containing a date, by using a function which returns a date (above), or as a text constant in the form "yyyy-mm-dd". You cannot specify a date in a formula as you might type it in a cell, say 31-10-2020 or 7/4/2020, since - and / are the subtraction and division operators.
DATEDIF               Number of days/months/years between dates (LibreOffice)
DAY Day number (1-31) from a date
DAYS Days between dates
DAYS360 Days between dates using 360-day year
DAYSINMONTH Number of days in a specific month
DAYSINYEAR Number of days in a specific year
ISLEAPYEAR TRUE if date is in leap year, else FALSE
ISOWEEKNUM Week number for a date (LibreOffice)
MONTHS Months between dates as an integer
MONTH Month number (1-12) from a date
NETWORKDAYS Number of work days between dates
NETWORKDAYS_EXCEL2003 Number of work days between dates (LibreOffice)
NETWORKDAYS.INTL Number of work days between dates (LibreOffice)
WEEKDAY Day of week (1-7) of a date
WEEKNUM Week number for a date
WEEKNUM_EXCEL2003 Week number for a date (LibreOffice)
WEEKS Weeks between dates as an integer
WEEKSINYEAR Number of weeks in a year (52-53)
YEAR Year number from a date
YEARFRAC Years between dates as a decimal fraction
YEARS Years between dates as an integer

Section C. Incrementing and decrementing dates
Add or subtract n days to a date:   date+n           or date-n
Add or subtract n weeks to a date: date+n*7 or date-n*7
Add or subtract n months to a date: EDATE(date;n) or EDATE(date;-n)
Add or subtract n years to a date: EDATE(date;n*12) or EDATE(date;-n*12)
When using EDATE, the day of the month does not change unless it would be larger than the number of days in the target month.
In that case, EDATE's value is the last day of that month.

Section D. Determine years/months/weeks/days between dates, where the difference between two identical dates is zero.
In days:        end-start
In full weeks: QUOTIENT(end-start;7)
In full months: MONTHS(start;end;0)
In full years: YEARS(start;end;0)
As nnW nD: TEXT(QUOTIENT(end-start;7);"0\W ")&TEXT(MOD(end-start;7);"0\D")
As nnM nnD: TEXT(MONTHS(start;end;0);"0\M ")&TEXT(end-EDATE(start;MONTHS(start;end;0));"0\D")
As nnY nnnD: TEXT(YEARS(start;end;0);"0\Y ")&TEXT(end-EDATE(start;YEARS(start;end;0)*12);"0\D")
As nnY nnM nnD: TEXT(YEARS(start;end;0);"0\Y ")&TEXT(MOD(MONTHS(start;end;0);12);"0\M ")
&TEXT(end-EDATE(start;MONTHS(start;end;0));"0\D")
The first four formulas return numbers (integers); the last four return text.
A person's age in years is YEARS(birthdate;TODAY();0).

Section E. Calculations for weeks that run Monday through Sunday (ISO8601 standard)
date-WEEKDAY(date;2)+1  Monday    of the week with that date
date-WEEKDAY(date;2)+2 Tuesday of the week with that date
date-WEEKDAY(date;2)+3 Wednesday of the week with that date
date-WEEKDAY(date;2)+4 Thursday of the week with that date
date-WEEKDAY(date;2)+5 Friday of the week with that date
date-WEEKDAY(date;2)+6 Saturday of the week with that date
date-WEEKDAY(date;2)+7 Sunday of the week with that date
WEEKNUM(date;2) Week number for the date, where
Week 1 is defined as the first week with the majority of its days in the year
Week 1 is always the week containing yyyy-01-04
Week 1 is always the one with the year's first Thursday
A Year has 53 weeks if:
• it begins on Thursday, or
• it begins on Wednedsay and it's leap year
DATE(yyyy;1;-2)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Monday of year yyyy week w
DATE(yyyy;1;-1)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Tuesday of year yyyy week w
DATE(yyyy;1;+0)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Wednesday of year yyyy week w
DATE(yyyy;1;+1)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Thursday of year yyyy week w
DATE(yyyy;1;+2)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Friday of year yyyy week w
DATE(yyyy;1;+3)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Saturday of year yyyy week w
DATE(yyyy;1;+4)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Sunday of year yyyy week w

Section F. Calculations for weeks that run Sunday through Saturday
date-WEEKDAY(date)+1  Sunday    of the week with that date
date-WEEKDAY(date)+2 Monday of the week with that date
date-WEEKDAY(date)+3 Tuesday of the week with that date
date-WEEKDAY(date)+4 Wednesday of the week with that date
date-WEEKDAY(date)+5 Thursday of the week with that date
date-WEEKDAY(date)+6 Friday of the week with that date
date-WEEKDAY(date)+7 Saturday of the week with that date
WEEKNUM(date;1) Week number for the date, where
Week 1 is defined as the first week with the majority of its days in the year
Week 1 is always the week containing yyyy-01-04
Week 1 is always the one with the year's first Wednesday
A year has 53 weeks if:
• it begins on Wednesday, or
• it begins on Tuesday and it's leap year
DATE(yyyy;1;-2)-WEEKDAY(DATE(yyyy;1;4))+w*7 Sunday of year yyyy week w
DATE(yyyy;1;-1)-WEEKDAY(DATE(yyyy;1;4))+w*7 Monday of year yyyy week w
DATE(yyyy;1;+0)-WEEKDAY(DATE(yyyy;1;4))+w*7 Tuesday of year yyyy week w
DATE(yyyy;1;+1)-WEEKDAY(DATE(yyyy;1;4))+w*7 Wednesday of year yyyy week w
DATE(yyyy;1;+2)-WEEKDAY(DATE(yyyy;1;4))+w*7 Thursday of year yyyy week w
DATE(yyyy;1;+3)-WEEKDAY(DATE(yyyy;1;4))+w*7 Friday of year yyyy week w
DATE(yyyy;1;+4)-WEEKDAY(DATE(yyyy;1;4))+w*7 Saturday of year yyyy week w

Section G. DATEDIF translations
DATEDIF(start;end;"Y")  is YEARS(start;end;0)
DATEDIF(start;end;"YM") is MOD(MONTHS(start;end;0);12)
DATEDIF(start;end;"YD") is end-EDATE(start;YEARS(start;end;0)*12)
DATEDIF(start;end;"M") is MONTHS(start;end;0)
DATEDIF(start;end;"MD") is end-EDATE(start;MONTHS(start;end;0))
DATEDIF(start;end;"D") is end-start
YEARS(start;end;0) is DATEDIF(start;end;"Y")
MONTHS(start;end;0) is DATEDIF(start;end;"M")
WEEKS(start;end;0) is QUOTIENT(end-start;7)
DAYS(end;start) is DATEDIF(start;end;"D")

Section H. Work days and leisure days
Work days are Monday through Friday. Leisure days are Saturday and Sunday. When a week runs Monday through Sunday, the work days are at the beginning and the leisure days follow them. When a week runs Sunday through Saturday, the work days are in the middle and the leisure days are at both ends.
WORKDAY(date;1)                                      Work day after a date
WORKDAY(date;-1) Work day before a date
WORKDAY(WORKDAY(date;-1);1) Work day after or on a date
WORKDAY(WORKDAY(date;1);-1) Work day before or on a date
WORKDAY(DATE(yyyy;mm;0);1) First work day, year yyyy month mm
WORKDAY(DATE(yyyy;mm+1;1);-1) Last work day, year yyyy month mm
date+MAX(7-WEEKDAY(date);1) Leisure day after a date
date-MAX(WEEKDAY(date)-1;1) Leisure day before a date
date+MOD(7-WEEKDAY(date);6) Leisure day after or on a date
date-MOD(WEEKDAY(date)-1;6) Leisure day before or on a date
DATE(yyyy;mm;MAX(7-WEEKDAY(DATE(yyyy;mm;0));1) First leisure day, year yyyy month mm
DATE(yyyy;mm+1;-MOD(WEEKDAY(DATE(yyyy;mm+1;0))-1;6)) Last leisure day, year yyyy month mm
To count work days or leisure days in a month, see section N below.

Section I. Days of the week before and after a date
date-WEEKDAY(date+6)    Sunday    before that date
date-WEEKDAY(date+5) Monday before that date
date-WEEKDAY(date+4) Tuesday before that date
date-WEEKDAY(date+3) Wednesday before that date
date-WEEKDAY(date+2) Thursday before that date
date-WEEKDAY(date+1) Friday before that date
date-WEEKDAY(date) Saturday before that date
date+1-WEEKDAY(date) Sunday before or on that date
date+1-WEEKDAY(date+6) Monday before or on that date
date+1-WEEKDAY(date+5) Tuesday before or on that date
date+1-WEEKDAY(date+4) Wednesday before or on that date
date+1-WEEKDAY(date+3) Thursday before or on that date
date+1-WEEKDAY(date+2) Friday before or on that date
date+1-WEEKDAY(date+1) Saturday before or on that date
date+8-WEEKDAY(date) Sunday after that date
date+8-WEEKDAY(date+6) Monday after that date
date+8-WEEKDAY(date+5) Tuesday after that date
date+8-WEEKDAY(date+4) Wednesday after that date
date+8-WEEKDAY(date+3) Thursday after that date
date+8-WEEKDAY(date+2) Friday after that date
date+8-WEEKDAY(date+1) Saturday after that date
date+7-WEEKDAY(date+6) Sunday after or on that date
date+7-WEEKDAY(date+5) Monday after or on that date
date+7-WEEKDAY(date+4) Tuesday after or on that date
date+7-WEEKDAY(date+3) Wednesday after or on that date
date+7-WEEKDAY(date+2) Thursday after or on that date
date+7-WEEKDAY(date+1) Friday after or on that date
date+7-WEEKDAY(date) Saturday after or on that date

Section J. Convert date to/from year yyyy and day-of-year nnn (Jan1=1, Jan31=31, Feb1=32, Dec31=365/366)
DATE(yyyy;1;nnn)       Date for day nnn in year yyyy        
YEAR(date) Determines yyyy from a date
date-DATE(yyyy;1;0) Determines nnn from a date

Section K. Convert date to/from UNIX timestamp
timestamp*TIMEVALUE("0:00:01")+DATEVALUE("1970-01-01")   Timestamp to date
(date-DATEVALUE("1970-01-01"))/TIMEVALUE("0:00:01") Date to timestamp

Section L. Determine if a date is in a date range (start through end)
MEDIAN(start;date;end)=date   Value is TRUE if date is in the range
The three dates may be provided to MEDIAN in any order.

Section M. 1st/2nd/3rd/4th/last of a specific day of week, given yyyy and mm
DATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;6))         1st  Monday
DATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;6)) 2nd Monday
DATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;6)) 3rd Monday
DATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;6)) 4th Monday
DATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;6)) Last Monday
DATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;5)) 1st Tuesday
DATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;5)) 2nd Tuesday
DATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;5)) 3rd Tuesday
DATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;5)) 4th Tuesday
DATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;5)) Last Tuesday
DATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;4)) 1st Wednesday
DATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;4)) 2nd Wednesday
DATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;4)) 3rd Wednesday
DATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;4)) 4th Wednesday
DATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;4)) Last Wednesday
DATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;3)) 1st Thursday
DATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;3)) 2nd Thursday
DATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;3)) 3rd Thursday
DATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;3)) 4th Thursday
DATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;3)) Last Thursday
DATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;2)) 1st Friday
DATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;2)) 2nd Friday
DATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;2)) 3rd Friday
DATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;2)) 4th Friday
DATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;2)) Last Friday
DATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;1)) 1st Saturday
DATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;1)) 2nd Saturday
DATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;1)) 3rd Saturday
DATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;1)) 4th Saturday
DATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;1)) Last Saturday
DATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;7)) 1st Sunday
DATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;7)) 2nd Sunday
DATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;7)) 3rd Sunday
DATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;7)) 4th Sunday
DATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;7)) Last Sunday

Section N. 1st/2nd/3rd/4th/last of a specific day of week, given date of the first of the month
first+07-WEEKDAY(first+5)                         1st  Monday
first+14-WEEKDAY(first+5) 2nd Monday
first+21-WEEKDAY(first+5) 3rd Monday
first+28-WEEKDAY(first+5) 4th Monday
EOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+6) Last Monday
first+07-WEEKDAY(first+4) 1st Tuesday
first+14-WEEKDAY(first+4) 2nd Tuesday
first+21-WEEKDAY(first+4) 3rd Tuesday
first+28-WEEKDAY(first+4) 4th Tuesday
EOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+5) Last Tuesday
first+07-WEEKDAY(first+3) 1st Wednesday
first+14-WEEKDAY(first+3) 2nd Wednesday
first+21-WEEKDAY(first+3) 3rd Wednesday
first+28-WEEKDAY(first+3) 4th Wednesday
EOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+4) Last Wednesday
first+07-WEEKDAY(first+2) 1st Thursday
first+14-WEEKDAY(first+2) 2nd Thursday
first+21-WEEKDAY(first+2) 3rd Thursday
first+28-WEEKDAY(first+2) 4th Thursday
EOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+3) Last Thursday
first+07-WEEKDAY(first+1) 1st Friday
first+14-WEEKDAY(first+1) 2nd Friday
first+21-WEEKDAY(first+1) 3rd Friday
first+28-WEEKDAY(first+1) 4th Friday
EOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+2) Last Friday
first+07-WEEKDAY(first) 1st Saturday
first+14-WEEKDAY(first) 2nd Saturday
first+30-WEEKDAY(first) 3rd Saturday
first+28-WEEKDAY(first) 4th Saturday
EOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+1) Last Saturday
first+07-WEEKDAY(first+6) 1st Sunday
first+14-WEEKDAY(first+6) 2nd Sunday
first+21-WEEKDAY(first+6) 3rd Sunday
first+28-WEEKDAY(first+6) 4th Sunday
EOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)) Last Sunday

Section O. 1st/2nd/3rd/4th/last of a specific day of week, given date of the last of the month
EOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+6)   1st  Monday
EOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+6) 2nd Monday
EOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+6) 3rd Monday
EOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+6) 4th Monday
last+1-WEEKDAY(last+6) Last Monday
EOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+5) 1st Tuesday
EOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+5) 2nd Tuesday
EOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+5) 3rd Tuesday
EOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+5) 4th Tuesday
last+1-WEEKDAY(last+5) Last Tuesday
EOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+4) 1st Wednesday
EOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+4) 2nd Wednesday
EOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+4) 3rd Wednesday
EOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+4) 4th Wednesday
last+1-WEEKDAY(last+4) Last Wednesday
EOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+3) 1st Thursday
EOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+3) 2nd Thursday
EOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+3) 3rd Thursday
EOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+3) 4th Thursday
last+1-WEEKDAY(last+3) Last Thursday
EOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+2) 1st Friday
EOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+2) 2nd Friday
EOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+2) 3rd Friday
EOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+2) 4th Friday
last+1-WEEKDAY(last+2) Last Friday
EOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+1) 1st Saturday
EOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+1) 2nd Saturday
EOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+1) 3rd Saturday
EOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+1) 4th Saturday
last+1-WEEKDAY(last+1) Last Saturday
EOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)) 1st Sunday
EOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)) 2nd Sunday
EOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)) 3rd Sunday
EOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)) 4th Sunday
last+1-WEEKDAY(last) Last Sunday

Section P. Count specific days in a range of dates (start to end)
(end-start+1-WEEKDAY(end+6)+WEEKDAY(start+5))/7    Mondays      in start to end
(end-start+1-WEEKDAY(end+5)+WEEKDAY(start+4))/7 Tuesdays in start to end
(end-start+1-WEEKDAY(end+4)+WEEKDAY(start+3))/7 Wednesdays in start to end
(end-start+1-WEEKDAY(end+3)+WEEKDAY(start+2))/7 Thursdays in start to end
(end-start+1-WEEKDAY(end+2)+WEEKDAY(start+1))/7 Fridays in start to end
(end-start+1-WEEKDAY(end+1)+WEEKDAY(start))/7 Saturdays in start to end
(end-start+1-WEEKDAY(end)+WEEKDAY(start+6))/7 Sundays in start to end
NETWORKDAYS(start;end) Work days in start to end
end-start+1-NETWORKDAYS(start;end) Leisure days in start to end

Section Q. Insert a static timestamp or datestamp in a cell
See Favorite Recorded Calc Macros

Section R. Determine dates common to two date ranges
The intersection [XX,YY] of date intervals [SA,EA] [SB,EB] is [MAX(SA;SB),MIN(EA;EB)]. The interval is empty if XX>YY.

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3956
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Return to Calc

Who is online

Users browsing this forum: No registered users and 1 guest