SPB Git

Repository Public

DateEdgeTests.swift 10.4 KB 178 lines swift

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
Files main
Menu

Theme