Programming Language Cheat Sheets: Dates

Strings Mathematics Operators and Symbols General (Control Flow/Debugging) [TickCount] Dates Objects New Features Timelines Sections: Time Zones Displaying local dates in 'yyyy-MM-dd' form Month/Year Calculations: Add/Subtract/Difference Weekdays AM/PM Leap Years Combining Date Parts Date Systems Current Year / Birth Year / Age Android / Kotlin / java.time 14 Year Types / Reusing Diaries / Week Numbers Excel: Calculating Date To Week Number Links (Date Formats) Lower Priority Original Outline Note: UFL: Universal Function Library, a set of around 100-300 standard functions for all programming languages. Languages do not have to implement anything exactly to the UFL specification, it is a guide. See lower down for further details. UFL: DateObjectNewDemo [demonstrate Date object, by creating the local time now (see also 'DateNewLocalNow')] AutoHotkey: vDate := A_Now [e.g. vDate := 20060504030201] [note: AutoHotkey uses 14-digit 'yyyyMMddHHmmss' integers, 'substrings' of length 4/6/8/10/12 are also valid] C++: C#: Crystal: Excel: =NOW() [e.g. 38841 represents 2006-05-04 (38841 days since 1899-12-30)] Excel VBA: oDate = Now [type: Date] [note: date object to days since 1899-12-30, e.g. oDate = DateSerial(1900, 3, 1): vDate = CDbl(oDate)] [note: days since 1899-12-30 to date object, e.g. vDate = 61: oDate = CDate(vDate)] Go: Java: ___ [see also: Kotlin entries] JavaScript: oDate = new Date() [type: Date] Kotlin: oDate = java.time.LocalDateTime.now() [type: LocalDateTime] PHP: Python: oDate = datetime.datetime.now() [type: datetime] R: Ruby: Rust: Scala: Swift: UFL: DateFormatFriendly [show: weekday, Y/M/D/H/M/S, time zone] AutoHotkey: vDate := FormatTime(vDate, "ddd yyyy-MM-dd HH:mm:ss") [note: doesn't show time zone] C++: C#: Crystal: Excel: =TEXT(A1,"ddd yyyy-mm-dd hh:mm:ss") [note: doesn't show time zone] Excel VBA: vDate = Format(oDate, "ddd yyyy-mm-dd hh:mm:ss") [note: doesn't show time zone] Go: Java: JavaScript: oDate.toString() [also: oDate.toLocaleString("en-GB", {timeZone:vTZ, dateStyle:"full", timeStyle:"full"})] [e.g. vTZ = "UTC", vTZ = "Europe/London", vTZ = "America/New_York"] [also: Intl.RelativeTimeFormat] Kotlin: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("E yyyy-MM-dd HH:mm:ss")) [note: doesn't show time zone] PHP: Python: vDate = oDate.strftime("%c %z") [also (without weekday): str(oDate)] [also (without time zone): oDate.ctime()] [note: local time zone: oDate.astimezone().strftime("%a %Y-%m-%d %H:%M:%S %z")] R: Ruby: Rust: Scala: Swift: UFL: DateNewUTCNow [create a date (UTC), the date/time now] AutoHotkey: vDate := A_NowUTC C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: oDate = new Date() Kotlin: oDate = java.time.LocalDateTime.now() [also: oDate = java.time.Instant.now()] [WARNING: java.time has LocalDateTime/ZonedDateTime/Instant/OffsetDateTime, LocalDateTime/Instant objects both lack a time zone, LocalDateTime has a greater variety of methods than Instant e.g. format/getXXX/minusXXX/of/plusXXX/withXXX] PHP: Python: oDate = datetime.datetime.now().astimezone(datetime.timezone.utc) R: Ruby: Rust: Scala: Swift: UFL: DateNewUTC [create a date (UTC)] AutoHotkey: vDate := 20060504030201 C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: oDate = new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)) [WARNING: month is 0-based] [note: max 7 parts: vYear, vMonth, vDay, vHour, vMin, vSec, vMSec] [WARNING: 1 param: 'new Date(vMSecSince1970)' versus 'new Date(Date.UTC(vYear))'] Kotlin: oDate = java.time.LocalDateTime.of(2006, 5, 4, 3, 2, 1) PHP: Python: oDate = datetime.datetime(2006, 5, 4, 3, 2, 1, tzinfo=datetime.timezone.utc) R: Ruby: Rust: Scala: Swift: UFL: DateNewUTCFromStr [create a date (local time zone)][note: the functions can typically handle various date/time formats] AutoHotkey: vDate := "20060504030201" C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: oDate = new Date(Date.parse("2006-05-04 03:02:01 Z")) [note: 'Z' to treat strings as UTC] Kotlin: oDate = java.time.LocalDateTime.parse("2006-05-04T03:02:01") PHP: Python: oDate = datetime.datetime.strptime("20060504030201 +0000", "%Y%m%d%H%M%S %z") R: Ruby: Rust: Scala: Swift: UFL: DateNewLocalNow [create a date (local time zone), the date/time now] AutoHotkey: vDate := A_Now C++: C#: Crystal: Excel: =NOW() [note: days since 1899-12-30] [can use: TODAY() for the date only (with no time value)] Excel VBA: oDate = Now [also: oDate = Date + Time] Go: Java: JavaScript: oDate = new Date() Kotlin: oDate = java.time.LocalDateTime.now() PHP: Python: oDate = datetime.datetime.now() [also: to always print the time zone (offset-aware rather than offset-naive): datetime.datetime.now().astimezone()] R: Ruby: Rust: Scala: Swift: UFL: DateNewLocalMSec [get the current milliseconds (0-999) (local time zone)] AutoHotkey: vMSec := A_MSec C++: C#: Crystal: Excel: =RIGHT(TEXT(NOW(),"s.000"),3) Excel VBA: vMSec = (Timer * 1000) Mod 1000 Go: Java: JavaScript: vMSec = new Date().getMilliseconds() Kotlin: vMSec = java.time.LocalDateTime.now().getNano() / 1000000 [note: integer division (truncated division)] PHP: Python: vMSec = datetime.datetime.now().microsecond // 1000 R: Ruby: Rust: Scala: Swift: UFL: DateNewLocal [create a date (local time zone)] AutoHotkey: vDate := 20060504030201 C++: C#: Crystal: Excel: =DATE(2006,5,4)+TIME(3,2,1) Excel VBA: oDate = DateSerial(2006, 5, 4) + TimeSerial(3, 2, 1) Go: Java: JavaScript: oDate = new Date(2006, 5-1, 4, 3, 2, 1) [WARNING: month is 0-based] [note: max 7 parts: vYear, vMonth, vDay, vHour, vMin, vSec, vMSec] [WARNING: 1 param: 'new Date(vMSecSince1970)' versus 'new Date(Date.UTC(vYear))'] Kotlin: oDate = java.time.LocalDateTime.of(2006, 5, 4, 3, 2, 1) PHP: Python: oDate = datetime.datetime(2006, 5, 4, 3, 2, 1) [also: to always print the time zone: datetime.datetime(2006, 5, 4, 3, 2, 1).astimezone()] R: Ruby: Rust: Scala: Swift: UFL: DateNewLocalWithFold [create a date (local time zone) (local time zones often have an ambiguous hour when DST ends)] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: oDate = new Date(Date.parse("1999-10-31 01:30:00 GMT+0000")) [e.g. the UK: use 'GMT+0100'/'GMT+0000', for the earlier/later time] Kotlin: oDate = java.time.ZonedDateTime.parse("1999-10-31T01:30:00+00:00[Europe/London]") [e.g. the UK: use '+01:00'/'+00:00', for the earlier/later time] PHP: Python: oDate = datetime.datetime(1999, 10, 31, 1, 30, 0, fold=0) [note: set fold to 0 or 1, for the earlier/later time] [e.g. the UK: 1999-10-31, had 1am BST followed by 1am GMT] R: Ruby: Rust: Scala: Swift: UFL: DateNewLocalFromStr [create a date (local time zone)][note: the functions can typically handle various date/time formats] AutoHotkey: vDate := "20060504030201" C++: C#: Crystal: Excel: =DATEVALUE("2006-05-04 03:02:01")+TIMEVALUE("2006-05-04 03:02:01") Excel VBA: oDate = CDate("2006-05-04 03:02:01") [also: DateValue("2006-05-04 03:02:01") + TimeValue("2006-05-04 03:02:01")] Go: Java: JavaScript: oDate = new Date(Date.parse("2006-05-04 03:02:01")) Kotlin: oDate = java.time.LocalDateTime.parse("2006-05-04T03:02:01") PHP: Python: oDate = datetime.datetime.strptime("20060504030201", "%Y%m%d%H%M%S") [also: to always print the time zone: datetime.datetime.strptime("20060504030201", "%Y%m%d%H%M%S").astimezone()] R: Ruby: Rust: Scala: Swift: UFL: DateFormat [create a date string using substrings, show: weekday, Y/M/D/H/M/S, time zone] AutoHotkey: vDate := FormatTime(vDate, "ddd yyyy-MM-dd HH:mm:ss") [note: doesn't show time zone] [note: uses the Winapi's GetDateFormat/GetTimeFormat] C++: C#: Crystal: Excel: =TEXT(A1,"ddd yyyy-mm-dd hh:mm:ss") [note: doesn't show time zone] [WARNING: 'smart' behaviour is needed to determine whether 'm' means *month* or *minute* (e.g. by observing whether 'h'/'s' is near, 'm' by itself is month)] [WARNING: TEXT with 'ddd'/'dddd' gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-01-01 was actually a Monday)] Excel VBA: vDate = Format(oDate, "ddd yyyy-mm-dd hh:mm:ss") [note: doesn't show time zone] [WARNING: 'smart' behaviour is needed to determine whether 'm' means *month* or *minute* (e.g. by observing whether 'h' precedes 'm', 'm' by itself is month)] [WARNING: Excel VBA also has 'n' for minutes, but Excel sheet functions lack this] [note: 'ddd'/'dddd' in Format and WorksheetFunction.Text give correct values for Jan 1900 and Feb 1900] Go: Java: JavaScript: ___ [note: toLocaleString() has various options e.g. oDate.toLocaleString("en-CA-u-hc-h23", {weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"})] Kotlin: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("E yyyy-MM-dd HH:mm:ss")) [note: doesn't show time zone] PHP: Python: vDate = oDate.strftime("%a %Y-%m-%d %H:%M:%S %z") R: Ruby: Rust: Scala: Swift: UFL: DateFormatISOUTC [UTC time as ISO string, no time zone: '####-##-##T##:##:##.###Z' e.g. '2006-05-04T03:02:01.000Z'] AutoHotkey: vDate := FormatTime(vDate, "yyyy-MM-ddTHH:mm:ss.000Z") [note: assumes 0 milliseconds] C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: vDate = oDate.toISOString() [note: the return value is in the UTC time zone (the local time zone is ignored)] Kotlin: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) PHP: Python: vDate = oDate.astimezone(datetime.timezone.utc).strftime("%FT%T.%f")[:-3]+"Z" [also: oDate.astimezone(datetime.timezone.utc).isoformat(timespec="milliseconds")[:23] + "Z"] [note: F%/T% aren't listed on the Python documentation] R: Ruby: Rust: Scala: Swift: UFL: DateFormatISOLocal [local time as ISO string, no time zone: '####-##-##T##:##:##.###Z' e.g. '2006-05-04T03:02:01.000Z'][note: includes milliseconds] AutoHotkey: vDate := FormatTime(vDate, "yyyy-MM-ddTHH:mm:ss.000Z") [WARNING: prints 0 milliseconds] C++: C#: Crystal: Excel: =TEXT(A1,"yyyy-mm-ddThh:mm:ss.000Z") [note: *does* handle milliseconds (the Excel sheet function does, Excel VBA doesn't)] Excel VBA: vDate = Format(oDate, "yyyy-mm-ddThh:mm:ss.000Z") [WARNING: prints 0 milliseconds] Go: Java: JavaScript: vDate = new Date(oDate.valueOf()-oDate.getTimezoneOffset()*60000).toISOString() [note: valueOf/getTime are interchangeable] Kotlin: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) PHP: Python: vDate = oDate.astimezone().strftime("%FT%T.%f")[:-3]+"Z" [also: oDate.astimezone().isoformat(timespec="milliseconds")[:23] + "Z"] [note: F%/T% aren't listed on the Python documentation] R: Ruby: Rust: Scala: Swift: UFL: DateFormat14 [14-digit string (yyyyMMddHHmmss)] AutoHotkey: vDate := FormatTime(vDate, "yyyyMMddHHmmss") C++: C#: Crystal: Excel: =TEXT(A1,"yyyymmddhhmmss") Excel VBA: vDate = Format(oDate, "yyyymmddhhmmss") Go: Java: JavaScript: vDate = oDate.toISOString().replace(/\D+/g, "").slice(0, 14) [note: returns UTC time] [also (fairly similar): (using af/lt/sv/en-CA-u-hc-h23 locales): oDate.toLocaleString("af", {timeZone:"UTC"})] [note: '-u-hc-h23' means use 24-hour time] [note: using locales is best avoided as formats sometimes change] Kotlin: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) PHP: Python: vDate = oDate.strftime("%Y%m%d%H%M%S") R: Ruby: Rust: Scala: Swift: UFL: DateSplit [6-item array e.g. string (yyyy,MM,dd,HH,mm,ss) or numeric (yyyy,M,d,H,m,s)] AutoHotkey: oArray := StrSplit(FormatTime(vDate, "yyyy|MM|dd|HH|mm|ss"), "|") [note: will preserve any leading zeros] C++: C#: Crystal: Excel: ___ [note: can use: TEXT(A1,"yyyy|mm|dd|hh|mm|ss")] Excel VBA: oArray = Split(Format(oDate, "yyyy|mm|dd|hh|mm|ss"), "|") Go: Java: JavaScript: oArray = oDate.toISOString().split(/\D+/g, 6) [note: returns UTC time] Kotlin: oArray = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy|MM|dd|HH|mm|ss")).split("|") PHP: Python: oList = str.split(oDate.strftime("%Y|%m|%d|%H|%M|%S"), "|") R: Ruby: Rust: Scala: Swift: UFL: DateGetTZOffsetMinLocal [e.g. 60 or -60 (minutes) (for time zone '+01:00') (at a particular moment in time, what was the offset from UTC (including any DST) for the local time zone] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: vOffsetMin = oDate.getTimezoneOffset() [e.g. returns 60 for '+01:00'] [e.g. new Date(2006, 7-1, 1).getTimezoneOffset()] Kotlin: vOffsetMin = oTZ.getRules().getDaylightSavings(oDate.toInstant()).getSeconds() / 60 [beforehand: oTZ = java.time.ZoneId.systemDefault()] PHP: Python: vOffsetMin = oDate.astimezone().utcoffset().total_seconds() // 60 [e.g. returns 60 for '+01:00'] [also (to get both offsets): time.timezone//60 and time.altzone//60 (e.g. they return 0 and -60 respectively for Europe/London ('+01:00'))] R: Ruby: Rust: Scala: Swift: UFL: DateGetTZOffsetMin [e.g. 60 or -60 (minutes) (for time zone '+01:00') (at a particular moment in time, what was the offset from UTC (including any DST) for a specific time zone] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: vOffsetMin = (Date.parse(oDate.toLocaleString("en",{timeZone:"UTC"})+" Z")-Date.parse(oDate.toLocaleString("en",{timeZone:vTZ})+" Z"))/60000 [note: 'Z' to treat strings as UTC] [e.g. returns -60 for '+01:00'] [e.g. vTZ = "America/New_York"] Kotlin: vOffsetMin = oTZ.getRules().getDaylightSavings(oDate.toInstant()).getSeconds() / 60 PHP: Python: vOffsetMin = oDate.astimezone(oTZ).utcoffset().total_seconds() // 60 [e.g. returns 60 for '+01:00'] R: Ruby: Rust: Scala: Swift: UFL: DateGetTZOffsetStrLocal [e.g. '+01:00'/'+0100' (the number varies depending on whether DST is in force)] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: vOffset = oDate.toLocaleString("en", {day:"2-digit", timeZoneName:"longOffset"}).slice(7) || "+00:00" Kotlin: vOffset = oTZ.getRules().getOffset(oDate.toInstant()).toString() [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'Z' / '+01:00'] PHP: Python: vOffset = oDate.astimezone().strftime("%z") [e.g. '+0100'] [also: '%:z' (e.g. '+01:00')] R: Ruby: Rust: Scala: Swift: UFL: DateGetTZOffsetStr [e.g. '+01:00'/'+0100' (the number varies depending on whether DST is in force)] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: vOffset = oDate.toLocaleString("en", {timeZone:vTZ, day:"2-digit", timeZoneName:"longOffset"}).slice(7) || "+00:00" [e.g. vTZ = "Europe/London"] Kotlin: vOffset = oTZ.getRules().getOffset(oDate.toInstant()).toString() [e.g. 'Z' / '+01:00'] PHP: Python: vOffset = oDate.strftime("%z") [e.g. '+0100'] [also: '%:z' (e.g. '+01:00')] R: Ruby: Rust: Scala: Swift: UFL: DateGetTZLocationLocal [e.g. 'Europe/London' / 'America/New_York'] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: vTZ = Intl.DateTimeFormat().resolvedOptions().timeZone [e.g. 'Europe/London'] Kotlin: vTZ = oTZ.getId() [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'Europe/London'] PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateGetTZLocation [e.g. 'Europe/London' / 'America/New_York'] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: ___ Kotlin: vTZ = oDate.getZone().getId() [e.g. getId: 'Europe/London'] [also: oTZ.getDisplayName(java.time.format.TextStyle.FULL, java.util.Locale.getDefault())] [e.g. getDisplayName (text style: FULL/SHORT/NARROW): 'Europe/London'/'British Time'/'BT'] PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateGetTZNameLocal [get ST/DT name][e.g. 'Greenwich Mean Time'/'British Summer Time'/'Eastern Standard Time'][note: the name can vary depending on DST] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: vTZName = oDate.toLocaleString("en", {day:"2-digit", timeZoneName:"long"}).slice(4) Kotlin: vTZName = oDate.format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [e.g. 'GMT' / 'BST'] PHP: Python: vTZName = time.tzname [note: returns both as a tuple, e.g. '('GMT Standard Time', 'GMT Daylight Time')'] [can use (to return a time zone, although '%Z' should be avoided as it's OS-specific): oDate.astimezone().strftime("%Z")] R: Ruby: Rust: Scala: Swift: UFL: DateGetTZName [get ST/DT name][e.g. 'Greenwich Mean Time'/'British Summer Time'/'Eastern Standard Time'][note: the name can vary depending on DST] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: vTZName = oDate.toLocaleString("en", {timeZone:vTZ, day:"2-digit", timeZoneName:"long"}).slice(4) [e.g. for timeZone 'Europe/London': 'Greenwich Mean Time' / 'British Summer Time'] Kotlin: vTZName = oDate.format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [e.g. 'GMT' / 'BST'] PHP: Python: ___ [WARNING: 'If neither system data nor tzdata are available, all calls to ZoneInfo will raise ZoneInfoNotFoundError.'] R: Ruby: Rust: Scala: Swift: UFL: DateAdd [e.g. add days/hours/minutes/seconds/milliseconds] AutoHotkey: vDateNew := DateAdd(vDate, vNum, vUnit) [e.g. units: D/H/M/S, doesn't handle milliseconds] C++: C#: Crystal: Excel: =A1+vDays [e.g. =A1+vSec/86400] [e.g. add 12 hours: =A1+0.5] Excel VBA: oDateNew = DateAdd(vUnit, vNum, oDate) [e.g. units: yyyy/m/d/h/n/s, also: q (quarter = 3 months), ww (week), y/w (day)] [WARNING: y (day)] Go: Java: JavaScript: oDateNew = new Date(oDate.valueOf() + vMSec) [note: valueOf/getTime are interchangeable] Kotlin: oDateNew = oDate.plus(vNum.toLong(), oUnit) [e.g. oDateNew = oDate.plus(vDays.toLong(), java.time.temporal.ChronoUnit.DAYS)] [note: plus() uses 'long, TemporalUnit', with() uses 'TemporalField, long'] PHP: Python: ___ [e.g. set day: oDateNew = oDate + datetime.timedelta(days=vDays)] [note: datetime.timedelta(days=0, seconds=0, microseconds=0, milliseconds=0, minutes=0, hours=0, weeks=0)] [note: all arguments can be omitted] R: Ruby: Rust: Scala: Swift: UFL: DateAddYears [prefer the algorithm used by Excel's EDATE function] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ [can use: =EDATE(A1,vYears*12)] Excel VBA: oDateNew = DateAdd("yyyy", vYears, oDate) Go: Java: JavaScript: ___ [can use: oDate.setUTCMonth(oDate.getUTCMonth() + vYears*12)] [WARNING: adding 1 month, can increase the month by 2: 'if the current value is 31st January 2016, calling setMonth with a value of 1 will return 2nd March 2016'] Kotlin: oDateNew = oDate.plusYears(vYears.toLong()) PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateAddMonths [prefer the algorithm used by Excel's EDATE function] AutoHotkey: ___ C++: C#: Crystal: Excel: =EDATE(A1,vMonths) Excel VBA: oDateNew = DateAdd("m", vMonths, oDate) Go: Java: JavaScript: oDate.setUTCMonth(oDate.getUTCMonth() + vMonths) [WARNING: adding 1 month, can increase the month by 2: 'if the current value is 31st January 2016, calling setMonth with a value of 1 will return 2nd March 2016'] Kotlin: oDateNew = oDate.plusMonths(vMonths.toLong()) PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateAddDays [date add days] AutoHotkey: vDateNew := DateAdd(vDate, vNum, "D") C++: C#: Crystal: Excel: =A1+vDays [e.g. add 12 hours: =A1+0.5] Excel VBA: oDateNew = DateAdd("d", vDays, oDate) Go: Java: JavaScript: oDateNew = new Date(oDate.valueOf() + vDays*86400*1000) Kotlin: oDateNew = oDate.plusDays(vDays.toLong()) PHP: Python: oDateNew = oDate + datetime.timedelta(days=vDays) R: Ruby: Rust: Scala: Swift: UFL: DateAddHours [date add hours] AutoHotkey: vDateNew := DateAdd(vDate, vNum, "H") C++: C#: Crystal: Excel: =A1+vHours/24 [e.g. add 12 hours: =A1+0.5] Excel VBA: oDateNew = DateAdd("h", vHours, oDate) Go: Java: JavaScript: oDateNew = new Date(oDate.valueOf() + vHours*1440*1000) Kotlin: oDateNew = oDate.plusHours(vHours.toLong()) PHP: Python: oDateNew = oDate + datetime.timedelta(hours=vHours) R: Ruby: Rust: Scala: Swift: UFL: DateAddMinutes [date add minutes] AutoHotkey: vDateNew := DateAdd(vDate, vNum, "M") C++: C#: Crystal: Excel: =A1+vMin/1440 Excel VBA: oDateNew = DateAdd("n", vMin, oDate) Go: Java: JavaScript: oDateNew = new Date(oDate.valueOf() + vMin*60*1000) Kotlin: oDateNew = oDate.plusMinutes(vMin.toLong()) PHP: Python: oDateNew = oDate + datetime.timedelta(minutes=vMin) R: Ruby: Rust: Scala: Swift: UFL: DateAddSeconds [date add seconds] AutoHotkey: vDateNew := DateAdd(vDate, vNum, "S") C++: C#: Crystal: Excel: =A1+vSec/86400 Excel VBA: oDateNew = DateAdd("s", vSec, oDate) Go: Java: JavaScript: oDateNew = new Date(oDate.valueOf() + vSec*1000) Kotlin: oDateNew = oDate.plusSeconds(vSec.toLong()) PHP: Python: oDateNew = oDate + datetime.timedelta(seconds=vSec) R: Ruby: Rust: Scala: Swift: UFL: DateAddMilliseconds [date add milliseconds] AutoHotkey: ___ C++: C#: Crystal: Excel: =A1+vMSec/(86400*1000) Excel VBA: oDateNew = oDate + vMSec / (86400 * 1000) Go: Java: JavaScript: oDateNew = new Date(oDate.valueOf() + vMSec) Kotlin: oDateNew = oDate.plusNanos(vMSec.toLong()*1000000) [also: oDateNew = oDate.plus(vMSec.toLong(), java.time.temporal.ChronoUnit.MILLIS)] PHP: Python: oDateNew = oDate + datetime.timedelta(milliseconds=vMSec) R: Ruby: Rust: Scala: Swift: UFL: DateAddWorkdays [add working days to current date, specify a list of dates to exclude] AutoHotkey: ___ C++: C#: Crystal: Excel: =A1-INT(A1)+WORKDAY(A1,vDays) [also: MOD(A1,1)+WORKDAY(A1,vDays)] [note: or for the date only (with no time value): WORKDAY(A1,vDays)] Excel VBA: oDateNew = TimeValue(oDate) + WorksheetFunction.WorkDay(oDate, vDays) [note: or for the date only (with no time value): CDate(WorksheetFunction.WorkDay(oDate, vDays))] Go: Java: JavaScript: ___ Kotlin: ___ PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateDiff [e.g. difference in days/hours/minutes/seconds/milliseconds] AutoHotkey: vDiff := DateDiff(vDate1, vDate2, vUnit) [note: doesn't handle milliseconds] C++: C#: Crystal: Excel: =A1-B1 [note: difference in days, e.g. 0.5 is a difference of 12 hours] [WARNING: gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-03-01 minus 1900-02-28 is actually 1 day)] Excel VBA: vDiff = DateDiff("s", oDate1, oDate2) [WARNING: returns oDate2-oDate1 (oDate1-oDate2 would be more intuitive)] [note: DateDiff gives correct values for Jan 1900 and Feb 1900] Go: Java: JavaScript: vDiff = Math.floor((oDate1.valueOf()-oDate2.valueOf())/vMSec) [note: valueOf/getTime are interchangeable] Kotlin: vDiff = java.time.temporal.ChronoUnit.SECONDS.between(oDate1, oDate2) PHP: Python: vDiff = (oDate2-oDate1).total_seconds() R: Ruby: Rust: Scala: Swift: UFL: DateDiffYears [count complete years (e.g. tiebreaks: Year1>Year2 but Month1<Month2, return vYear1-vYear2-1)] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: ___ Kotlin: ___ PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateDiffMonths [count complete months (e.g. tiebreaks: Month1>Month2 but Day1<Day2, return vMonth1-vMonth2-1]) AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: ___ Kotlin: ___ PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateIsValid [validate numbers/string][note: the functions can typically handle various date/time formats] AutoHotkey: vDateIsValid := IsTime(vDate) C++: C#: Crystal: Excel: =ISNUMBER(DATEVALUE("2006-05-04 03:02:01")+TIMEVALUE("2006-05-04 03:02:01")) [WARNING: this is not 100% reliable, try changing one or more parts to '99' to see which pass/fail] Excel VBA: vDateIsValid = IsDate("2006-05-04 03:02:01") Go: Java: JavaScript: vDateIsValid = !isNaN(Date.parse("2006-05-04 03:02:01")) Kotlin: ___ [note: parse() throws if given invalid input] [e.g. oDate = java.time.LocalDateTime.parse("2006-05-04T03:02:01")] PHP: Python: ___ [note: datetime.datetime() throws if given invalid input] R: Ruby: Rust: Scala: Swift: UFL: DateEaster [or DateGetEaster][get Gregorian (or Julian) Easter for a given year][if passed a date, get the year, get the Easter for that year] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: ___ Kotlin: ___ PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateWeekdayNearest [or DateWorkdayNearest][nearest working day to a specific date (if Sat, go back 1, if Sun, go forward 1) (i.e. choose the nearest M/T/W/T/F, a more general function lets you pick which weekdays)] AutoHotkey: ___ C++: C#: Crystal: Excel: =A1+MID("2111110",WEEKDAY(A1),1)-1 [note: WEEKDAY: 1-7, Sun = 1] Excel VBA: oDate = oDate + Mid("2111110", Weekday(oDate), 1) - 1 [note: Weekday: 1-7, Sun = 1] Go: Java: JavaScript: oDate = new Date(Date.UTC(oDate.getFullYear(), oDate.getMonth(), oDate.getDate() + [1,0,0,0,0,0,-1][oDate.getDay()])) [note: getDay: 0-6, Sun = 0] Kotlin: oDate = oDate.plusDays(arrayOf<Long>(0,0,0,0,0,-1,1)[oDate.getDayOfWeek().getValue()-1]) [note: getDayOfWeek: 1-7, Mon = 1] PHP: Python: oDate = oDate + datetime.timedelta(days=[0,0,0,0,0,-1,1][oDate.weekday()]) [WARNING: if the date is not UTC, this could skip by 2 days, e.g. on the day UTC starts, e.g. the UK, 1999-03-28 00:59:00 + 1 day] [note: weekday: 0-6, Mon = 0] [also: isoweekday: 1-7, Mon = 1] R: Ruby: Rust: Scala: Swift: UFL: DateWeekdayNthInRange [nth weekday in range (e.g. 1st/2nd/3rd/4th/last Sunday of the month)] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: ___ Kotlin: ___ PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateWeekdayNthInMonth [nth weekday in month (e.g. 1st/2nd/3rd/4th Sunday of the month)] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: ___ Kotlin: oDate = java.time.LocalDate.now().withMonth(vMonth).withYear(vYear).with(java.time.temporal.TemporalAdjusters.dayOfWeekInMonth(vNum, java.time.DayOfWeek.SUNDAY)) [e.g. vNum 1 for 1st Sunday in month] [WARNING: vNum 5 and above may return days in later months] [note: for the last Sunday of the month, use vNum 0, and specify the subsequent month (i.e. 1 week before the first Sunday of next month)] PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateWeekdayLastInMonth [last weekday in month (e.g. last Sunday of the month)] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: ___ Kotlin: oDate = java.time.YearMonth.of(vYear, vMonth).atEndOfMonth().with(java.time.temporal.TemporalAdjusters.previousOrSame(java.time.DayOfWeek.SUNDAY)) PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateGetYear [date get year (e.g. a 4-digit number)] AutoHotkey: vYear := FormatTime(vDate, "yyyy") C++: C#: Crystal: Excel: =YEAR(A1) Excel VBA: vYear = Year(oDate) Go: Java: JavaScript: vYear = oDate.getUTCFullYear() [also: oDate.getFullYear()] Kotlin: vYear = oDate.getYear() PHP: Python: vYear = oDate.year R: Ruby: Rust: Scala: Swift: UFL: DateGetMonth [date get month (1-12) (some systems use 0-11)] AutoHotkey: vMonth := FormatTime(vDate, "M") [note: 'MM' for leading zeros] C++: C#: Crystal: Excel: =MONTH(A1) Excel VBA: vMonth = Month(oDate) Go: Java: JavaScript: vMonth = oDate.getUTCMonth() + 1 [also: oDate.getMonth() + 1] [WARNING: getUTCMonth/getMonth are 0-based] Kotlin: vMonth = oDate.getMonthValue() [also: getMonth()] PHP: Python: vMonth = oDate.month R: Ruby: Rust: Scala: Swift: UFL: DateGetDay [date get day of the month (1-31)] AutoHotkey: vDay := FormatTime(vDate, "d") [note: 'dd' for leading zeros] C++: C#: Crystal: Excel: =DAY(A1) Excel VBA: vDay = Day(oDate) Go: Java: JavaScript: vDay = oDate.getUTCDate() [also: oDate.getDate()] [WARNING: 'Date' not 'Day'] Kotlin: vDay = oDate.getDayOfMonth() PHP: Python: vDay = oDate.day R: Ruby: Rust: Scala: Swift: UFL: DateGetHours [date get hour (0-59)] AutoHotkey: vHour := FormatTime(vDate, "H") [note: 'HH' for leading zeros] C++: C#: Crystal: Excel: =HOUR(A1) Excel VBA: vHour = Hour(oDate) Go: Java: JavaScript: vHour = oDate.getUTCHours() [also: oDate.getHours()] Kotlin: vHour = oDate.getHour() PHP: Python: vHour = oDate.hour R: Ruby: Rust: Scala: Swift: UFL: DateGetMinutes [date get minute (0-59)] AutoHotkey: vMin := FormatTime(vDate, "m") [note: 'mm' for leading zeros] C++: C#: Crystal: Excel: =MINUTE(A1) Excel VBA: vMin = Minute(oDate) Go: Java: JavaScript: vMin = oDate.getUTCMinutes() [also: oDate.getMinutes()] Kotlin: vMin = oDate.getMinute() PHP: Python: vMin = oDate.minute R: Ruby: Rust: Scala: Swift: UFL: DateGetSeconds [date get second (0-59)] AutoHotkey: vSec := FormatTime(vDate, "s") [note: 'ss' for leading zeros] C++: C#: Crystal: Excel: =SECOND(A1) Excel VBA: vSec = Second(oDate) Go: Java: JavaScript: vSec = oDate.getUTCSeconds() [also: oDate.getSeconds()] Kotlin: vSec = oDate.getSecond() PHP: Python: vSec = oDate.second R: Ruby: Rust: Scala: Swift: UFL: DateGetMilliseconds [date get milliseconds (0-999)] AutoHotkey: ___ C++: C#: Crystal: Excel: =RIGHT(TEXT(A1,"s.000"),3) [also: MOD(ROUND((A1-INT(A1))*86400000,0),1000)] Excel VBA: vMSec = Round((oDate - Int(oDate)) * 86400000) Mod 1000 Go: Java: JavaScript: vMSec = oDate.getUTCMilliseconds() [also: oDate.getMilliseconds()] Kotlin: vMSec = oDate.getNano() / 1000000 [note: integer division (truncated division)] PHP: Python: vMSec = oDate.microsecond // 1000 R: Ruby: Rust: Scala: Swift: UFL: DateGetWDay [or DateGetWeekday][typically 0-6 or 1-7] AutoHotkey: vWDay := FormatTime(vDate, "WDay") [note: 1-7, Sun = 1] C++: C#: Crystal: Excel: =WEEKDAY(A1) [note: 1-7, Sun = 1 (but can use 2nd param to use other weekday systems)] [WARNING: WEEKDAY gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-01-01 was actually a Monday)] Excel VBA: vWDay = Weekday(oDate) [note: 1-7, Sun = 1 (but can use 2nd param to use other weekday systems)] [note: Weekday and WorksheetFunction.Weekday give correct values for Jan 1900 and Feb 1900] Go: Java: JavaScript: vWDay = oDate.getUTCDay() [also: oDate.getDay()] [WARNING: 'Day' not 'WDay'/'Weekday'] [note: 0-6, Sun = 0] Kotlin: vWDay = oDate.getDayOfWeek().getValue() [note: 1-7, Mon = 1] PHP: Python: vWDay = oDate.isoweekday() [note: 1-7, Mon = 1] [also: oDate.weekday() (0-6, Mon = 0)] R: Ruby: Rust: Scala: Swift: UFL: DateGetWeek [get week number (1-54) (week containing January 1st is numbered week 1, week starts on Monday)] AutoHotkey: ___ C++: C#: Crystal: Excel: =WEEKNUM(A1,2) Excel VBA: vWeek = Format(oDate, "ww", vbMonday) Go: Java: JavaScript: ___ Kotlin: vWeek = oDate.get(java.time.temporal.WeekFields.of(java.time.DayOfWeek.MONDAY, 1).weekOfYear()) PHP: Python: vWeek = int(oDate.strftime("%W")) + (1 if datetime.date(oDate.year,1,1).weekday() else 0) R: Ruby: Rust: Scala: Swift: UFL: DateGetISOYearWeek [get ISO week number (and year) (e.g. 200618) (ISO week: 1-53) (week containing the first Thursday of the year is numbered week 1, week starts on Monday)] [WARNING: the first ISO week of year n may have year n-1] [WARNING: the last ISO week of year n may have year n+1] AutoHotkey: vIsoYWeek := FormatTime(vDate, "YWeek") [note: number of the form 'yyyyww'] C++: C#: Crystal: Excel: ___ [can use (without year): ISOWEEKNUM(A1)] [also (without year): WEEKNUM(A1,21)] [note: ISOWEEKNUM added in Excel 2013, WEEKNUM '21' option added in Excel 2010] [also (year): YEAR(A1-WEEKDAY(A1-1)+4)] [can use (without year): =INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)] Excel VBA: ___ [can use (without year): vIsoWeek = WorksheetFunction.IsoWeekNum(oDate)] [WARNING: returns the ISO week number 1-53, but not the ISO year] [WARNING: Format/DatePart with 'ww'/vbMonday/vbFirstFourDays sometimes return the wrong week: 'the last Monday in some calendar years is returned as week 53 when it should be week 1'] [also (year): vIsoYear = Year(CDbl(oDate) - Weekday(oDate, 2) + 4)] Go: Java: JavaScript: ___ Kotlin: vIsoYWeek = oDate.get(java.time.temporal.IsoFields.WEEK_BASED_YEAR)*100 + oDate.get(java.time.temporal.IsoFields.WEEK_OF_WEEK_BASED_YEAR) PHP: Python: vIsoYWeek = oDate.strftime("%G%V") [also: oDate.isocalendar(): contains year/week(/weekday) values] R: Ruby: Rust: Scala: Swift: UFL: DateGetMonthName [e.g. the long/short forms of Jan/Feb/Mar/Apr/May/Jun/Jul/Aug/Sep/Oct/Nov/Dec] AutoHotkey: vMonthName := FormatTime(vDate, "MMMM") [note: 'MMM' for short version] C++: C#: Crystal: Excel: =TEXT(A1,"mmmm") [note: 'mmm' for short version] Excel VBA: vMonthName = Format(oDate, "mmmm") [note: 'mmm' for short version] [also: MonthName(DatePart("m", oDate))] Go: Java: JavaScript: vMonthName = oDate.toLocaleString("en", {month:"long"}) [note: 'short' for short version] Kotlin: vMonthName = oDate.getMonth().toString() PHP: Python: vMonthName = oDate.strftime("%B") [note: '%b' for short version] R: Ruby: Rust: Scala: Swift: UFL: DateGetWDayName [or DateGetWeekdayName][e.g. the long/short forms of Mon/Tue/Wed/Thu/Fri/Sat/Sun] AutoHotkey: vWDayName := FormatTime(vDate, "dddd") [note: 'ddd' for short version] C++: C#: Crystal: Excel: =TEXT(A1,"dddd") [note: 'ddd' for short version] Excel VBA: vWDayName = Format(oDate, "dddd") [note: 'ddd' for short version] [can use: WeekdayName(Weekday(oDate), , vbSunday)] [WARNING: WeekdayName default: 1-7, Mon = 1 (differs from Weekday function), can use: vbSunday: 1-7, Sun = 1 (matches Weekday function)] Go: Java: JavaScript: vWDayName = oDate.toLocaleString("en", {weekday:"long"}) [note: 'short' for short version] Kotlin: vWDayName = oDate.getDayOfWeek().toString() PHP: Python: vWDayName = oDate.strftime("%A") [note: '%a' for short version] R: Ruby: Rust: Scala: Swift: UFL: DateGetHour12 [or DateGetHourAmPm][i.e. 1-12] AutoHotkey: vHour12 := FormatTime(vDate, "h") [note: 'hh' for leading zeros] C++: C#: Crystal: Excel: =MOD(HOUR(A1)+11,12)+1 Excel VBA: vHour12 = ((Hour(oDate) + 11) Mod 12) + 1 Go: Java: JavaScript: vHour12 = oDate.getUTCHours()%12||12 [also: oDate.getHours()%12||12] Kotlin: vHour12 = oDate.format(java.time.format.DateTimeFormatter.ofPattern("h")) [also (leading zero): 'hh'] PHP: Python: vHour12 = oDate.strftime("%I") R: Ruby: Rust: Scala: Swift: UFL: DateGetAmPm [e.g. 'AM'/'PM'] AutoHotkey: vAmPm := FormatTime(vDate, "tt") [e.g. AM/PM] [note: 't' for short version (e.g. A/P)] C++: C#: Crystal: Excel: =TEXT(A1,"AM/PM") Excel VBA: vAmPm = Format(oDate, "AM/PM") Go: Java: JavaScript: vAmPm = oDate.getUTCHours()<12?"AM":"PM" [also: oDate.getHours()<12?"AM":"PM"] Kotlin: vAmPm = oDate.format(java.time.format.DateTimeFormatter.ofPattern("a")) PHP: Python: vAmPm = oDate.strftime("%p") [WARNING: confusingly: %p returns upper case, %P returns lower case] R: Ruby: Rust: Scala: Swift: UFL: DateGetDaySuffix [i.e. st/nd/rd/th (e.g. 1st/2nd/3rd/4th) (ordinal suffixes)][the algorithm aims to be short, readable, and handle all positive integers and 0 (pass abs(vNum) to handle all integers)] AutoHotkey: vSfx := Abs(Mod(vNum,100)-12)<=1?"th":["st","nd","rd","th"][Min(Mod(vNum+9,10),3)+1] C++: C#: Crystal: Excel: =IF(ABS(MOD(A1,100)-12)<=1,"th",MID("stndrdth",MIN(MOD(A1+9,10),3)*2+1,2)) Excel VBA: vSfx = IIf(Abs((vNum Mod 100) - 12) <= 1, "th", Mid("stndrdth", WorksheetFunction.Min(((vNum + 9) Mod 10), 3) * 2 + 1, 2)) Go: Java: JavaScript: vSfx = Math.abs(vNum%100-12)<=1?"th":["st","nd","rd"][vNum%10-1]||"th" Kotlin: vSfx = if (Math.abs(vNum%100-12)<=1) "th" else arrayOf("st","nd","rd","th")[Math.min((vNum+9)%10,3)] PHP: Python: vSfx = "th" if (abs(vNum%100-12)<=1) else ["st","nd","rd","th"][min((vNum+9)%10,3)] R: Ruby: Rust: Scala: Swift: UFL: DateGetYDay [i.e. 1-366] AutoHotkey: vYDay := FormatTime(vDate, "YDay") [note: 'YDay0' for leading zeros] C++: C#: Crystal: Excel: =A1-DATE(YEAR(A1),1,0) Excel VBA: vYDay = DatePart("y", oDate) [also: Format(oDate, "y")] [WARNING: in Excel sheet functions, 'y' returns the 2-digit year] Go: Java: JavaScript: vYDay = (Date.UTC(oDate.getFullYear(), oDate.getMonth(), oDate.getDate()) - Date.UTC(oDate.getFullYear(), 0, 0)) / 86400000 Kotlin: vYDay = oDate.getDayOfYear() PHP: Python: vYDay = oDate.timetuple().tm_yday [also: oDate.strftime("%j")] R: Ruby: Rust: Scala: Swift: UFL: (DateGetEra) [or DateGetBcAd][e.g. BC/AD] AutoHotkey: vEra := FormatTime(vDate, "gg") [e.g. 'A.D.'] C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: vEra = oDate.toLocaleString("en", {year:"2-digit", era:"short"}).slice(3) [e.g. 'AD'] Kotlin: ___ [can use (CE as string e.g. 'AD', can replace CE with BCE e.g. 'BC'): java.time.chrono.IsoEra.CE.getDisplayName(java.time.format.TextStyle.SHORT, java.util.Locale.getDefault()))] PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateGetMonthEnd [get last day of month] AutoHotkey: ___ [can use: DateAdd and SubStr: get first day of current month, add 31 days to fall inside new month, get the day of the new month, subtract that many days] C++: C#: Crystal: Excel: =EOMONTH(A1,0) Excel VBA: oDateNew = CDate(WorksheetFunction.EoMonth(oDate, 0)) [also: DateSerial(Year(oDate), Month(oDate) + 1, 0) (note: '0th' day of month, goes back 1 day)] Go: Java: JavaScript: oDateNew = new Date(oDate.getFullYear(), oDate.getMonth()+1, 0) Kotlin: oDateNew = oDate.with(java.time.temporal.TemporalAdjusters.lastDayOfMonth()) PHP: Python: oDateNew = calendar.monthrange(oDate.year, oDate.month)[1] R: Ruby: Rust: Scala: Swift: UFL: DateGetQuarter [i.e. Jan/Feb/Mar to 1, Apr/May/Jun to 2, Jul/Aug/Sep to 3, Oct/Nov/Dec to 4] AutoHotkey: vQtr := (FormatTime(vDate, "M")+2) // 3 C++: C#: Crystal: Excel: =INT((MONTH(A1)+2)/3) Excel VBA: vQtr = DatePart("q", oDate) [also: Format(oDate, "q")] [WARNING: Excel sheet functions lack the 'q' option] Go: Java: JavaScript: vQtr = Math.floor(oDate.getUTCMonth()/3+1) [also: Math.floor(oDate.getMonth()/3+1)] [WARNING: getUTCMonth/getMonth are 0-based] Kotlin: vQtr = oDate.get(java.time.temporal.IsoFields.QUARTER_OF_YEAR) PHP: Python: vQtr = (oDate.month+2) // 3 R: Ruby: Rust: Scala: Swift: UFL: DateGetWeekdayCount [count weekdays in date range, specify a list of dates to exclude] AutoHotkey: ___ C++: C#: Crystal: Excel: =NETWORKDAYS(A1,B1) Excel VBA: vCountDays = WorksheetFunction.NetworkDays(oDate1, oDate2) Go: Java: JavaScript: ___ Kotlin: ___ PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateIsLeapYear [for a date, is the year a leap year] AutoHotkey: vIsLeapYear := !Mod(vYear, 4) && (Mod(vYear, 100) || !Mod(vYear, 400)) [beforehand: vYear := FormatTime(vDate, "yyyy")] C++: C#: Crystal: Excel: =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),True,False) [note: where A1 contains a year] [note: date to year: YEAR(A1)] [note: based on code from learn.microsoft.com] Excel VBA: vIsLeapYear = (Month(DateSerial(vYear, 2, 29)) = 2) [beforehand: vYear = Year(oDate)] Go: Java: JavaScript: vIsLeapYear = (new Date(vYear, 2-1, 29).getDate() == 29) [beforehand: vYear = oDate.getFullYear()] Kotlin: vIsLeapYear = java.time.Year.isLeap(vYear) [also: vIsLeapYear = (java.time.Year.of(vYear).length() == 366)] [beforehand: vYear = oDate.getYear()] PHP: Python: vIsLeapYear = calendar.isleap(vYear) [beforehand: vYear = oDate.year] R: Ruby: Rust: Scala: Swift: UFL: DateIsDSTLocal [for a given time, was DST in force at that time, using the local time zone] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: vIsDST = (oDate.getTimezoneOffset() < Math.max(new Date(oDate.getFullYear(), 6, 1).getTimezoneOffset(), new Date(oDate.getFullYear(), 0, 1).getTimezoneOffset())) [WARNING: it happens to work for almost every time zone, but is not guaranteed to work] Kotlin: vIsDST = oTZ.getRules().isDaylightSavings(oDate.toInstant()) [beforehand: oTZ = java.time.ZoneId.systemDefault()] PHP: Python: vIsDST = (oDate.astimezone().utcoffset().total_seconds() != -time.timezone) [note: if offset is not the standard (non-DST) offset, it must be DST offset] R: Ruby: Rust: Scala: Swift: UFL: DateIsDST [for a given time, was DST in force at that time, using a specific time zone] AutoHotkey: ___ C++: C#: Crystal: Excel: ___ Excel VBA: ___ Go: Java: JavaScript: ___ [note: toLocaleString() and Date.parse() can be used on a sample of dates (e.g. 1 date per month), to determine the DST/non-DST UTC offsets for a time zone, and that can be compared with the current UTC offset (see the 'DateGetTZOffsetMin' example)] Kotlin: vIsDST = oTZ.getRules().isDaylightSavings(oDate.toInstant()) PHP: Python: ___ R: Ruby: Rust: Scala: Swift: UFL: DateSetPart [or DateSetValue][e.g. set Y/M/D/H/M/S] AutoHotkey: ___ [can use: e.g. set day: vDate := FormatTime(vDate, Format("yyyyMM{:02}HHmmss", vDay))] [also (set all parts): vDate := Format("{:04}{:02}{:02}{:02}{:02}{:02}", vYear, vMonth, vDay, vHour, vMin, vSec)] [also (overwrite part of a date string): StrPut(vNumStr, StrPtr(vDateStr)+vOffset, StrLen(vNumStr))] C++: C#: Crystal: Excel: ___ [can use: e.g. set day: =DATE(YEAR(A1),MONTH(A1),vDay)+MOD(A1,1)] [also (set all parts): =DATE(vYear,vMonth,vDay)+TIME(vHour,vMin,vSec)+vMSec/(86400*1000)] [also: INT(A1) gets the date value, A1-INT(A1) or MOD(A1,1) gets the time value] Excel VBA: ___ [can use: e.g. set day: oDateNew = DateSerial(Year(oDate), Month(oDate), vDay) + (oDate - Int(oDate))] [also (set all parts): oDate = DateSerial(vYear, vMonth, vDay) + TimeSerial(vHour, vMin, vSec) + vMSec / (86400 * 1000)] [also: Int(oDate) gets the date value, oDate-Int(oDate) gets the time value] [WARNING: unlike the Excel sheet function MOD, Mod in VBA rounds floats to integers] Go: Java: JavaScript: ___ [can use: e.g. set day (of month): oDate.setUTCDate(vDay)] [e.g. set day (of month): oDate.setDate(vDay)] Kotlin: oDateNew = oDate.with(oField, vNum) [e.g. oDate = oDate.with(java.time.temporal.ChronoField.DAY_OF_MONTH, vDay.toLong())] [note: plus() uses 'long, TemporalUnit', with() uses 'TemporalField, long'] PHP: Python: ___ [e.g. set day: oDate.replace(day=vDay)] [note: datetime.replace(year=self.year, month=self.month, day=self.day, hour=self.hour, minute=self.minute, second=self.second, microsecond=self.microsecond, tzinfo=self.tzinfo, *, fold=0)] [note: all arguments can be omitted] [note: 'Parameters after “*” or “*identifier” are keyword-only parameters and may only be passed by keyword arguments.'] R: Ruby: Rust: Scala: Swift: UFL: DateSetYear [date set year (e.g. a 4-digit number)] AutoHotkey: vDate := FormatTime(vDate, Format("{:04}MMddHHmmss", vYear)) C++: C#: Crystal: Excel: =DATE(vYear,MONTH(A1),DAY(A1))+MOD(A1,1) Excel VBA: oDateNew = DateSerial(vYear, Month(oDate), Day(oDate)) + (oDate - Int(oDate)) Go: Java: JavaScript: oDate.setUTCFullYear(vYear) [also: oDate.setFullYear(vYear)] Kotlin: oDateNew = oDate.withYear(vYear) PHP: Python: oDateNew = oDate.replace(year=vYear) R: Ruby: Rust: Scala: Swift: UFL: DateSetMonth [date set month (1-12) (some systems use 0-11)] AutoHotkey: vDate := FormatTime(vDate, Format("yyyy{:02}ddHHmmss", vMonth)) C++: C#: Crystal: Excel: =DATE(YEAR(A1),vMonth,DAY(A1))+MOD(A1,1) Excel VBA: oDateNew = DateSerial(Year(oDate), vMonth, Day(oDate)) + (oDate - Int(oDate)) Go: Java: JavaScript: oDate.setUTCMonth(vMonth) [also: oDate.setMonth(vMonth)] Kotlin: oDateNew = oDate.withMonth(vMonth) PHP: Python: oDateNew = oDate.replace(month=vMonth) R: Ruby: Rust: Scala: Swift: UFL: DateSetDay [date set day of the month (1-31)] AutoHotkey: vDate := FormatTime(vDate, Format("yyyyMM{:02}HHmmss", vDay)) C++: C#: Crystal: Excel: =DATE(YEAR(A1),MONTH(A1),vDay)+MOD(A1,1) Excel VBA: oDateNew = DateSerial(Year(oDate), Month(oDate), vDay) + (oDate - Int(oDate)) Go: Java: JavaScript: oDate.setUTCDate(vDay) [also: oDate.setDate(vDay)] Kotlin: oDateNew = oDate.withDayOfMonth(vDay) PHP: Python: oDateNew = oDate.replace(day=vDay) R: Ruby: Rust: Scala: Swift: UFL: DateSetHours [date set hour (0-59)] AutoHotkey: vDate := FormatTime(vDate, Format("yyyyMMdd{:02}mmss", vHour)) C++: C#: Crystal: Excel: =TIME(vHour,MINUTE(A1),SECOND(A1))+INT(A1) Excel VBA: oDateNew = TimeSerial(vHour, Minute(oDate), Second(oDate)) + Int(oDate) Go: Java: JavaScript: oDate.setUTCHours(vHour) [also: oDate.setHours(vHour)] Kotlin: oDateNew = oDate.withHour(vHour) PHP: Python: oDateNew = oDate.replace(hour=vHour) R: Ruby: Rust: Scala: Swift: UFL: DateSetMinutes [date set minute (0-59)] AutoHotkey: vDate := FormatTime(vDate, Format("yyyyMMddHH{:02}ss", vMin)) C++: C#: Crystal: Excel: =TIME(HOUR(A1),vMin,SECOND(A1))+INT(A1) Excel VBA: oDateNew = TimeSerial(Hour(oDate), vMin, Second(oDate)) + Int(oDate) Go: Java: JavaScript: oDate.setUTCMinutes(vMin) [also: oDate.setMinutes(vMin)] Kotlin: oDateNew = oDate.withMinute(vMin) PHP: Python: oDateNew = oDate.replace(minute=vMin) R: Ruby: Rust: Scala: Swift: UFL: DateSetSeconds [date set second (0-59)] AutoHotkey: vDate := FormatTime(vDate, Format("yyyyMMddHHmm{:02}", vSec)) C++: C#: Crystal: Excel: =TIME(HOUR(A1),MINUTE(A1),vSec)+INT(A1) Excel VBA: oDateNew = TimeSerial(Hour(oDate), Minute(oDate), vSec) + Int(oDate) Go: Java: JavaScript: oDate.setUTCSeconds(vSec) [also: oDate.setSeconds(vSec)] Kotlin: oDateNew = oDate.withSecond(vSec) PHP: Python: oDateNew = oDate.replace(second=vSec) R: Ruby: Rust: Scala: Swift: UFL: DateSetMilliseconds [date set milliseconds (0-999)] AutoHotkey: ___ C++: C#: Crystal: Excel: =TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+INT(A1)+vMSec/(86400*1000) Excel VBA: oDateNew = TimeSerial(Hour(oDate), Minute(oDate), Second(oDate)) + Int(oDate) + vMSec / (86400 * 1000) Go: Java: JavaScript: oDate.setUTCMilliseconds(vMSec) [also: oDate.setMilliseconds(vMSec)] Kotlin: oDateNew = oDate.withNano(vMSec*1000000+(oDate.getNano()%1000000)) [also: oDateNew = oDate.with(java.time.temporal.ChronoField.MILLI_OF_SECOND, vMSec.toLong())] PHP: Python: oDateNew = oDate.replace(microsecond=vMSec*1000+(oDate.microsecond%1000)) R: Ruby: Rust: Scala: Swift: UFL: DateToUnix [Unix dates: seconds since 1970][note: sometimes Unix dates are stated as milliseconds] AutoHotkey: vUnixSec := DateDiff(vDateUTC, 1970, "Seconds") C++: C#: Crystal: Excel: =INT((A1-25569)*86400) [WARNING: the calculation uses local, not UTC time] [note: 25569 is equivalent to 1970-01-01] Excel VBA: vUnixSec = Int((oDate - 25569) * 86400) [WARNING: the calculation uses local, not UTC time] [note: 25569 is equivalent to 1970-01-01] Go: Java: JavaScript: vUnixSec = Math.floor(oDate.getTime()/1000) Kotlin: vUnixSec = oDate.toEpochSecond(java.time.ZoneOffset.UTC) [also: oOffset = oTZ.getRules().getOffset(oInstant)] PHP: Python: vUnixSec = (oDate.astimezone(datetime.timezone.utc)-datetime.datetime.fromtimestamp(0, datetime.timezone.utc)).total_seconds() [also: vUnixSec = (oDate.astimezone(datetime.timezone.utc)-datetime.datetime(1970, 1, 1, tzinfo=datetime.timezone.utc)).total_seconds()] R: Ruby: Rust: Scala: Swift: UFL: DateFromUnix [or UnixToDate][Unix dates: seconds since 1970][note: sometimes Unix dates are stated as milliseconds] AutoHotkey: vDate := DateAdd(1970, vUnixSec, "Seconds") C++: C#: Crystal: Excel: =25569+(A1/86400) [WARNING: the calculation uses local, not UTC time] [note: 25569 is equivalent to 1970-01-01] Excel VBA: oDate = DateSerial(1970, 1, 1) + (vUnixSec / 86400) [WARNING: the calculation uses local, not UTC time] Go: Java: JavaScript: oDate = new Date(vUnixSec*1000) Kotlin: oDate = java.time.LocalDateTime.ofEpochSecond(vUnixSec, 0, java.time.ZoneOffset.UTC) [also: oOffset = oTZ.getRules().getOffset(oInstant)] PHP: Python: oDate = datetime.datetime.fromtimestamp(vUnixSec) R: Ruby: Rust: Scala: Swift:

Time Zones

If using an online programming language compiler/interpreter, it may report 'UTC', and not your local time zone. It can be best to think of dates as a UTC time and a location. To display a date in a time zone, you query: for this UTC time, at this location, what was the time zone offset, and was DST (daylight saving time) in force. Time zones can be thought of as having 4 names. A location, a general name, a non-DST name, a DST name. E.g. Europe/London, 'British Time' (BT), Greenwich Mean Time (GMT) (UTC), British Summer Time (BST) (UTC+1). E.g. America/New_York, Eastern Time (ET), Eastern Standard Time (EST) (UTC-5), Eastern Daylight Time (EDT) (UTC-4). E.g. America/Chicago, Central Time (CT), Central Standard Time (CST) (UTC-6), Central Daylight Time (CDT) (UTC-5). E.g. America/Denver, Mountain Time (MT), Mountain Standard Time (MST) (UTC-7), Mountain Daylight Time (MDT) (UTC-6). E.g. America/Los_Angeles, Pacific Time (PT), Pacific Standard Time (PST) (UTC-8), Pacific Daylight Time (PDT) (UTC-7). Phoenix, Arizona uses Mountain Standard Time (MST) (UTC-7) all year round, it doesn't observe DST. The UK uses Greenwich Mean Time (GMT) (UTC) for 5 months of the year (late Oct-late Mar). The UK uses British Summer Time (BST) (UTC+1) for 7 months of the year (late Mar-late Oct). In US time zones, 'S' stands for 'Standard', i.e. non-DST. In UK time zones, 'S' stands for 'Summer', i.e. DST. When using time zone functionality... Some time zone information may be specific to the operating system (e.g. Windows-specific / Linux-specific).

Displaying local dates in 'yyyy-MM-dd' form

Using toLocaleString() to format dates as 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-ddTHH:mm:ss.000Z' It can be useful to format a date like so: 'yyyy-MM-dd HH:mm:ss'. E.g. '2006-05-04 03:02:01'. Such dates are easily sorted, and are well understood internationally. The following examples, ISO and locale, give values where the digits are in the same order: > ISO string: These give a date in the UTC time zone: E.g. new Date().toISOString() E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toISOString() E.g. '2006-05-04T03:02:01.000Z'. > locale string: These give a date in the specified time zone (or the local time zone if 'timezone' is omitted): E.g. new Date().toLocaleString("en-CA-u-hc-h23", {weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-CA-u-hc-h23", {weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-CA-u-hc-h23", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. 'Thu, 2006-05-04, 04:02:01 GMT+01:00'. Note: there is no guarantee that the format won't change in future. E.g. the Canadian format was changed, then changed back. > various locale strings for comparison: E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-GB", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-US-u-hc-h23", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-CA-u-hc-h23", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-US", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-CA", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. 'Thu, 04/05/2006, 04:02:01 GMT+01:00'. 'en-GB' (using dd/MM/yyyy). E.g. 'Thu, 05/04/2006, 04:02:01 GMT+01:00'. 'en-US-u-hc-h23' (using dd/MM/yyyy). E.g. 'Thu, 2006-05-04, 04:02:01 GMT+01:00'. 'en-CA-u-hc-h23'. E.g. 'Thu, 05/04/2006, 04:02:01 AM GMT+01:00'. 'en-US' (using MM/dd/yyyy). E.g. 'Thu, 2006-05-04, 04:02:01 a.m. GMT+01:00'. 'en-CA'. > Background: 'en', 'en-CA' and 'en-CA-u-hc-h23' are examples of BCP 47 language tags. I was looking for a 'default' BCP 47 tag, to use with toLocaleString(), that would convert dates to ISO format, and perhaps a variant that used English weekday/month names. Unfortunately, it appears that none currently exists (Dec 2023). The closest I could find were: af, lt, sv, en-CA. Afrikaans, Lithuanian, Swedish, and English (Canada) respectively. Unfortunately, although en-CA used ISO dates, it used 12-hour time, however, this could be fixed by appending '-u-hc-h23', to give: 'en-CA-u-hc-h23'. Note: 'hc' stands for hour cycle. Note: 'h23' indicates hours 0-23. Note: 'h23', not 'h24'. > Links: Intl.DateTimeFormat() constructor - JavaScript | MDN Intl.Locale - JavaScript | MDN Intl - JavaScript | MDN Intl.Locale.prototype.hourCycle - JavaScript | MDN IETF language tag - Wikipedia

Month/Year Calculations: Add/Subtract/Difference

Preferred algorithm for adding/subtracting years/months My preferred algorithm for adding/subtracting years/months, works as follows: Adding/subtracting months, always round down if the date doesn't exist: E.g. Oct 31 + 1 month = 'Nov 31', which doesn't exist, so round down to Nov 30. E.g. Dec 31 - 1 month = 'Nov 31', which doesn't exist, so round down to Nov 30. Adding/subtracting years, always round down if the date doesn't exist: E.g. Feb 29 + 1 year = 'Feb 29', which doesn't exist, so round down to Feb 28. E.g. Feb 29 - 1 year = 'Feb 29', which doesn't exist, so round down to Feb 28. One benefit of this approach is that you know which month you will end up in, it's simple: If you are in January, and you 'add one month', you will end up in February. If you are in February, and you 'subtract one month', you will end up in January. This algorithm is equivalent to the following: ● Microsoft Excel's EDATE function. ● Microsoft Excel VBA's DateAdd function (with 'm'/'yyyy' options). ● Microsoft Windows's SysDateTimePick32 controls. This algorithm differs from: ● JavaScript's setUTCMonth/setMonth methods. Preferred algorithm for counting months between 2 dates My preferred algorithm for counting the complete months between 2 dates, works as follows: E.g. 2006-05-04 versus 2006-03-14. At first glance, 2006-05 v. 2006-03, the difference is 2 months. Now check the rest of the date (D/H/M/S/milliseconds). The days of the month: 4 v. 14, 4 < 14, so subtract 1 from the total month count. The result is a difference of 1 month. I.e. 14 March to 4 May, is 2 months minus 10 days, hence 1 complete month. E.g. 2006-05-14 versus 2006-03-04. At first glance, 2006-05 v. 2006-03, the difference is 2 months. Now check the rest of the date (D/H/M/S/milliseconds). The days of the month: 4 v. 14, 14 > 4, so leave the month count unchanged. The result is a difference of 2 months. I.e. 4 March to 14 May, is 2 months and 10 days, hence 2 complete months. In general: Compare the year and month values to get a month count. Then compare the rest of the date, and either subtract a month, or leave the value unchanged.

Weekdays

Sometimes 'weekday' means Mon/Tue/Wed/Thu/Fri but not Sat/Sun. Sometimes 'weekday' means any day of the week (Mon/Tue/Wed/Thu/Fri/Sat/Sun).

AM/PM

24-hour time, translates to 12-hour time like so: 0 -> 12 AM 1-11 -> 1-11 AM 12 -> 12 PM 13-23 -> 1-11 PM Note: 1-11 *AM*, is followed by 12 *PM*. Note: 1-11 *PM*, is followed by 12 *AM*.

Leap Years

Using the Gregorian calendar: To determine whether a year is a leap year or not: 4Y AND (100N OR 400Y): Where 4Y means divisible by 4. Where 100N means not divisible by 100. Where 400Y means divisible by 400. To be a leap year, it must pass 4Y, and pass either 100N or 400Y. Some example years: 1900 4Y 100Y 400N: passes 4Y, fails 100N, fails 400Y, NOT leap. 2000 4Y 100Y 400Y: passes 4Y, fails 100N, but passes 400Y, IS leap. 2001 4N 100N 400N: fails 4Y, NOT leap. 2004 4Y 100Y 400N: passes 4Y, passes 100N, IS leap. Century examples: Are leap: 1600, 2000, 2400. Aren't leap: 1700, 1800, 1900, 2100, 2200, 2300.

Combining Date Parts

Combining dates and milliseconds Beware when combining bits of dates that were obtained at different times. Problem: Let's say the time is noon. 12:00:00. At 12:00:00.950, you get the time (12:00:00). At 12:00:00.970, you get the milliseconds (970). That gives: 12:00:00.970. = At 12:00:00.990, you get the time (12:00:00). At 12:00:01.010, you get the milliseconds (010). That gives: 12:00:00.010. You now have 2 'dates' that are out of sequence. Solution: Let's say the time is noon. 12:00:00. At 12:00:00.950, you get the time (12:00:00). At 12:00:00.970, you get the milliseconds (970). At 12:00:00.990, you get the time (12:00:00). That gives: 12:00:00.970. The time values matched (both 12:00:00), so we know the combined time is reliable. = At 12:00:01.010, you get the milliseconds (010). At 12:00:01.030, you get the time (12:00:01). The current time (12:00:01) and the previous time (12:00:00) don't match, so try again: = At 12:00:01.050, you get the milliseconds (050). At 12:00:01.070, you get the time (12:00:01). That gives: 12:00:01.050. The time values matched (both 12:00:01), so we know the combined time is reliable.

Date Systems

Note: 1 second = 1000 milliseconds 1 millisecond = 1000 microseconds 1 microsecond = 1000 nanoseconds Some common date systems: Unix (seconds since 1970-01-01 00:00:00) Excel (days since 1899-12-30 00:00:00) FILETIME (100-nanosecond intervals since 1601-01-01 00:00:00) ticks (.NET) (100-nanosecond intervals since 0001-01-01 00:00:00) Listed below, the equivalents of various years in different date systems: Unix (seconds since 1970-01-01 00:00:00): note: sometimes *milliseconds* since 1970 is used, in which case the number is longer by 3 digits

1970	0
1980	315532800
1990	631152000
2000	946684800
2010	1262304000
2020	1577836800
2030	1893456000
2040	2208988800
2050	2524608000
2060	2840140800
2070	3155760000
2080	3471292800
2090	3786912000
2100	4102444800

Excel (days since 1899-12-30 00:00:00): note: Excel incorrectly assumes that the year 1900 is a leap year - Microsoft 365 Apps | Microsoft Learn Microsoft Excel incorrectly assumes that the year 1900 is a leap year. This article explains why the year 1900 is treated as a leap year, and outlines the behaviors that may occur if this specific issue is corrected. ... When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3. ... The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.

1899-12-30	-1 (should be 0)
1899-12-31	0 (should be 1)
1900-01-01	1 (should be 2)
1900-02-28	59 (should be 60)
1900-02-29	60 (non-existent day)
1900-03-01	61
1901-01-01	367
1910	3654
1920	7306
1930	10959
1940	14611
1950	18264
1960	21916
1970	25569
1980	29221
1990	32874
2000	36526
2010	40179
2020	43831
2030	47484
2040	51136
2050	54789
2060	58441
2070	62094
2080	65746
2090	69399
2100	73051

FILETIME (100-nanosecond intervals since 1601-01-01 00:00:00):

1601	0
1700	31241376000000000
1800	62798112000000000
1900	94354848000000000
1910	97510176000000000
1920	100665504000000000
1930	103821696000000000
1940	106977024000000000
1950	110133216000000000
1960	113288544000000000
1970	116444736000000000
1980	119600064000000000
1990	122756256000000000
2000	125911584000000000
2010	129067776000000000
2020	132223104000000000
2030	135379296000000000
2040	138534624000000000
2050	141690816000000000
2060	144846144000000000
2070	148002336000000000
2080	151157664000000000
2090	154313856000000000
2100	157469184000000000

ticks (.NET) (100-nanosecond intervals since 0001-01-01 00:00:00):

1	0
500	157469184000000000
1000	315253728000000000
1500	473038272000000000
1600	504595008000000000
1601	504911232000000000 [FILETIME + 504911232000000000 = ticks]
1700	536152608000000000
1800	567709344000000000
1900	599266080000000000
1910	602421408000000000
1920	605576736000000000
1930	608732928000000000
1940	611888256000000000
1950	615044448000000000
1960	618199776000000000
1970	621355968000000000
1980	624511296000000000
1990	627667488000000000
2000	630822816000000000
2010	633979008000000000
2020	637134336000000000
2030	640290528000000000
2040	643445856000000000
2050	646602048000000000
2060	649757376000000000
2070	652913568000000000
2080	656068896000000000
2090	659225088000000000
2100	662380416000000000

Current Year / Birth Year / Age

Year/Birth Year To Age If a person was born in the year Y1, then in the year Y2... They are age Y2-Y1-1 before their birthday. They are age Y2-Y1 after their birthday. During a person's birth year... They are age -1 before their birthday. They are age 0 after their birthday. Age/Year To Birth Year If a person is N years old in the year Y... If they haven't had their birthday yet that year, they were born in the year Y-N-1. If they've had their birthday that year, they were born in the year Y-N. Age/Birth Year To Year If a person was born in the year Y, and is N years old... They will be N years old at the end of the year Y+N. They will be N years old at the start of the year Y+N+1.

Android / Kotlin / java.time

If an Android app written in Kotlin uses the java.time class... Some older mobile phones cannot run the app. A workaround is to use code lines such as: '@android.annotation.TargetApi(26)' These effectively 'comment out' functions when used on older mobile phones. See also re. using Java data classes in Kotlin: android - Creating Date objects in Kotlin for API level less than or equal to 16 - Stack Overflow 'date-time classes such as java.util.Date, java.util.Calendar, & java.text.SimpleDateFormat are now legacy, supplanted by the java.time classes'

14 Year Types / Reusing Diaries / Week Numbers

14 Year Types If you define a year by the first weekday of the year, and by whether it is a common year or a leap year, that gives 14 possible year types. In the lists below, for 1900-2099, years are grouped by the weekdays on 1 Jan and 1 Mar: E.g. 1900: 1 Jan was a Monday, 1 Mar was a Thursday. 20th Century: Common years: MonThu: 1900,1906,1917,1923,1934,1945,1951,1962,1973,1979,1990 TueFri: 1901,1907,1918,1929,1935,1946,1957,1963,1974,1985,1991 WedSat: 1902,1913,1919,1930,1941,1947,1958,1969,1975,1986,1997 ThuSun: 1903,1914,1925,1931,1942,1953,1959,1970,1981,1987,1998 FriMon: 1909,1915,1926,1937,1943,1954,1965,1971,1982,1993,1999 SatTue: 1910,1921,1927,1938,1949,1955,1966,1977,1983,1994 SunWed: 1905,1911,1922,1933,1939,1950,1961,1967,1978,1989,1995 Leap years: MonFri: 1912,1940,1968,1996 TueSat: 1924,1952,1980 WedSun: 1908,1936,1964,1992 ThuMon: 1920,1948,1976 FriTue: 1904,1932,1960,1988 SatWed: 1916,1944,1972 SunThu: 1928,1956,1984 21st Century: Common years: MonThu: 2001,2007,2018,2029,2035,2046,2057,2063,2074,2085,2091 TueFri: 2002,2013,2019,2030,2041,2047,2058,2069,2075,2086,2097 WedSat: 2003,2014,2025,2031,2042,2053,2059,2070,2081,2087,2098 ThuSun: 2009,2015,2026,2037,2043,2054,2065,2071,2082,2093,2099 FriMon: 2010,2021,2027,2038,2049,2055,2066,2077,2083,2094 SatTue: 2005,2011,2022,2033,2039,2050,2061,2067,2078,2089,2095 SunWed: 2006,2017,2023,2034,2045,2051,2062,2073,2079,2090 Leap years: MonFri: 2024,2052,2080 TueSat: 2008,2036,2064,2092 WedSun: 2020,2048,2076 ThuMon: 2004,2032,2060,2088 FriTue: 2016,2044,2072 SatWed: 2000,2028,2056,2084 SunThu: 2012,2040,2068,2096 Reusing Diaries You may have an old unused diary, or calendar. E.g. a diary from 1995: 1995 was a 'SunWed' year. That diary could be used in 2006/2017/2023/2034 etc. E.g. a diary from 1996. 1996 was a 'MonFri' year. A leap year. That diary could be used in 2024/2052/2080 etc. If a diary is for a leap year, it can take longer before a matching year arises. If you're impatient, one possibility is to use a diary where the first 2 months of the diary don't match the current year, but where the last 10 months do match. E.g. a diary from 1996. 1996 was a 'MonFri' year. You could use that diary during a 'TueFri' year. That diary could be used in 2002/2013/2019/2030/2041 etc. The weekdays for a 'MonFri' diary and a 'TueFri' year will differ during January and February. Also, a 'MonFri' diary will have a 29 Feb, while a 'TueFri' year won't. But a 'MonFri' diary and a 'TueFri' year will have the same structure for the months March to December. Note: bank holidays etc will typically be consistent. However, the date of Easter, and holidays based on Easter, probably won't match. Note: Easter Sunday possible date range (Gregorian calendar): 22 March to 25 April inclusive. Some dates based on Easter: Good Friday BH, Easter Sunday, Easter Monday BH. Shrove Tuesday (47 days before Easter Sunday). Ash Wednesday (46 days before Easter Sunday). Mother's Day (UK) (21 days before Easter Sunday). Note: Mother's Day (UK) possible date range: 1 March to 4 April inclusive. Note: Mother's Day (US) possible date range: 8 May to 14 May inclusive. (Second Sunday in May.) Simple Week Numbers One simple way of defining weeks (equivalent to 'WEEKNUM(A1,2)' in Excel): Weeks start on Monday. Week 1 is the week containing 1 Jan. The first 'week' of the year may not have 7 days. The last 'week' of the year may not have 7 days. E.g. week numbers for the last day of the year (i.e. the number of 'weeks' in the year): Common years: MonThu: 2001 53 [52 complete weeks, 1 day] TueFri: 2002 53 [6 days, 51 complete weeks, 2 days] WedSat: 2003 53 [5 days, 51 complete weeks, 3 days] ThuSun: 2009 53 [4 days, 51 complete weeks, 4 days] FriMon: 2010 53 [3 days, 51 complete weeks, 5 days] SatTue: 2005 53 [2 days, 51 complete weeks, 6 days] SunWed: 2006 53 [1 day, 52 complete weeks] Leap years: MonFri: 2024 53 [52 complete weeks, 2 days] TueSat: 2008 53 [6 days, 51 complete weeks, 3 days] WedSun: 2020 53 [5 days, 51 complete weeks, 4 days] ThuMon: 2004 53 [4 days, 51 complete weeks, 5 days] FriTue: 2016 53 [3 days, 51 complete weeks, 6 days] SatWed: 2000 53 [2 days, 52 complete weeks] SunThu: 2012 54 [1 day, 52 complete weeks, 1 day] ISO Week Numbers The ISO week definition: Weeks start on Monday. Week 1 is the week containing 4 Jan. (The week containing the first Thursday of the year.) All weeks have 7 days. Week numbers are of the form 'yyyyMM'. Where 'yyyy' may not match the year number in the first/last ISO weeks of the year. E.g. ISO week numbers for the first and last day of the year: Common years: MonThu: 2001 200101-200201 [spans 53 weeks] [penultimate week: 200152] TueFri: 2002 200201-200301 [spans 53 weeks] [penultimate week: 200252] WedSat: 2003 200301-200401 [spans 53 weeks] [penultimate week: 200352] ThuSun: 2009 200901-200953 [spans 53 weeks] FriMon: 2010 200953-201052 [spans 53 weeks] SatTue: 2005 200453-200552 [spans 53 weeks] SunWed: 2006 200552-200652 [spans 53 weeks] Leap years: MonFri: 2024 202401-202501 [spans 53 weeks] [penultimate week: 202452] TueSat: 2008 200801-200901 [spans 53 weeks] [penultimate week: 200852] WedSun: 2020 202001-202053 [spans 53 weeks] ThuMon: 2004 200401-200453 [spans 53 weeks] FriTue: 2016 201553-201652 [spans 53 weeks] SatWed: 2000 199952-200052 [spans 53 weeks] SunThu: 2012 201152-201301 [spans 54 weeks] [penultimate week: 201252] Note: the week spans are essentially the same as for 'WEEKNUM(A1,2)'. This is because both systems define weeks as starting on a Monday. JavaScript code for generating year lists

//code example (JavaScript):
//get weekdays for 1 Jan/1 Mar of each year e.g. 'MonThu':
oObj = {};
for (let i=1900; i<=1999; i++)
{
	vYear = i;
	//vYear += 100; //for the next century
	oDate1 = new Date(Date.UTC(vYear, 1-1));
	oDate2 = new Date(Date.UTC(vYear, 3-1));
	vWDayName1 = oDate1.toLocaleString("en", {weekday:"short"});
	vWDayName2 = oDate2.toLocaleString("en", {weekday:"short"});
	vWDayNames = vWDayName1 + vWDayName2;
	if (vWDayNames in oObj)
		oObj[vWDayNames] += "," + vYear;
	else
		oObj[vWDayNames] = "" + vYear;
}
console.log(oObj);

Python code for generating week number lists

#code example (Python):
#week numbers (equivalent to 'WEEKNUM(A1,2)' in Excel):
import datetime
oList = [2001,2002,2003,2009,2010,2005,2006,2024,2008,2020,2004,2016,2000,2012]
vOutput = ""
for vYear in oList:
	oDate = datetime.datetime(vYear, 12, 31)
	vWeek = int(oDate.strftime("%W")) + (1 if datetime.date(oDate.year,1,1).weekday() else 0)
	vOutput += str(vYear) + "\t" + str(vWeek) + "\r\n"
print(vOutput)

#code example (Python):
#ISO weeks:
import datetime
oList = [2001,2002,2003,2009,2010,2005,2006,2024,2008,2020,2004,2016,2000,2012]
vOutput = ""
for vYear in oList:
	vIsoYWeek1 = datetime.datetime(vYear, 1, 1).strftime("%G%V") #first week of year
	vIsoYWeek2 = datetime.datetime(vYear, 12, 24).strftime("%G%V") #penultimate week of year
	vIsoYWeek3 = datetime.datetime(vYear, 12, 31).strftime("%G%V") #last week of year
	vOutput += str(vYear) + "\t" + vIsoYWeek1 + "\t" + vIsoYWeek2 + "\t" + vIsoYWeek3 + "\r\n"
print(vOutput)

Excel: Calculating Date To Week Number

Since some important week number functionality is not available in Excel 2007 and earlier, we consider calculating week numbers from first principles. We will consider 2 definitions of week number. Excel: 'WEEKNUM(_, 2)' Weeks start on a Monday. The week containing January 1st is week 1. The first and last weeks of the year may have fewer than 7 days. Excel: 'ISOWEEKNUM' Weeks start on a Monday. The week containing January 4th is week 1. Every week has exactly 7 days. Days within Dec 29-31 may belong to the next year. Days within Jan 1-3 may belong to the previous year. ... This means some ISO weeks have days in 2 different years. > Weeks that start on Monday: A year that starts on Monday E.g.: Jan 1-Jan 7: Week 1 Jan 8-Jan 14: Week 2 Jan 15-Jan 21: Week 3 Etc. We can calculate date to week number like so: =INT((A1-DATE(YEAR(A1),1,1))/7)+1 Note: the DATE function creates a date from year/month/day values. I.e. 'date - Jan 1' gives us the number of elapsed days (through the year so far). Divide by 7, and truncate, to get the number of completed weeks. That gives us the nth week (0-based), i.e. the 1st week would be 0, the 2nd week would be 1. So add 1 to get the nth week (1-based), i.e. the 1st week would be 1, the 2nd week would be 2. > Weeks that start on Monday: In general: 'WEEKNUM(_, 2)' If a year starts on Tuesday. Week 1 is only 6 days long. As though by Jan 1, 1 day of the week has already elapsed. As though the year started on 31 Dec. ... If a year starts on Sunday. Week 1 is only 1 day long. As though by Jan 1, 6 days of the week have already elapsed. As though the year started on 26 Dec. Which gives us this table, where 'weekday' is the weekday on 1 Jan, where 'adjustment' is the number of days of week 1 that have 'elapsed' by 1 Jan:

weekday	adjustment
Mon	0
Tue	1
Wed	2
Thu	3
Fri	4
Sat	5
Sun	6

It so happens that: WEEKDAY(A1,3) gives exactly the values in 'adjustment'. E.g. if A1 contains a date whose weekday is Wednesday, WEEKDAY(A1,3) will return 2. Here's our formula that handles years starting on Monday: =INT((A1-DATE(YEAR(A1),1,1))/7)+1 Here's a formula that handles years starting on any day: =INT((A1-DATE(YEAR(A1),1,1)+WEEKDAY(DATE(YEAR(A1),1,1),3))/7)+1 Which is equivalent to: =WEEKNUM(A1, 2) I.e. we added '+WEEKDAY(DATE(YEAR(A1),1,1),3)' to generate the 'adjustment' value. > Weeks that start on Monday: In general: 'WEEKNUM(_, 2)': without using 'WEEKDAY(_, 3)' We took advantage of 'WEEKDAY(_, 3)'. But some versions of Excel lack this. Here's a summary: WEEKDAY(_, 1): Sunday = 1, Monday = 2, ... , Saturday = 7 WEEKDAY(_, 2): Monday = 1, Tuesday = 2, ... , Sunday = 7 WEEKDAY(_, 3): Monday = 0, Tuesday = 1, ... , Sunday = 6 Note: 'WEEKDAY(_)' is equivalent to 'WEEKDAY(_, 1)'. If we use 'WEEKDAY(_, 1)', we get a new table:

weekday	x	adjustment
Mon	2	0
Tue	3	1
Wed	4	2
Thu	5	3
Fri	6	4
Sat	7	5
Sun	1	6
where: x is WEEKDAY(_) for 1st Jan

The numbers are simpler if we consider the weekday for 31st Dec.

weekday	x	adjustment
Mon	1	0
Tue	2	1
Wed	3	2
Thu	4	3
Fri	5	4
Sat	6	5
Sun	7	6
where: x is WEEKDAY(_) for 31st Dec
where: weekday is the weekday name for 1st Jan

So, WEEKDAY(_) for 31st Dec, gives us a value 1 larger than the required adjustment. Here's our formula that handles years starting on any day, that uses 'WEEKDAY(_, 3)': =INT((A1-DATE(YEAR(A1),1,1)+WEEKDAY(DATE(YEAR(A1),1,1),3))/7)+1 Here's a formula that handles years starting on any day, using 'WEEKDAY(_)': =INT(((A1-DATE(YEAR(A1)-1,12,31)+WEEKDAY(DATE(YEAR(A1)-1,12,31)))+5)/7) Let's explain the differences: In the first, it's '(date - 1st Jan + adjustment) / 7 + 1 Equivalent to: '(date - 1st Jan + adjustment + 7) / 7 In the second, it's '(date - 31st Dec - 1 + bigger adjustment - 1 + 7) / 7 Equivalent to: '(date - 31st Dec + bigger adjustment + 5) / 7 Note: the difference between date and 31st Dec, is 1 bigger than for 1 Jan, so we subtract 1. Note: the adjustment is 1 bigger than required, so we subtract 1. Note: we changed both dates to 31st Dec, it's simpler if both dates have the same value. (We could have kept the first date as 1st Jan.) > ISO weeks: ISOWEEKNUM In the 'WEEKDAY(_, 2)' example, the logic considered the 1st week of the year starting between 26 Dec and 1 Jan. For ISOWEEKNUM, we can instead consider the 1st week of the year starting between 29 Dec and 4 Jan. Here are our two formulas for 'WEEKDAY(_, 2)', with/without 'WEEKDAY(_,3)': =INT((A1-DATE(YEAR(A1),1,1)+WEEKDAY(DATE(YEAR(A1),1,1),3))/7)+1 =INT(((A1-DATE(YEAR(A1)-1,12,31)+WEEKDAY(DATE(YEAR(A1)-1,12,31)))+5)/7) Here are two formulas for ISOWEEKNUM, with/without 'WEEKDAY(_,3)': =INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,4)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,4),3))/7)+1 =INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7) Note: YEAR(A1-WEEKDAY(A1-1)+4) gives the 'ISO year' for a date, the logic is explained lower down. Note: the 'WEEKDAY(_)' form for ISOWEEKNUM appears at excelribbon.tips.net. Note: 'DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,4)' appears twice in the first formula. Note: 'DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)' appears twice in the second formula. Note: WEEKDAY(A1) gives: Sunday = 1, Monday = 2, ... , Saturday = 7 Note: WEEKDAY(A1-1) gives: Monday = 1, Tuesday = 2, ... , Sunday = 7 Note: WEEKDAY(A1-1) is equivalent to WEEKDAY(A1, 2), and equivalent to INT(MOD(A1+5,7))+1 Note: WEEKDAY(A1-1) is a hack: use the weekday value for yesterday, to get a value for today where Monday is the smallest > ISO weeks: ISOWEEKNUM: calculating the ISO year YEAR(A1-WEEKDAY(A1-1)+4) gives the 'ISO year' for a date. '-WEEKDAY(_)' returns a number between -7 and -1 inclusive. So '-WEEKDAY(A1-1)+4' returns a number between -4 and 3 inclusive. This means that some dates in early January will return the previous year, and that some dates in late December will return the next year. A summary of ISO weeks that contain dates from 2 different years:

weekday	details
Mon	('ISO years' match years) (week 1: 1 Jan-7 Jan)
Tue	week 1 includes 31 Dec (week 1: 31 Dec-6 Jan)
Wed	week 1 includes 30-31 Dec (week 1: 30 Dec-5 Jan)
Thu	week 1 includes 29-31 Dec (week 1: 29 Dec-4 Jan)
Fri	1-3 Jan in previous ISO year (week 1: 4 Jan-10 Jan)
Sat	1-2 Jan in previous ISO year (week 1: 3 Jan-9 Jan)
Sun	1 Jan in previous ISO year (week 1: 2 Jan-8 Jan)
where: weekday is the weekday on 1st Jan

[AutoHotkey] FormatTime - Syntax & Usage | AutoHotkey v2 [C++] [C#] [Crystal] [Excel] Format numbers as dates or times - Microsoft Support Excel incorrectly assumes that the year 1900 is a leap year - Microsoft 365 Apps | Microsoft Learn [Excel VBA] Format function (Visual Basic for Applications) | Microsoft Learn Format or DatePar functions return a wrong week number for a date - Microsoft 365 Apps | Microsoft Learn [Format/DatePart] DatePart function (Visual Basic for Applications) | Microsoft Learn DateAdd Function - Microsoft Support [Go] [Java] DateTimeFormatter (Java Platform SE 8 ) TextStyle (Java Platform SE 8 ) ChronoUnit (Java Platform SE 8 ) ChronoField (Java Platform SE 8 ) IsoEra (Java Platform SE 8 ) LocalDateTime (Java Platform SE 8 ) ZonedDateTime (Java Platform SE 8 ) Instant (Java Platform SE 8 ) OffsetDateTime (Java Platform SE 8 ) ZoneId (Java Platform SE 8 ) ZoneRules (Java Platform SE 8 ) ZoneOffset (Java Platform SE 8 ) TemporalAdjusters (Java Platform SE 8 ) IsoFields (Java Platform SE 8 ) WeekFields (Java Platform SE 8 ) [JavaScript] Intl.DateTimeFormat() constructor - JavaScript | MDN Date - JavaScript | MDN [Kotlin] [note: see Java] [PHP] [Python] strftime(3) — manpages-dev — Debian bookworm — Debian Manpages datetime — Basic date and time types — Python 3.12.1 documentation [R] [Ruby] [Rust] [Swift]

Lower Priority

The only item currently marked as lower priority is DateGetEra (e.g. return AD). Another relatively low priority item is DateGetYDay. One use is to schedule something every n days.

Original Outline

note: this is the original outline on which this page was based note: further items were since added new date: UTC new date: local new date: any time zone new date: tick count (e.g. milliseconds since program started, milliseconds since PC switched on) date to string: UTC string date to string: local date string with time zone stated date to string: 14-digit string (yyyyMMddHHmmss) date to string: 6-member array (yyyy, MM, dd, HH, mm, ss) date to string: using similar formatting to AutoHotkey's FormatTime, and the Winapi's GetDateFormat/GetTimeFormat date to number: get week number (week containing January 1st is numbered week 1) date to number: get ISO week number (week containing the first Thursday of the year is numbered week 1) date add: days/hours/minutes/seconds/milliseconds date add: years/months (same algorithm as Microsoft SysDateTimePick32 controls) date diff: difference in days/hours/minutes/seconds/milliseconds date diff: difference in years/months (and handling tiebreaks) date is valid: validate string calculate key dates: Easter calculate key dates: nearest weekday to a specific date calculate key dates: nth weekday in range (e.g. 1st/2nd/3rd/4th/last Sunday of the month)