1package functions23import (4 "math"5 "strings"67 "src.rest/cat/012/internal/numfmt"8 "src.rest/cat/012/internal/value"9)1011var (12 dateFormat = returns(value.Preset(value.FmtDate))13 timeFormat = returns(value.Preset(value.FmtTime))14 dateTimeFormat = returns(value.Preset(value.FmtDateTime))15)1617func init() {18 define(19 &FuncDef{Name: "DATE", Args: "year, month, day", Desc: "A date from its parts; months and days past the end roll over", Min: 3, Max: 3,20 eval: numeric(date), format: dateFormat},21 &FuncDef{Name: "TIME", Args: "hour, minute, second", Desc: "A time of day from its parts", Min: 3, Max: 3,22 eval: numeric(timeOfDay), format: timeFormat},23 &FuncDef{Name: "TODAY", Desc: "Today's date, updated on every change", Max: 0, Volatile: true,24 eval: func([]Node, lookup) Value { return num(math.Floor(numfmt.SerialOf(value.Now()))) }, format: dateFormat},25 &FuncDef{Name: "NOW", Desc: "The current date and time, updated on every change", Max: 0, Volatile: true,26 eval: func([]Node, lookup) Value { return num(numfmt.SerialOf(value.Now())) }, format: dateTimeFormat},27 &FuncDef{Name: "YEAR", Args: "date", Desc: "Year of a date", Min: 1, Max: 1,28 eval: datePart(func(y, _, _ int) int { return y })},29 &FuncDef{Name: "MONTH", Args: "date", Desc: "Month of a date, 1 to 12", Min: 1, Max: 1,30 eval: datePart(func(_, m, _ int) int { return m })},31 &FuncDef{Name: "DAY", Args: "date", Desc: "Day of the month of a date", Min: 1, Max: 1,32 eval: datePart(func(_, _, d int) int { return d })},33 &FuncDef{Name: "WEEKDAY", Args: "date, [type]", Desc: "Day of the week: 1 is Sunday, or Monday with type 2", Min: 1, Max: 2,34 eval: weekday},35 &FuncDef{Name: "HOUR", Args: "time", Desc: "Hour of a time, 0 to 23", Min: 1, Max: 1,36 eval: timePart(func(s int) int { return s / 3600 })},37 &FuncDef{Name: "MINUTE", Args: "time", Desc: "Minute of a time, 0 to 59", Min: 1, Max: 1,38 eval: timePart(func(s int) int { return s / 60 % 60 })},39 &FuncDef{Name: "SECOND", Args: "time", Desc: "Second of a time, 0 to 59", Min: 1, Max: 1,40 eval: timePart(func(s int) int { return s % 60 })},41 &FuncDef{Name: "EDATE", Args: "start_date, months", Desc: "The same day a number of months away", Min: 2, Max: 2,42 eval: monthShift(false), format: dateFormat},43 &FuncDef{Name: "EOMONTH", Args: "start_date, months", Desc: "The last day of the month a number of months away", Min: 2, Max: 2,44 eval: monthShift(true), format: dateFormat},45 &FuncDef{Name: "DATEDIF", Args: `start_date, end_date, unit`, Desc: `Time between dates in "Y", "M", "D", "MD", "YM" or "YD"`, Min: 3, Max: 3,46 eval: datedif},47 &FuncDef{Name: "DAYS", Args: "end_date, start_date", Desc: "Number of days between two dates", Min: 2, Max: 2,48 eval: days},49 &FuncDef{Name: "NETWORKDAYS", Args: "start_date, end_date, [holidays]", Desc: "Number of weekdays between two dates, counting both", Min: 2, Max: 3,50 eval: networkdays},51 &FuncDef{Name: "DATEVALUE", Args: "date_string", Desc: "The date a text such as \"2026-09-26\" stands for", Min: 1, Max: 1,52 eval: dateValue, format: dateFormat},53 &FuncDef{Name: "TIMEVALUE", Args: "time_string", Desc: "The time of day a text such as \"2:30 PM\" stands for", Min: 1, Max: 1,54 eval: timeValue, format: timeFormat},55 )56}5758// Evaluators for the table above, in its order.5960func date(x []float64) Value {61 y, m, d := math.Trunc(x[0]), math.Trunc(x[1]), math.Trunc(x[2])62 if y >= 0 && y < 1900 {63 y += 1900 // DATE(26, 1, 1) is 1926, as in Sheets64 }65 if y < 0 || y >= 10000 || math.Abs(m) > 1e6 || math.Abs(d) > 1e8 {66 return value.ErrNum67 }68 v := numfmt.DateSerial(int(y), int(m), int(d))69 if v < 0 {70 return value.ErrNum71 }72 return num(v)73}7475func timeOfDay(x []float64) Value {76 secs := math.Trunc(x[0])*3600 + math.Trunc(x[1])*60 + math.Trunc(x[2])77 if secs < 0 {78 return value.ErrNum79 }80 return num(math.Mod(secs, 86400) / 86400)81}8283func weekday(args []Node, get lookup) Value {84 d, err := dateArg(args[0], get)85 if err != nil {86 return *err87 }88 typ, err := intArg(args, 1, 1, get)89 if err != nil {90 return *err91 }92 days, _ := numfmt.SplitSerial(d)93 wd := numfmt.Weekday(days) // 0 = Sunday94 switch typ {95 case 1:96 return num(float64(wd + 1))97 case 2:98 return num(float64((wd+6)%7 + 1))99 case 3:100 return num(float64((wd + 6) % 7))101 }102 return value.ErrNum103}104105func days(args []Node, get lookup) Value {106 end, err := dateArg(args[0], get)107 if err != nil {108 return *err109 }110 start, err := dateArg(args[1], get)111 if err != nil {112 return *err113 }114 return num(math.Floor(end) - math.Floor(start))115}116117func dateValue(args []Node, get lookup) Value {118 v, ok := parsedText(args[0], get)119 if ok != nil {120 return *ok121 }122 d, f, parsed := value.ParseDateTime(v)123 if !parsed || f.Kind == value.FmtTime || f.Kind == value.FmtDuration {124 return value.ErrValue125 }126 return num(math.Floor(d))127}128129func timeValue(args []Node, get lookup) Value {130 v, ok := parsedText(args[0], get)131 if ok != nil {132 return *ok133 }134 if d, _, parsed := value.ParseDateTime(v); parsed {135 return num(d - math.Floor(d))136 }137 return value.ErrValue138}139140// parsedText requires a text argument, as DATEVALUE and TIMEVALUE do.141func parsedText(n Node, get lookup) (string, *Value) {142 v := eval(n, get)143 switch v.Kind {144 case value.Error:145 return "", errOf(v)146 case value.Text:147 return v.Str, nil148 }149 return "", &value.ErrValue150}151152// dateArg is a date argument: a serial number or text such as153// "2026-09-26". Negative serials are #NUM!.154func dateArg(n Node, get lookup) (float64, *Value) {155 d, err := numArg(n, get)156 if err != nil {157 return 0, err158 }159 if d < 0 {160 return 0, &value.ErrNum161 }162 return d, nil163}164165func datePart(part func(y, m, d int) int) func([]Node, lookup) Value {166 return func(args []Node, get lookup) Value {167 d, err := dateArg(args[0], get)168 if err != nil {169 return *err170 }171 days, _ := numfmt.SplitSerial(d)172 return num(float64(part(numfmt.Civil(days))))173 }174}175176func timePart(part func(secs int) int) func([]Node, lookup) Value {177 return func(args []Node, get lookup) Value {178 d, err := dateArg(args[0], get)179 if err != nil {180 return *err181 }182 _, secs := numfmt.SplitSerial(d)183 return num(float64(part(secs)))184 }185}186187// monthShift builds EDATE and EOMONTH. EDATE keeps the day, pulling it188// back to the month's last day when it doesn't exist (Jan 31 + 1 month is189// Feb 28).190func monthShift(endOfMonth bool) func([]Node, lookup) Value {191 return func(args []Node, get lookup) Value {192 d, err := dateArg(args[0], get)193 if err != nil {194 return *err195 }196 months, err := intArg(args, 1, 0, get)197 if err != nil {198 return *err199 }200 y, m, day := numfmt.Civil(int64(math.Floor(d)))201 y, m = numfmt.AddMonths(y, m, months)202 if y < 1 || y > 9999 {203 return value.ErrNum204 }205 last := numfmt.DaysIn(y, m)206 if endOfMonth || day > last {207 day = last208 }209 v := numfmt.DateSerial(y, m, day)210 if v < 0 {211 return value.ErrNum212 }213 return num(v)214 }215}216217func datedif(args []Node, get lookup) Value {218 start, err := dateArg(args[0], get)219 if err != nil {220 return *err221 }222 end, err := dateArg(args[1], get)223 if err != nil {224 return *err225 }226 unit, err := textArg(args[2], get)227 if err != nil {228 return *err229 }230 s, e := int64(math.Floor(start)), int64(math.Floor(end))231 if s > e {232 return value.ErrNum233 }234 sy, sm, sd := numfmt.Civil(s)235 ey, em, ed := numfmt.Civil(e)236 months := (ey-sy)*12 + em - sm237 if ed < sd {238 months--239 }240 switch strings.ToUpper(unit) {241 case "Y":242 return num(float64(months / 12))243 case "M":244 return num(float64(months))245 case "D":246 return num(float64(e - s))247 case "YM":248 return num(float64(months % 12))249 case "MD":250 if ed >= sd {251 return num(float64(ed - sd))252 }253 // Days from sd in the month before the end date.254 py, pm := ey, em-1255 if pm == 0 {256 py, pm = ey-1, 12257 }258 return num(float64(e) - numfmt.DateSerial(py, pm, sd))259 case "YD":260 // Days since the last anniversary of the start date.261 y := ey262 if em < sm || em == sm && ed < sd {263 y--264 }265 anniv := numfmt.DateSerial(y, sm, min(sd, numfmt.DaysIn(y, sm)))266 return num(float64(e) - anniv)267 }268 return value.ErrNum269}270271func networkdays(args []Node, get lookup) Value {272 start, err := dateArg(args[0], get)273 if err != nil {274 return *err275 }276 end, err := dateArg(args[1], get)277 if err != nil {278 return *err279 }280 holidays := map[int64]bool{}281 if len(args) > 2 {282 hs, err := nums(args[2:], get)283 if err != nil {284 return *err285 }286 for _, h := range hs {287 holidays[int64(math.Floor(h))] = true288 }289 }290 s, e := int64(math.Floor(start)), int64(math.Floor(end))291 sign := 1.0292 if s > e {293 s, e, sign = e, s, -1294 }295 if e-s > 1e7 {296 return value.ErrNum297 }298 n := 0299 for d := s; d <= e; d++ {300 if wd := numfmt.Weekday(d); wd != 0 && wd != 6 && !holidays[d] {301 n++302 }303 }304 return num(sign * float64(n))305}