## [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

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 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 dayDATEVALUE       Date, given a text valueEASTERSUNDAY    Date of Easter, given a yearEDATE           Adjusted date, given number of monthsEOMONTH         Date at the end of a monthTODAY           Current dateWORKDAY         Adjusted date, skipping leisure daysWORKDAY.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 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 dateDAYS                  Days between datesDAYS360               Days between dates using 360-day yearDAYSINMONTH           Number of days in a specific monthDAYSINYEAR            Number of days in a specific yearISLEAPYEAR            TRUE if date is in leap year, else FALSEISOWEEKNUM            Week number for a date (LibreOffice)MONTHS                Months between dates as an integerMONTH                 Month number (1-12) from a dateNETWORKDAYS           Number of work days between datesNETWORKDAYS_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 dateWEEKNUM               Week number for a dateWEEKNUM_EXCEL2003     Week number for a date (LibreOffice)WEEKS                 Weeks between dates as an integerWEEKSINYEAR           Number of weeks in a year (52-53)YEAR                  Year number from a dateYEARFRAC              Years between dates as a decimal fractionYEARS                 Years between dates as an integer`

Section C. Incrementing and decrementing dates
`Add or subtract n days to a date:   date+n           or date-nAdd or subtract n weeks to a date:  date+n*7         or date-n*7Add 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-startIn 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 datedate-WEEKDAY(date;2)+2  Tuesday   of the week with that datedate-WEEKDAY(date;2)+3  Wednesday of the week with that datedate-WEEKDAY(date;2)+4  Thursday  of the week with that datedate-WEEKDAY(date;2)+5  Friday    of the week with that datedate-WEEKDAY(date;2)+6  Saturday  of the week with that datedate-WEEKDAY(date;2)+7  Sunday    of the week with that dateWEEKNUM(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 yearWEEKNUM_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 MondayDATE(yyyy;1;-2)-WEEKDAY(DATE(yyyy;1;4);2)+w*7  Monday    of year yyyy week wDATE(yyyy;1;-1)-WEEKDAY(DATE(yyyy;1;4);2)+w*7  Tuesday   of year yyyy week wDATE(yyyy;1;+0)-WEEKDAY(DATE(yyyy;1;4);2)+w*7  Wednesday of year yyyy week wDATE(yyyy;1;+1)-WEEKDAY(DATE(yyyy;1;4);2)+w*7  Thursday  of year yyyy week wDATE(yyyy;1;+2)-WEEKDAY(DATE(yyyy;1;4);2)+w*7  Friday    of year yyyy week wDATE(yyyy;1;+3)-WEEKDAY(DATE(yyyy;1;4);2)+w*7  Saturday  of year yyyy week wDATE(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 datedate-WEEKDAY(date)+2  Monday    of the week with that datedate-WEEKDAY(date)+3  Tuesday   of the week with that datedate-WEEKDAY(date)+4  Wednesday of the week with that datedate-WEEKDAY(date)+5  Thursday  of the week with that datedate-WEEKDAY(date)+6  Friday    of the week with that datedate-WEEKDAY(date)+7  Saturday  of the week with that dateWEEKNUM(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 yearWEEKNUM_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 SundayDATE(yyyy;1;-2)-WEEKDAY(DATE(yyyy;1;4))+w*7  Sunday    of year yyyy week wDATE(yyyy;1;-1)-WEEKDAY(DATE(yyyy;1;4))+w*7  Monday    of year yyyy week wDATE(yyyy;1;+0)-WEEKDAY(DATE(yyyy;1;4))+w*7  Tuesday   of year yyyy week wDATE(yyyy;1;+1)-WEEKDAY(DATE(yyyy;1;4))+w*7  Wednesday of year yyyy week wDATE(yyyy;1;+2)-WEEKDAY(DATE(yyyy;1;4))+w*7  Thursday  of year yyyy week wDATE(yyyy;1;+3)-WEEKDAY(DATE(yyyy;1;4))+w*7  Friday    of year yyyy week wDATE(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 dateWORKDAY(date;-1)                                     Work day before a dateWORKDAY(WORKDAY(date;-1);1)                          Work day after or on a dateWORKDAY(WORKDAY(date;1);-1)                          Work day before or on a dateWORKDAY(DATE(yyyy;mm;0);1)                           First work day, year yyyy month mmWORKDAY(DATE(yyyy;mm+1;1);-1)                        Last work day, year yyyy month mmdate+MAX(7-WEEKDAY(date);1)                          Leisure day after a datedate-MAX(WEEKDAY(date)-1;1)                          Leisure day before a datedate+MOD(7-WEEKDAY(date);6)                          Leisure day after or on a datedate-MOD(WEEKDAY(date)-1;6)                          Leisure day before or on a dateDATE(yyyy;mm;MAX(7-WEEKDAY(DATE(yyyy;mm;0));1)       First leisure day, year yyyy month mmDATE(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 datedate-WEEKDAY(date+5)     Monday    before that datedate-WEEKDAY(date+4)     Tuesday   before that datedate-WEEKDAY(date+3)     Wednesday before that datedate-WEEKDAY(date+2)     Thursday  before that datedate-WEEKDAY(date+1)     Friday    before that datedate-WEEKDAY(date)       Saturday  before that datedate+1-WEEKDAY(date)     Sunday    before or on that datedate+1-WEEKDAY(date+6)   Monday    before or on that datedate+1-WEEKDAY(date+5)   Tuesday   before or on that datedate+1-WEEKDAY(date+4)   Wednesday before or on that datedate+1-WEEKDAY(date+3)   Thursday  before or on that datedate+1-WEEKDAY(date+2)   Friday    before or on that datedate+1-WEEKDAY(date+1)   Saturday  before or on that datedate+8-WEEKDAY(date)     Sunday    after that datedate+8-WEEKDAY(date+6)   Monday    after that datedate+8-WEEKDAY(date+5)   Tuesday   after that datedate+8-WEEKDAY(date+4)   Wednesday after that datedate+8-WEEKDAY(date+3)   Thursday  after that datedate+8-WEEKDAY(date+2)   Friday    after that datedate+8-WEEKDAY(date+1)   Saturday  after that datedate+7-WEEKDAY(date+6)   Sunday    after or on that datedate+7-WEEKDAY(date+5)   Monday    after or on that datedate+7-WEEKDAY(date+4)   Tuesday   after or on that datedate+7-WEEKDAY(date+3)   Wednesday after or on that datedate+7-WEEKDAY(date+2)   Thursday  after or on that datedate+7-WEEKDAY(date+1)   Friday    after or on that datedate+7-WEEKDAY(date)     Saturday  after or on that datedate+4-WEEKDAY(date+3)   Sunday    nearest to that datedate+4-WEEKDAY(date+2)   Monday    nearest to that datedate+4-WEEKDAY(date+1)   Tuesday   nearest to that datedate+4-WEEKDAY(date)     Wednesday nearest to that datedate+4-WEEKDAY(date+6)   Thursday  nearest to that datedate+4-WEEKDAY(date+5)   Friday    nearest to that datedate+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 datedate-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  MondayDATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;6))         2nd  MondayDATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;6))         3rd  MondayDATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;6))         4th  MondayDATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;6))      Last MondayDATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;5))         1st  TuesdayDATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;5))         2nd  TuesdayDATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;5))         3rd  TuesdayDATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;5))         4th  TuesdayDATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;5))      Last TuesdayDATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;4))         1st  WednesdayDATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;4))         2nd  WednesdayDATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;4))         3rd  WednesdayDATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;4))         4th  WednesdayDATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;4))      Last WednesdayDATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;3))         1st  ThursdayDATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;3))         2nd  ThursdayDATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;3))         3rd  ThursdayDATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;3))         4th  ThursdayDATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;3))      Last ThursdayDATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;2))         1st  FridayDATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;2))         2nd  FridayDATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;2))         3rd  FridayDATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;2))         4th  FridayDATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;2))      Last FridayDATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;1))         1st  SaturdayDATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;1))         2nd  SaturdayDATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;1))         3rd  SaturdayDATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;1))         4th  SaturdayDATE(yyyy;mm+1;1)-WEEKDAY(DATE(yyyy;mm+1;1))      Last SaturdayDATE(yyyy;mm;08)-WEEKDAY(DATE(yyyy;mm;7))         1st  SundayDATE(yyyy;mm;15)-WEEKDAY(DATE(yyyy;mm;7))         2nd  SundayDATE(yyyy;mm;22)-WEEKDAY(DATE(yyyy;mm;7))         3rd  SundayDATE(yyyy;mm;29)-WEEKDAY(DATE(yyyy;mm;7))         4th  SundayDATE(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  Mondayfirst+14-WEEKDAY(first+5)                         2nd  Mondayfirst+21-WEEKDAY(first+5)                         3rd  Mondayfirst+28-WEEKDAY(first+5)                         4th  MondayEOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+6)    Last Mondayfirst+07-WEEKDAY(first+4)                         1st  Tuesdayfirst+14-WEEKDAY(first+4)                         2nd  Tuesdayfirst+21-WEEKDAY(first+4)                         3rd  Tuesdayfirst+28-WEEKDAY(first+4)                         4th  TuesdayEOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+5)    Last Tuesdayfirst+07-WEEKDAY(first+3)                         1st  Wednesdayfirst+14-WEEKDAY(first+3)                         2nd  Wednesdayfirst+21-WEEKDAY(first+3)                         3rd  Wednesdayfirst+28-WEEKDAY(first+3)                         4th  WednesdayEOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+4)    Last Wednesdayfirst+07-WEEKDAY(first+2)                         1st  Thursdayfirst+14-WEEKDAY(first+2)                         2nd  Thursdayfirst+21-WEEKDAY(first+2)                         3rd  Thursdayfirst+28-WEEKDAY(first+2)                         4th  ThursdayEOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+3)    Last Thursdayfirst+07-WEEKDAY(first+1)                         1st  Fridayfirst+14-WEEKDAY(first+1)                         2nd  Fridayfirst+21-WEEKDAY(first+1)                         3rd  Fridayfirst+28-WEEKDAY(first+1)                         4th  FridayEOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+2)    Last Fridayfirst+07-WEEKDAY(first)                           1st  Saturdayfirst+14-WEEKDAY(first)                           2nd  Saturdayfirst+30-WEEKDAY(first)                           3rd  Saturdayfirst+28-WEEKDAY(first)                           4th  SaturdayEOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0)+1)    Last Saturdayfirst+07-WEEKDAY(first+6)                         1st  Sundayfirst+14-WEEKDAY(first+6)                         2nd  Sundayfirst+21-WEEKDAY(first+6)                         3rd  Sundayfirst+28-WEEKDAY(first+6)                         4th  SundayEOMONTH(first;0)+1-WEEKDAY(EOMONTH(first;0))      Last Sunday`

Section P. 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  MondayEOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+6)   2nd  MondayEOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+6)   3rd  MondayEOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+6)   4th  Mondaylast+1-WEEKDAY(last+6)                            Last MondayEOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+5)   1st  TuesdayEOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+5)   2nd  TuesdayEOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+5)   3rd  TuesdayEOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+5)   4th  Tuesdaylast+1-WEEKDAY(last+5)                            Last TuesdayEOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+4)   1st  WednesdayEOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+4)   2nd  WednesdayEOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+4)   3rd  WednesdayEOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+4)   4th  Wednesdaylast+1-WEEKDAY(last+4)                            Last WednesdayEOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+3)   1st  ThursdayEOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+3)   2nd  ThursdayEOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+3)   3rd  ThursdayEOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+3)   4th  Thursdaylast+1-WEEKDAY(last+3)                            Last ThursdayEOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+2)   1st  FridayEOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+2)   2nd  FridayEOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+2)   3rd  FridayEOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+2)   4th  Fridaylast+1-WEEKDAY(last+2)                            Last FridayEOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1)+1)   1st  SaturdayEOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1)+1)   2nd  SaturdayEOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1)+1)   3rd  SaturdayEOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1)+1)   4th  Saturdaylast+1-WEEKDAY(last+1)                            Last SaturdayEOMONTH(last;-1)+08-WEEKDAY(EOMONTH(last;-1))     1st  SundayEOMONTH(last;-1)+15-WEEKDAY(EOMONTH(last;-1))     2nd  SundayEOMONTH(last;-1)+22-WEEKDAY(EOMONTH(last;-1))     3rd  SundayEOMONTH(last;-1)+29-WEEKDAY(EOMONTH(last;-1))     4th  Sundaylast+1-WEEKDAY(last)                              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 endNETWORKDAYS(start;end)                             Work days    in start to endend-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.

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 yyyyDATE(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 yearYYYY   2000         4-character yearM      1            1-character or 2-character month numberMM     01           2-character month numberMMM    Jan          3-character month abbreviation, first letter only capitalizedMMMM   January      Month name, first letter only capitalizedMMMMM  J            First letter of name of month, capitalizedD      2            1-character or 2-character day numberDD     02           2-character day numberDDD    Sun          3-character day of week abbreviation, first letter only capitalizedDDDD   Sunday       Day of week, first letter only capitalizedNN     Sun          3-character day of week abbreviation, first letter only capitalizedNNN    Sunday       Day of week, first letter only capitalizedNNNN   Sunday,      Day of week, first letter only capitalized, followed by a commaW      W            The letter "W", not a 1-character calendar weekWW     01           2-character calendar week from WEEKNUM(«date»;1)Q      Q1           "Q" followed by the quarterQQ     1st quarter  Ordinal followed by " quarter"`

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

MrProgrammer
Moderator

Posts: 4041
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA