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:
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).
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
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.
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).
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*.
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 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.
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
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.
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 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)
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]
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.
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)