| 1 | package calc |
| 2 | |
| 3 | import ( |
| 4 | "math" |
| 5 | "time" |
| 6 | ) |
| 7 | |
| 8 | // fn_date_parts.go: the date functions that take a value rather than the |
| 9 | // clock. |
| 10 | // |
| 11 | // **This engine uses a PROLEPTIC 1900 system, and Excel does not.** |
| 12 | // Excel's serial 60 is 1900-02-29 — a day that did not exist, kept for |
| 13 | // compatibility with Lotus 1-2-3 — so every Excel serial from 60 onward |
| 14 | // is one greater than a correct day count from 1900-01-01. This engine |
| 15 | // counts real days from 1899-12-30 (fn_date.go's excelEpoch), which |
| 16 | // makes it agree with Excel for every date from 1900-03-01 and disagree |
| 17 | // below it: DATE(1900,1,1) is 2 here and 1 in Excel, and |
| 18 | // DATE(1900,2,29) normalises to March 1 rather than existing. |
| 19 | // |
| 20 | // Named here rather than fixed, deliberately. The epoch is shipped and |
| 21 | // NOW/TODAY already round-trip through it, XLSX import and export both |
| 22 | // carry serials through the same constant, and changing it moves every |
| 23 | // stored date in every sheet. That is a migration and a spec ruling, |
| 24 | // not a patch to a new file. Recorded in the slice spec's deferred |
| 25 | // table with a trigger: a sheet whose dates predate 1900-03-01. |
| 26 | // |
| 27 | // The branch review found this by noticing that every corpus case |
| 28 | // round-trips a modern DATE through YEAR/MONTH/DAY, so the offset |
| 29 | // cancels and the oracle stays green. That is true, and it is why the |
| 30 | // divergence is written down here rather than discovered later. |
| 31 | // |
| 32 | // NOW and TODAY are in fn_date.go and are clockFuncs, because they need |
| 33 | // the pass's pinned instant. These need nothing but their arguments, so |
| 34 | // they are ordinary Funcs — and keeping the two kinds in separate files |
| 35 | // is what stops somebody adding a sixth one here and reaching for |
| 36 | // env.Now() out of habit, which is the bug |
| 37 | // TestClockIsPinnedWithinAPass was written for. |
| 38 | // |
| 39 | // A date is a number. fn_date.go's excelEpoch comment states the rule |
| 40 | // and it holds here unchanged: there is no date Value kind, only a |
| 41 | // serial that a number format displays as a calendar date. |
| 42 | |
| 43 | func init() { |
| 44 | Register("YEAR", fnYear) |
| 45 | Register("MONTH", fnMonth) |
| 46 | Register("DAY", fnDay) |
| 47 | Register("DATE", fnDate) |
| 48 | Register("EOMONTH", fnEOMonth) |
| 49 | } |
| 50 | |
| 51 | // serialTime is dateSerial's inverse. |
| 52 | // |
| 53 | // Truncated toward zero before conversion, so the time of day inside a |
| 54 | // serial is discarded rather than rounded: 2026-09-03 23:59 must be |
| 55 | // DAY 3, and a serial rounded to the nearest whole day would make it |
| 56 | // the 4th. That is the one arithmetic mistake available here and it is |
| 57 | // invisible for all but the last hour of a day, which is when nobody |
| 58 | // is looking. |
| 59 | func serialTime(serial float64) time.Time { |
| 60 | return excelEpoch.AddDate(0, 0, int(math.Trunc(serial))) |
| 61 | } |
| 62 | |
| 63 | // datePart is the shared body of YEAR, MONTH and DAY. |
| 64 | // |
| 65 | // A negative serial is #NUM!, not a date before 1899: Excel has no |
| 66 | // negative dates, and AddDate would cheerfully produce one that then |
| 67 | // formats as something plausible. |
| 68 | func datePart(args []Value, part func(time.Time) int) Value { |
| 69 | if len(args) != 1 { |
| 70 | return errValue(ErrValue) |
| 71 | } |
| 72 | if args[0].Kind == KindErr { |
| 73 | return args[0] |
| 74 | } |
| 75 | serial, ek := toNumber(args[0]) |
| 76 | if ek != ErrNone { |
| 77 | return errValue(ek) |
| 78 | } |
| 79 | if serial < 0 { |
| 80 | return errValue(ErrNum) |
| 81 | } |
| 82 | return Value{Kind: KindNum, Num: float64(part(serialTime(serial)))} |
| 83 | } |
| 84 | |
| 85 | // maxSheetYear is the last year Excel's date system represents. |
| 86 | const maxSheetYear = 9999 |
| 87 | |
| 88 | func fnYear(args []Value) Value { |
| 89 | return datePart(args, func(t time.Time) int { return t.Year() }) |
| 90 | } |
| 91 | |
| 92 | func fnMonth(args []Value) Value { |
| 93 | return datePart(args, func(t time.Time) int { return int(t.Month()) }) |
| 94 | } |
| 95 | |
| 96 | func fnDay(args []Value) Value { |
| 97 | return datePart(args, func(t time.Time) int { return t.Day() }) |
| 98 | } |
| 99 | |
| 100 | // fnDate implements DATE(year, month, day). |
| 101 | // |
| 102 | // Out-of-range months and days ROLL, and that is Excel's documented |
| 103 | // behaviour rather than a shortcut: DATE(2026,13,1) is January 2027 and |
| 104 | // DATE(2026,1,0) is the last day of December 2025. time.Date normalises |
| 105 | // exactly this way, which is why the arguments are passed through |
| 106 | // rather than validated — validating them would be a divergence dressed |
| 107 | // as strictness, and `=DATE(YEAR(A1),MONTH(A1)+1,0)` is the idiom half |
| 108 | // of all end-of-month formulas are written with. |
| 109 | func fnDate(args []Value) Value { |
| 110 | if len(args) != 3 { |
| 111 | return errValue(ErrValue) |
| 112 | } |
| 113 | parts := make([]int, 3) |
| 114 | for i, a := range args { |
| 115 | if a.Kind == KindErr { |
| 116 | return a |
| 117 | } |
| 118 | n, ek := toNumber(a) |
| 119 | if ek != ErrNone { |
| 120 | return errValue(ek) |
| 121 | } |
| 122 | parts[i] = int(math.Trunc(n)) |
| 123 | } |
| 124 | // Excel's two-digit year window: 0-1899 is read as an offset from |
| 125 | // 1900, so DATE(26,9,3) is 1926 and not the year 26. Written out |
| 126 | // rather than left to time.Date, which would happily build a |
| 127 | // first-century date that then formats as a plausible-looking |
| 128 | // serial nobody meant. |
| 129 | year := parts[0] |
| 130 | if year >= 0 && year < 1900 { |
| 131 | year += 1900 |
| 132 | } |
| 133 | // Excel's own upper bound. time.Date will build the year 400000 |
| 134 | // without complaint and dateSerial will return a number for it, |
| 135 | // which is a value no spreadsheet can hold and no export can carry. |
| 136 | if year > maxSheetYear { |
| 137 | return errValue(ErrNum) |
| 138 | } |
| 139 | t := time.Date(year, time.Month(parts[1]), parts[2], 0, 0, 0, 0, time.UTC) |
| 140 | if t.Before(excelEpoch) { |
| 141 | return errValue(ErrNum) |
| 142 | } |
| 143 | return Value{Kind: KindNum, Num: dateSerial(t)} |
| 144 | } |
| 145 | |
| 146 | // fnEOMonth implements EOMONTH(start_date, months): the last day of the |
| 147 | // month `months` away from start_date. |
| 148 | // |
| 149 | // Computed as day zero of the FOLLOWING month rather than by asking how |
| 150 | // many days the target month has. Both are correct; this one has no |
| 151 | // leap-year branch to get wrong, and time.Date's normalisation is |
| 152 | // already the thing being relied on in fnDate above. |
| 153 | func fnEOMonth(args []Value) Value { |
| 154 | if len(args) != 2 { |
| 155 | return errValue(ErrValue) |
| 156 | } |
| 157 | for _, a := range args { |
| 158 | if a.Kind == KindErr { |
| 159 | return a |
| 160 | } |
| 161 | } |
| 162 | start, ek := toNumber(args[0]) |
| 163 | if ek != ErrNone { |
| 164 | return errValue(ek) |
| 165 | } |
| 166 | months, ek := toNumber(args[1]) |
| 167 | if ek != ErrNone { |
| 168 | return errValue(ek) |
| 169 | } |
| 170 | if start < 0 { |
| 171 | return errValue(ErrNum) |
| 172 | } |
| 173 | t := serialTime(start) |
| 174 | end := time.Date(t.Year(), t.Month()+time.Month(int(math.Trunc(months)))+1, 0, |
| 175 | 0, 0, 0, 0, time.UTC) |
| 176 | if end.Before(excelEpoch) { |
| 177 | return errValue(ErrNum) |
| 178 | } |
| 179 | return Value{Kind: KindNum, Num: dateSerial(end)} |
| 180 | } |
| 181 | |
amadan