Repository Public
1import Foundation2import Testing3import SheetCore4import SheetFormat5@testable import FunctionsText67@Suite struct DateEdgeTests {8 let b = Book()910 @Test func leapYearBugOf1900() {11 #expect(b.eval("DATE(1900,2,29)") == .number(60))12 #expect(b.eval("DATE(1900,3,1)") == .number(61))13 #expect(b.eval("DATE(1900,3,0)") == .number(60))14 #expect(b.eval("DATE(1900,2,28)") == .number(59))15 #expect(b.eval("DATE(1900,1,1)") == .number(1))16 #expect(b.eval("DATE(1900,1,0)") == .number(0))17 #expect(b.eval("DATE(1900,1,-1)") == .error(.num))18 #expect(b.eval("YEAR(60)&\"-\"&MONTH(60)&\"-\"&DAY(60)") == .text("1900-2-29"))19 #expect(b.eval("DAY(0)") == .number(0))20 #expect(b.eval("MONTH(0)") == .number(1))21 #expect(b.eval("DAY(59)") == .number(28))22 #expect(b.eval("DAY(61)") == .number(1))23 #expect(b.eval("WEEKDAY(1)") == .number(1)) // Excel calls 1 Jan 1900 a Sunday24 #expect(b.eval("WEEKDAY(60)") == .number(4))25 #expect(b.eval("WEEKDAY(61)") == .number(5)) // 1 Mar 1900 really was a Thursday26 #expect(b.eval("WEEKDAY(0)") == .number(7))27 #expect(b.eval("DATEVALUE(\"2/29/1900\")") == .number(60))28 #expect(b.eval("DATEVALUE(\"2/30/1900\")") == .error(.value))29 #expect(b.eval("DATEVALUE(\"12/31/1899\")") == .error(.value))30 #expect(b.eval("EOMONTH(32,0)") == .number(60))31 #expect(b.eval("EDATE(31,1)") == .number(60))32 #expect(b.eval("EDATE(60,12)") == .number(425)) // 28 Feb 190133 #expect(b.eval("WEEKNUM(61,2)") == .number(10))34 #expect(b.eval("DAYS360(1,61)") == .number(60))35 #expect(b.eval("DATEDIF(59,61,\"D\")") == .number(2))36 #expect(b.eval("NETWORKDAYS(1,7)") == .number(5))37 }3839 @Test func dateSystem1904() {40 let m = Book(date1904: true)41 #expect(m.eval("DATE(1904,1,1)") == .number(0))42 #expect(m.eval("DATE(2023,3,15)") == .number(43538))43 #expect(m.eval("DATE(4,1,2)") == .number(1))44 #expect(m.eval("YEAR(0)&\"-\"&MONTH(0)&\"-\"&DAY(0)") == .text("1904-1-1"))45 #expect(m.eval("WEEKDAY(0)") == .number(6)) // Friday46 #expect(m.eval("TEXT(0,\"yyyy-mm-dd dddd\")") == .text("1904-01-01 Friday"))47 #expect(m.eval("DATEVALUE(\"3/15/2023\")") == .number(43538))48 #expect(m.eval("DATEVALUE(\"12/31/1903\")") == .error(.value))49 #expect(m.eval("EOMONTH(0,1)") == .number(59)) // 29 Feb 190450 #expect(m.eval("EDATE(30,1)") == .number(59))51 #expect(m.eval("NETWORKDAYS(0,6)") == .number(5))52 #expect(m.eval("WORKDAY(0,1)") == .number(3)) // Friday → Monday53 #expect(m.eval("ISOWEEKNUM(43538)") == .number(11))54 #expect(m.eval("WEEKNUM(43538)") == .number(11))55 #expect(m.eval("YEARFRAC(0,366,1)") == .number(1))56 #expect(m.eval("DATEDIF(0,43538,\"Y\")") == .number(119))57 #expect(m.eval("DAYS360(DATE(2023,1,31),DATE(2023,3,1))") == .number(31))58 #expect(m.eval("TEXT(-1,\"yyyy\")") == .text("-1904"))59 }6061 @Test func timeRounding() {62 #expect(b.eval("HOUR(0.9999999)") == .number(0)) // rounds to 24:00:0063 #expect(b.eval("MINUTE(0.9999999)") == .number(0))64 #expect(b.eval("SECOND(0.9999999)") == .number(0))65 #expect(b.eval("SECOND(0.99999)") == .number(59))66 #expect(b.eval("MINUTE(\"12:34:59.7\")") == .number(35))67 #expect(b.eval("SECOND(\"12:34:59.7\")") == .number(0))68 #expect(b.eval("HOUR(45000.75)") == .number(18))69 #expect(b.eval("HOUR(TIME(13,45,10))&\":\"&MINUTE(TIME(13,45,10))&\":\"&SECOND(TIME(13,45,10))") == .text("13:45:10"))70 #expect(b.eval("TIME(12.9,30.9,10.9)=TIME(12,30,10)") == .bool(true))71 #expect(b.eval("TIME(27,0,0)") == .number(0.125))72 #expect(b.eval("TIME(0,0,32768)") == .error(.num))73 #expect(b.eval("DAYS(45000.9,44000.1)") == .number(1000))74 }7576 @Test func microsoftDocumentedExamples() {77 #expect(b.eval("DATEDIF(\"1/1/2001\",\"1/1/2003\",\"Y\")") == .number(2))78 #expect(b.eval("DATEDIF(\"6/1/2001\",\"8/15/2002\",\"D\")") == .number(440))79 #expect(b.eval("DATEDIF(\"6/1/2001\",\"8/15/2002\",\"YD\")") == .number(75))80 #expect(b.eval("DATEDIF(DATE(2023,1,31),DATE(2023,3,1),\"MD\")") == .number(-2)) // the documented MD quirk81 #expect(b.eval("DATEDIF(DATE(2024,1,31),DATE(2024,3,1),\"MD\")") == .number(-1))82 #expect(b.eval("DAYS360(\"1-Jan-11\",\"31-Dec-11\")") == .number(360))83 #expect(b.eval("DAYS360(\"30-Jan-11\",\"1-Feb-11\")") == .number(1))84 #expect(b.eval("DAYS360(\"1-Jan-11\",\"1-Feb-11\")") == .number(30))85 #expect(abs(n(b.eval("YEARFRAC(\"1/1/2012\",\"7/30/2012\")")) - 0.58055556) < 1e-8)86 #expect(abs(n(b.eval("YEARFRAC(\"1/1/2012\",\"7/30/2012\",1)")) - 0.57650273) < 1e-8)87 #expect(abs(n(b.eval("YEARFRAC(\"1/1/2012\",\"7/30/2012\",3)")) - 0.57808219) < 1e-8)88 #expect(b.eval("NETWORKDAYS(\"10/1/2012\",\"3/1/2013\")") == .number(110))89 #expect(b.eval("NETWORKDAYS(\"10/1/2012\",\"3/1/2013\",\"11/22/2012\")") == .number(109))90 #expect(b.eval("NETWORKDAYS(\"10/1/2012\",\"3/1/2013\",{\"11/22/2012\",\"12/4/2012\",\"1/21/2013\"})") == .number(107))91 #expect(b.eval("WORKDAY(\"10/1/2008\",151)") == .number(39933))92 #expect(b.eval("WORKDAY(\"10/1/2008\",151,{\"11/26/2008\",\"12/4/2008\",\"1/21/2009\"})") == .number(39938))93 #expect(b.eval("NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,1,31))") == .number(22))94 #expect(b.eval("NETWORKDAYS.INTL(DATE(2006,2,28),DATE(2006,1,31))") == .number(-21))95 #expect(b.eval("NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),7,{\"2006/1/2\",\"2006/1/16\"})") == .number(22))96 #expect(b.eval("NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),\"0010001\",{\"2006/1/2\",\"2006/1/16\"})") == .number(20))97 #expect(b.eval("WORKDAY.INTL(DATE(2012,1,1),30,0)") == .error(.num))98 #expect(b.eval("WORKDAY.INTL(DATE(2012,1,1),90,11)") == .number(41013))99 #expect(b.eval("TEXT(WORKDAY.INTL(DATE(2012,1,1),30,17),\"m/dd/yyyy\")") == .text("2/05/2012"))100 #expect(b.eval("WEEKNUM(\"3/9/2012\")") == .number(10))101 #expect(b.eval("WEEKNUM(\"3/9/2012\",2)") == .number(11))102 #expect(b.eval("ISOWEEKNUM(\"3/9/2012\")") == .number(10))103 #expect(b.eval("EDATE(\"15-Jan-11\",1)") == .number(40589))104 #expect(b.eval("EDATE(\"15-Jan-11\",-1)") == .number(40527))105 #expect(b.eval("EOMONTH(\"1-Jan-11\",1)") == .number(40602))106 #expect(b.eval("EOMONTH(\"1-Jan-11\",-3)") == .number(40482))107 #expect(b.eval("WEEKDAY(\"2/14/2008\")") == .number(5))108 #expect(b.eval("WEEKDAY(\"2/14/2008\",2)") == .number(4))109 #expect(b.eval("WEEKDAY(\"2/14/2008\",3)") == .number(3))110 #expect(b.eval("DATE(2008,14,2)") == b.eval("DATE(2009,2,2)"))111 #expect(b.eval("DATE(2008,1,-15)") == b.eval("DATE(2007,12,16)"))112 #expect(b.eval("DATE(108,1,2)") == b.eval("DATE(2008,1,2)"))113 #expect(b.eval("DATEVALUE(\"8/22/2011\")") == .number(40777))114 #expect(b.eval("DATEVALUE(\"22-MAY-2011\")") == .number(40685))115 #expect(b.eval("DATEVALUE(\"2011/02/23\")") == .number(40597))116 #expect(abs(n(b.eval("TIMEVALUE(\"8:24 AM\")")) - 0.35) < 1e-12)117 #expect(abs(n(b.eval("TIMEVALUE(\"22-Aug-2011 6:35 AM\")")) - 0.274305556) < 1e-9)118 #expect(b.eval("DAYS(\"15-MAR-2021\",\"1-FEB-2021\")") == .number(42))119 }120121 @Test func holidaysFromRanges() {122 b.set("D1", "45005"); b.set("D2", "2023-03-21"); b.set("D4", "45010")123 b.set("E1", "=NETWORKDAYS(45000,45016,D1:D4)")124 b.set("E2", "=WORKDAY(45000,5,D1:D4)")125 b.set("E3", "=NETWORKDAYS.INTL(45000,45016,1,D1:D4)")126 b.set("E4", "=WORKDAY.INTL(45000,5,\"0000011\",D1:D4)")127 #expect(b.get("E1") == .number(11))128 #expect(b.get("E2") == .number(45009))129 #expect(b.get("E3") == .number(11))130 #expect(b.get("E4") == .number(45009))131 #expect(b.eval("SUM(NETWORKDAYS({45000,45001},45016))") == .number(25))132 #expect(b.eval("WORKDAY(45000,3000000)") == .error(.num))133 #expect(b.eval("WORKDAY(45000,-40000)") == .error(.num))134 #expect(b.eval("NETWORKDAYS.INTL(45000,45100,\"1111111\")") == .number(0))135 #expect(b.eval("WORKDAY.INTL(45000,3,\"1111111\")") == .error(.value))136 #expect(b.eval("WORKDAY(2958460,3)").isError == false)137 #expect(b.eval("WORKDAY(2958465,1)") == .error(.num))138 }139140 @Test func rangeLimits() {141 #expect(b.eval("YEAR(2958465)") == .number(9999))142 #expect(b.eval("YEAR(2958466)") == .error(.num))143 #expect(b.eval("DATE(9999,12,31)") == .number(2958465))144 #expect(b.eval("DATE(9999,12,32)") == .error(.num))145 #expect(b.eval("EDATE(2958465,1)") == .error(.num))146 #expect(b.eval("DATE(1899,12,31)") == .number(693_962)) // 1899 + 1900 = year 3799147 #expect(b.eval("YEARFRAC(45000,45000,1)") == .number(0))148 #expect(b.eval("YEARFRAC(45100,45000,1)") == b.eval("YEARFRAC(45000,45100,1)"))149 #expect(b.eval("DATEDIF(45000,45000,\"D\")") == .number(0))150 #expect(b.eval("WEEKNUM(DATE(2020,12,31))") == .number(53))151 #expect(b.eval("WEEKNUM(DATE(2011,12,31),2)") == .number(53))152 #expect(b.eval("ISOWEEKNUM(DATE(2021,1,3))") == .number(53))153 #expect(b.eval("ISOWEEKNUM(DATE(2024,12,30))") == .number(1))154 }155}156157@Suite struct LocaleTests {158 @Test func canadianFrench() {159 let registry = FunctionRegistry()160 TextFunctions.register(into: registry, locale: .frCA)161 let wb = Workbook(sheetNames: ["Feuil1"])162 let engine = CalcEngine(workbook: wb, registry: registry)163 func eval(_ f: String) -> Value { engine.evaluate("=" + f, sheet: 0) }164 #expect(eval("FIXED(1234.5,1)") == .text("1\u{00A0}234,5"))165 #expect(eval("DOLLAR(1234.5)") == .text("$1\u{00A0}234,50"))166 #expect(eval("TEXT(45000,\"d mmmm yyyy\")") == .text("15 mars 2023"))167 #expect(eval("TEXT(0.5,\"0.0%\")") == .text("50,0%"))168 #expect(eval("VALUE(\"1 234,5\")") == .number(1234.5))169 #expect(eval("VALUE(\"12,5\")") == .number(12.5))170 #expect(eval("NUMBERVALUE(\"1 234,5\")") == .number(1234.5))171 #expect(eval("DATEVALUE(\"15 mars 2023\")") == .number(45000))172 #expect(eval("DATEVALUE(\"15 févr. 2023\")") == .number(44972))173 #expect(eval("DATEVALUE(\"2023-03-15\")") == .number(45000))174 #expect(eval("DATEVALUE(\"15 March 2023\")") == .number(45000))175 #expect(eval("VALUETOTEXT(1.5)") == .text("1,5"))176 }177}178