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 time 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 W. The WEEKDAY() 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 (ISO8601) WEEKNUM_ADD Week number for a date (Excel) 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 α represents the day of the week for January 1 ISO8601 week number method Excel week number method ------ Function → → → → WEEKNUM(date;2) WEEKNUM_ADD(date;2) Week 1 definition First week with majority January 1 to first Sunday of its days in the year Week numbering By their Thursdays, first By their Sundays, first one one is always in week 1 is always in week 1 All weeks have 7 days? Yes No; If α≠Mon week 1 has no Monday Last week might not have a Sunday Jan 1 and previous day No (α≠Mon), Yes (α=Mon) Yes, but first and last weeks may have in different weeks? less than 7 days Jan 1 in week 1? Yes (α=Mon-Thu), no (α=Fri-Sun) Yes, but if α≠Mon week 1 has no Monday Highest week number 52 or 53 53 or 54 Date in week 1 January 4 January 1 Date in week 53 Possibly none December 30 Date in week 54 Never Only December 31 in leap year and α=Sun Date in last week Last Thursday and December 28 December 31 Dec 31 in last week? Yes (α=Thu-Sun), no (α=Mon-Wed) Yes DATE(yyyy;1;-2)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Monday of ISO8601 week w in year yyyy DATE(yyyy;1;-1)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Tuesday of ISO8601 week w in year yyyy DATE(yyyy;1;+0)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Wednesday of ISO8601 week w in year yyyy DATE(yyyy;1;+1)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Thursday of ISO8601 week w in year yyyy DATE(yyyy;1;+2)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Friday of ISO8601 week w in year yyyy DATE(yyyy;1;+3)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Saturday of ISO8601 week w in year yyyy DATE(yyyy;1;+4)-WEEKDAY(DATE(yyyy;1;4);2)+w*7 Sunday of ISO8601 week w in year yyyy DATE(yyyy;1;-5)-WEEKDAY(DATE(yyyy;1;1);2)+w*7 Monday of Excel week w in year yyyy DATE(yyyy;1;-4)-WEEKDAY(DATE(yyyy;1;1);2)+w*7 Tuesday of Excel week w in year yyyy DATE(yyyy;1;-3)-WEEKDAY(DATE(yyyy;1;1);2)+w*7 Wednesday of Excel week w in year yyyy DATE(yyyy;1;-2)-WEEKDAY(DATE(yyyy;1;1);2)+w*7 Thursday of Excel week w in year yyyy DATE(yyyy;1;+1)-WEEKDAY(DATE(yyyy;1;1);2)+w*7 Friday of Excel week w in year yyyy DATE(yyyy;1;+0)-WEEKDAY(DATE(yyyy;1;1);2)+w*7 Saturday of Excel week w in year yyyy DATE(yyyy;1;+1)-WEEKDAY(DATE(yyyy;1;1);2)+w*7 Sunday of Excel week w in year yyyy
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 α represents the day of the week for January 1 ISO8601 week number method Excel week number method ------ Function → → → → WEEKNUM(date;1) WEEKNUM_ADD(date;1) Week 1 definition First week with majority January 1 to first Saturday of its days in the year Week numbering By their Wednesdays, first By their Saturdays, first one one is always in week 1 is always in week 1 All weeks have 7 days? Yes No; If α≠Sun week 1 has no Sunday Last week might not have a Saturday Jan 1 and previous day No (α≠Sun), Yes (α=Sun) Yes, but first and last weeks may have in different weeks? less than 7 days Jan 1 in week 1? Yes (α=Sun-Wed), no (α=Thu-Sat) Yes, but if α≠Sun week 1 has no Sunday Highest week number 52 or 53 53 or 54 Date in week 1 January 4 January 1 Date in week 53 Possibly none December 30 Date in week 54 Never Only December 31 in leap year and α=Sun Date in last week Last Wednesday and December 28 December 31 Dec 31 in last week? Yes (α=Wed-Sat), no (α=Sun-Tue) Yes DATE(yyyy;1;-2)-WEEKDAY(DATE(yyyy;1;4);1)+w*7 Sunday of ISO8601 week w in year yyyy DATE(yyyy;1;-1)-WEEKDAY(DATE(yyyy;1;4);1)+w*7 Monday of ISO8601 week w in year yyyy DATE(yyyy;1;+0)-WEEKDAY(DATE(yyyy;1;4);1)+w*7 Tuesday of ISO8601 week w in year yyyy DATE(yyyy;1;+1)-WEEKDAY(DATE(yyyy;1;4);1)+w*7 Wednesday of ISO8601 week w in year yyyy DATE(yyyy;1;+2)-WEEKDAY(DATE(yyyy;1;4);1)+w*7 Thursday of ISO8601 week w in year yyyy DATE(yyyy;1;+3)-WEEKDAY(DATE(yyyy;1;4);1)+w*7 Friday of ISO8601 week w in year yyyy DATE(yyyy;1;+4)-WEEKDAY(DATE(yyyy;1;4);1)+w*7 Saturday of ISO8601 week w in year yyyy DATE(yyyy;1;-5)-WEEKDAY(DATE(yyyy;1;1);1)+w*7 Monday of Excel week w in year yyyy DATE(yyyy;1;-4)-WEEKDAY(DATE(yyyy;1;1);1)+w*7 Tuesday of Excel week w in year yyyy DATE(yyyy;1;-3)-WEEKDAY(DATE(yyyy;1;1);1)+w*7 Wednesday of Excel week w in year yyyy DATE(yyyy;1;-2)-WEEKDAY(DATE(yyyy;1;1);1)+w*7 Thursday of Excel week w in year yyyy DATE(yyyy;1;+1)-WEEKDAY(DATE(yyyy;1;1);1)+w*7 Friday of Excel week w in year yyyy DATE(yyyy;1;+0)-WEEKDAY(DATE(yyyy;1;1);1)+w*7 Saturday of Excel week w in year yyyy DATE(yyyy;1;+1)-WEEKDAY(DATE(yyyy;1;1);1)+w*7 Sunday of Excel week w in year yyyy
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 mmTo 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+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-1) Thursday nearest to that date date+4-WEEKDAY(date-2) Friday nearest to that date date+4-WEEKDAY(date-3) Saturday nearest to 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+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
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 time
INT(UNIXtime*TIMEVALUE("0:00:01")+DATEVALUE("1970-01-01")) UNIX time to Calc date (date-DATEVALUE("1970-01-01"))/TIMEVALUE("0:00:01") Date (if UTC) to UNIX timeHow to get UTC DateTime in a spreadsheet or in Basic
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 rangeThe 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.
Section W. WEEKDAY() function
Day of week Mon Tue Wed Thu Fri Sat Sun WEEKDAY(date;1) 2 3 4 5 6 7 1 WEEKDAY(date;2) 1 2 3 4 5 6 7 WEEKDAY(date;3) 0 1 2 3 4 5 6
Questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.