[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.
Locked
User avatar
MrProgrammer
Moderator
Posts: 4416
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

[Tutorial] Calc date formulas

Post by MrProgrammer »

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. YEARS, MONTHS, WEEKS, and DAYS functions
Section H. DATEDIF translations
Section I. Work days and leisure days
Section J. Days of the week before/after/nearest a date
Section K. Convert date to/from year yyyy and day-of-year nnn
Section L. UNIX timestamp conversions
SectioN M. Determine if a date is in a date range (start through end)
Section N. 1st/2nd/3rd/4th/last of a specific day of week, given yyyy and mm
Section O. 1st/2nd/3rd/4th/last of a specific day of week, given date of the first of the month
Section P. 1st/2nd/3rd/4th/last of a specific day of week, given date of the last of the month
Section Q. Count specific days in a range of dates (start to end)
Section R. Insert a static timestamp or datestamp in a cell
Section S. Determine dates common to two date ranges
Section T. Daylight Saving Time in the United States since 2007 (238 days)
Section U. Date formatting codes for the TEXT() function or the Numbers → Format Code dialog
Section V. The DATE() function

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
All of these functions except ISLEAPYEAR return integers. 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 2019-10-31 or 7/4/2020, since - and / are the subtraction and division operators. OpenOffice does not have a QUARTER function however you can use CEILING(MONTH(date);3)/3.
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
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
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

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:  WEEKS(start,end;0)
In full months: MONTHS(start;end;0)
In full years:  YEARS(start;end;0)
As nnW nD:      TEXT(WEEKS(start,end;0);"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
   yyyy-01-01 belongs to the previous year's final week if the year begins on Friday, Saturday, or Sunday
   The previous day (31 Dec) belongs to week 1 if that date falls on Monday, Tuesday, or Wednesday 
   A Year has 52 or 53 weeks, 53 if:
   • it begins on Thursday, or
   • it begins on Wednedsay and it's leap year
WEEKNUM_ADD(date;2)     Week number for the date, where
   Week 1 begins on yyyy-01-01
   The date before yyyy-01-01 (31 Dec) is week 53, or 54 for leap years that begin on Sunday
   The date before that (30 Dec) is always week 53
   The dates of the week containing yyyy-01-01, have different week numbers unless the year begins on Monday
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
   yyyy-01-01 belongs to the previous year's final week if the year begins on Thursday, Friday, or Saturday
   The previous day (31 Dec) belongs to week 1 if that date falls on Sunday, Monday, or Tuesday
   A year has 52 or 53 weeks, 53 if:
   • it begins on Wednesday, or
   • it begins on Tuesday and it's leap year
WEEKNUM_ADD(date;1)   Week number for the date, where
   Week 1 begins on yyyy-01-01
   The date before yyyy-01-01 (31 Dec) is week 53, or 54 for leap years that begin on Saturday
   The date before that (30 Dec) is always week 53
   The dates of the week containing yyyy-01-01, have different week numbers unless the year begins on Sunday
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. YEARS, MONTHS, WEEKS, and DAYS functions
YEARS(start;end;0)        Full years from start to end, like
                             DATEDIF(start;end;"Y")
YEARS(start;end;1)        Calendar years from start to end, like
                             YEAR(end)-YEAR(start)
MONTHS(start;end;0)       Full montns from start to end, like
                             DATEDIF(start;end;"M")
MONTHS(start;end;1)       Calendar months from start to end, like
                             MONTH(end)-MONTH(start)+12*(YEAR(end)-YEAR(start))
WEEKS(start;end;0)        Full weeks from start to end, like
                             QUOTIENT(end-start;7)
WEEKS(start;end;1)        Calendar weeks (Monday to Sunday) from start to end, like
                             QUOTIENT(end-start;7)+(WEEKDAY(end;2)<WEEKDAY(start;2))
WEEKS(start+1;end+1;1)    Calendar weeks (Sunday to Saturday) from start to end, like
                             QUOTIENT(end-start;7)+(WEEKDAY(end;1)<WEEKDAY(start;1))
DAYS(end;start)           Days from start to end, like
                             end-start or DATEDIF(start;end;"D")

Section H. DATEDIF translations
DATEDIF(start;end;"Y")    Full years from start to end, like
                             YEARS(start;end;0)
DATEDIF(start;end;"YM")   Full months from start to end, excluding completed years, always < 12, like
                             MOD(MONTHS(start;end;0);12)
DATEDIF(start;end;"YD")   Days from start to end, excluding completed years, always < 366, like
                             end-EDATE(start;YEARS(start;end;0)*12)
DATEDIF(start;end;"M")    Full months from start to end, like
                             MONTHS(start;end;0)
DATEDIF(start;end;"MD")   Days from start to end, excluding completed months, always < 31, like
                             end-EDATE(start;MONTHS(start;end;0))
DATEDIF(start;end;"D")    Days from start to end, like
                             end-start

Section I. 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 J. Days of the week before/after/nearest 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
date+4-WEEKDAY(date+3)   Sunday    nearest to that date
date+4-WEEKDAY(date+2)   Monday    nearest to that date
date+4-WEEKDAY(date+1)   Tuesday   nearest to that date
date+4-WEEKDAY(date)     Wednesday nearest to that date
date+4-WEEKDAY(date+6)   Thursday  nearest to that date
date+4-WEEKDAY(date+5)   Friday    nearest to that date
date+4-WEEKDAY(date+4)   Saturday  nearest to that date

Section K. 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 L. 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 M. 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 N. 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 O. 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 P. 1st/2nd/3rd/4th/last of a specific day of week, given any date in the month
EOMONTH(date;-1)+08-WEEKDAY(EOMONTH(date;-1)+6)   1st  Monday
EOMONTH(date;-1)+15-WEEKDAY(EOMONTH(date;-1)+6)   2nd  Monday
EOMONTH(date;-1)+22-WEEKDAY(EOMONTH(date;-1)+6)   3rd  Monday
EOMONTH(date;-1)+29-WEEKDAY(EOMONTH(date;-1)+6)   4th  Monday
EOMONTH(date;0)+1-WEEKDAY(EOMONTH(date;0)+6)      Last Monday
EOMONTH(date;-1)+08-WEEKDAY(EOMONTH(date;-1)+5)   1st  Tuesday
EOMONTH(date;-1)+15-WEEKDAY(EOMONTH(date;-1)+5)   2nd  Tuesday
EOMONTH(date;-1)+22-WEEKDAY(EOMONTH(date;-1)+5)   3rd  Tuesday
EOMONTH(date;-1)+29-WEEKDAY(EOMONTH(date;-1)+5)   4th  Tuesday
EOMONTH(date;0)+1-WEEKDAY(EOMONTH(date;0)+5)      Last Tuesday
EOMONTH(date;-1)+08-WEEKDAY(EOMONTH(date;-1)+4)   1st  Wednesday
EOMONTH(date;-1)+15-WEEKDAY(EOMONTH(date;-1)+4)   2nd  Wednesday
EOMONTH(date;-1)+22-WEEKDAY(EOMONTH(date;-1)+4)   3rd  Wednesday
EOMONTH(date;-1)+29-WEEKDAY(EOMONTH(date;-1)+4)   4th  Wednesday
EOMONTH(date;0)+1-WEEKDAY(EOMONTH(date;0)+4)      Last Wednesday
EOMONTH(date;-1)+08-WEEKDAY(EOMONTH(date;-1)+3)   1st  Thursday
EOMONTH(date;-1)+15-WEEKDAY(EOMONTH(date;-1)+3)   2nd  Thursday
EOMONTH(date;-1)+22-WEEKDAY(EOMONTH(date;-1)+3)   3rd  Thursday
EOMONTH(date;-1)+29-WEEKDAY(EOMONTH(date;-1)+3)   4th  Thursday
EOMONTH(date;0)+1-WEEKDAY(EOMONTH(date;0)+3)      Last Thursday
EOMONTH(date;-1)+08-WEEKDAY(EOMONTH(date;-1)+2)   1st  Friday
EOMONTH(date;-1)+15-WEEKDAY(EOMONTH(date;-1)+2)   2nd  Friday
EOMONTH(date;-1)+22-WEEKDAY(EOMONTH(date;-1)+2)   3rd  Friday
EOMONTH(date;-1)+29-WEEKDAY(EOMONTH(date;-1)+2)   4th  Friday
EOMONTH(date;0)+1-WEEKDAY(EOMONTH(date;0)+2)      Last Friday
EOMONTH(date;-1)+08-WEEKDAY(EOMONTH(date;-1)+1)   1st  Saturday
EOMONTH(date;-1)+15-WEEKDAY(EOMONTH(date;-1)+1)   2nd  Saturday
EOMONTH(date;-1)+22-WEEKDAY(EOMONTH(date;-1)+1)   3rd  Saturday
EOMONTH(date;-1)+29-WEEKDAY(EOMONTH(date;-1)+1)   4th  Saturday
EOMONTH(date;0)+1-WEEKDAY(EOMONTH(date;0)+1)      Last Saturday
EOMONTH(date;-1)+08-WEEKDAY(EOMONTH(date;-1))     1st  Sunday
EOMONTH(date;-1)+15-WEEKDAY(EOMONTH(date;-1))     2nd  Sunday
EOMONTH(date;-1)+22-WEEKDAY(EOMONTH(date;-1))     3rd  Sunday
EOMONTH(date;-1)+29-WEEKDAY(EOMONTH(date;-1))     4th  Sunday
EOMONTH(date;0)+1-WEEKDAY(EOMONTH(date;0))        Last Sunday

Section Q. 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 R. Insert a static timestamp or datestamp in a cell
See Favorite Recorded Calc Macros

Section S. 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. The length in days of the intersection [XX,YY] is MAX(MIN(EA;EB)-MAX(SA;SB);0), where the value is zero if XX=YY.

Section T. Daylight Saving Time in the United States since 2007 (238 days)
DATE(yyyy;3;15)-WEEKDAY(DATE(yyyy;3;7))     Start of DST in year yyyy
DATE(yyyy;11;8)-WEEKDAY(DATE(yyyy;11;7))    End of DST in year yyyy

Section U. Date formatting codes for the TEXT() function or the Numbers → Format Code dialog
These codes are for the English-en locale. For other locales, see Help → Index → number formats;codes.
The example in the second column is for the date 2000-01-02, which was a Sunday.
The TEXT() function always returns a text string; to return numbers, use YEAR(), MONTH(), DAY(), etc.
YY     00           2-character year
YYYY   2000         4-character year
M      1            1-character or 2-character month number
MM     01           2-character month number
MMM    Jan          3-character month abbreviation, first letter only capitalized
MMMM   January      Month name, first letter only capitalized
MMMMM  J            First letter of name of month, capitalized
D      2            1-character or 2-character day number
DD     02           2-character day number
DDD    Sun          3-character day of week abbreviation, first letter only capitalized
DDDD   Sunday       Day of week, first letter only capitalized
NN     Sun          3-character day of week abbreviation, first letter only capitalized
NNN    Sunday       Day of week, first letter only capitalized
NNNN   Sunday,      Day of week, first letter only capitalized, followed by a comma
W      W            The letter "W", not a 1-character calendar week
WW     01           2-character calendar week from WEEKNUM(«date»;1)
Q      Q1           "Q" followed by the quarter
QQ     1st quarter  Ordinal followed by " quarter"

Section V. The DATE() function
The DATE() function returns a date, given a year, month, and day. The year should be in the range 1583 to 9957. When the month is in the range 1 to 12 and the day is in the range 1 to last-day-of-that-month, the corresponding date is returned. But the month and day are not restricted to those ranges. DATE(2021;0;n) is treated as the month prior to January 2021, that is 2020-12-n. DATE(2021;13;n) is treated as the month after December 2021, that is 2022-1-n. Similarly DATE(2021;1;0) is the day prior to 2021-01-01, that is, 2020-12-31. The date a thousand months before Halloween 2021 is DATE(2021;10-1000;31). The combination of y, m, and d would be considerd "standard" if YEAR(DATE(y;m;d)) is y, MONTH(DATE(y;m;d)) is m, DAY(DATE(y;m;d)) is d. Any fractional part of the year, month, or day is ignored. DATE(2021;9.999;5.9) is 2021-09-05. When using very old dates, be aware that locations around the world converted from the Julian Calendar to the Gregorian Calendar at different times, and dates in the, say, 1700s may represent different days depending on which system was in use there. You should not depend on the Calc functions WEEKDAY() and others to return correct information for very old dates and it may be best to store them as text since you cannot perform reliable calculations with them.

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).
Locked