[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. 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 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 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 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: 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 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 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.

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"

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: 3945
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 2 guests