1package sheet23import (4 "testing"5 "time"67 "src.rest/cat/012/internal/value"8)910func TestDateFunctions(t *testing.T) {11 checkFormulas(t, fixture(t), []fnCase{12 {"=DATE(2026, 9, 26)", num(46291)},13 {"=DATE(2026, 14, 1)", num(46419)}, // month 14 rolls into 202714 {"=DATE(2026, 1, 0)", num(46022)}, // day 0 is the last of December15 {"=DATE(26, 1, 1)", num(9498)}, // two-digit years are 19xx16 {"=DATE(-1, 1, 1)", ErrNum},17 {"=DATE(1899, 12, 30)", num(693961)}, // 1899 counts as 3799, as in Sheets18 {"=TIME(14, 30, 0)", num(14.5 / 24)},19 {"=TIME(25, 0, 0)", num(1.0 / 24)},20 {"=TIME(0, -1, 0)", ErrNum},21 {"=TODAY()", num(46291)},22 {"=NOW()", num(46291 + 14.5/24)},23 {"=YEAR(F1)", num(2026)},24 {"=MONTH(F1)", num(9)},25 {"=DAY(F1)", num(26)},26 {`=YEAR("2026-09-26")`, num(2026)},27 {"=YEAR(-1)", ErrNum},28 {"=YEAR(A4)", ErrValue},29 {"=WEEKDAY(F1)", num(7)}, // Saturday30 {"=WEEKDAY(F1, 2)", num(6)},31 {"=WEEKDAY(F1, 3)", num(5)},32 {"=WEEKDAY(F1, 9)", ErrNum},33 {"=HOUR(F3)", num(14)},34 {"=MINUTE(F3)", num(30)},35 {"=SECOND(TIME(1, 2, 3))", num(3)},36 {"=HOUR(0.99999999)", num(0)}, // rounds to midnight37 {"=EDATE(F2, 1)", num(46081)}, // Jan 31 + 1 month is Feb 2838 {"=EDATE(F1, -12)", num(45926)},39 {"=EOMONTH(F1, 0)", num(46295)},40 {"=EOMONTH(F4, 12)", num(45716)},41 {`=DATEDIF(F4, F1, "Y")`, num(2)},42 {`=DATEDIF(F4, F1, "M")`, num(30)},43 {`=DATEDIF(F4, F1, "D")`, num(46291 - 45351)},44 {`=DATEDIF(F4, F1, "YM")`, num(6)},45 {`=DATEDIF(F4, F1, "MD")`, num(28)},46 {`=DATEDIF(F4, F1, "YD")`, num(210)}, // from Feb 28, the leap day's anniversary47 {`=DATEDIF(F2, F5, "D")`, ErrNum}, // start after end48 {`=DATEDIF(F4, F1, "Q")`, ErrNum},49 {"=DAYS(F1, F2)", num(238)},50 {`=DAYS("2026-01-02", "2026-01-01")`, num(1)},51 {"=NETWORKDAYS(F5, F2)", num(23)},52 {"=NETWORKDAYS(F2, F5)", num(-23)},53 {"=NETWORKDAYS(F5, F2, F5)", num(22)},54 {`=DATEVALUE("9/26/2026")`, num(46291)},55 {`=DATEVALUE("Sep 26, 2026 14:30")`, num(46291)},56 {`=DATEVALUE("hello")`, ErrValue},57 {"=DATEVALUE(46291)", ErrValue},58 {`=TIMEVALUE("2:30 PM")`, num(14.5 / 24)},59 {"=F1+1", num(46292)},60 {`="2026-09-27"-F1`, num(1)}, // date text coerces in arithmetic61 })62}6364func TestInferredFormats(t *testing.T) {65 fixedNow(t)66 s := New()67 for a, in := range map[string]string{68 "A1": "$1,200.00", "A2": "$300.00", "A3": "9/26/2026", "A4": "10%", "A5": "9/1/2026",69 } {70 s.Set(at(a), in)71 }72 tests := []struct{ in, want string }{73 {"=A1+A2", "$1,500.00"},74 {"=SUM(A1:A2)", "$1,500.00"},75 {"=A1*2", "$2,400.00"},76 {"=A3+7", "10/3/2026"},77 {"=A3-A5", "25"},78 {"=DATE(2026,1,31)", "1/31/2026"},79 {"=EDATE(A3,1)", "10/26/2026"},80 {"=TODAY()", "9/26/2026"},81 {"=NOW()", "9/26/2026 14:30:00"},82 {"=A1*A4", "$120.00"}, // the first formatted input wins83 {"=YEAR(A3)", "2026"},84 {"=MAX(A3,A5)", "9/26/2026"},85 {"=IF(A1>0,A2,0)", "$300.00"},86 }87 for _, tt := range tests {88 s.Set(at("C1"), tt.in)89 if got, _ := Display(s.Value(at("C1")), s.DisplayFormat(at("C1")), 30); got != tt.want {90 t.Errorf("%s shows %q, want %q", tt.in, got, tt.want)91 }92 }93 // Changing an input's format updates formulas that follow it.94 s.Set(at("C1"), "=A1+A2")95 s.SetFormat(NewRect(at("A1"), at("A1")), Preset(FmtNumber))96 if got, _ := Display(s.Value(at("C1")), s.DisplayFormat(at("C1")), 30); got != "1,500.00" {97 t.Errorf("after reformat C1 shows %q", got)98 }99}100101func TestVolatileRecalc(t *testing.T) {102 fixedNow(t)103 s := New()104 s.Set(at("A1"), "=TODAY()")105 s.Set(at("A2"), "=A1+1")106 if got := s.Value(at("A2")).Num; got != 46292 {107 t.Fatalf("A2 = %v", got)108 }109 value.Now = func() time.Time { return time.Date(2026, 9, 27, 9, 0, 0, 0, time.Local) }110 s.Set(at("Z1"), "unrelated") // any change recalculates volatile cells111 if got := s.Value(at("A2")).Num; got != 46293 {112 t.Errorf("A2 after a day = %v, want 46293", got)113 }114 s.Set(at("A1"), "5")115 if _, ok := s.volatile[at("A1")]; ok {116 t.Error("A1 still volatile after replacing the formula")117 }118}119120func TestVolatileFunctions(t *testing.T) {121 for _, name := range []string{"TODAY", "NOW", "RAND", "RANDBETWEEN"} {122 if f, _ := LookupFunc(name); !f.Volatile {123 t.Errorf("%s not volatile", name)124 }125 }126}