diff options
| -rw-r--r-- | calc.go | 90 | ||||
| -rw-r--r-- | calc_test.go | 19 |
2 files changed, 98 insertions, 11 deletions
@@ -256,6 +256,7 @@ var tokenPriority = map[string]int{ // CSC // CSCH // DATE +// DATEDIF // DEC2BIN // DEC2HEX // DEC2OCT @@ -4634,22 +4635,89 @@ func (fn *formulaFuncs) DATE(argsList *list.List) formulaArg { if argsList.Len() != 3 { return newErrorFormulaArg(formulaErrorVALUE, "DATE requires 3 number arguments") } - var year, month, day int - var err error - if year, err = strconv.Atoi(argsList.Front().Value.(formulaArg).String); err != nil { - return newErrorFormulaArg(formulaErrorVALUE, "DATE requires 3 number arguments") - } - if month, err = strconv.Atoi(argsList.Front().Next().Value.(formulaArg).String); err != nil { - return newErrorFormulaArg(formulaErrorVALUE, "DATE requires 3 number arguments") - } - if day, err = strconv.Atoi(argsList.Back().Value.(formulaArg).String); err != nil { + year := argsList.Front().Value.(formulaArg).ToNumber() + month := argsList.Front().Next().Value.(formulaArg).ToNumber() + day := argsList.Back().Value.(formulaArg).ToNumber() + if year.Type != ArgNumber || month.Type != ArgNumber || day.Type != ArgNumber { return newErrorFormulaArg(formulaErrorVALUE, "DATE requires 3 number arguments") } - d := makeDate(year, time.Month(month), day) + d := makeDate(int(year.Number), time.Month(month.Number), int(day.Number)) return newStringFormulaArg(timeFromExcelTime(daysBetween(excelMinTime1900.Unix(), d)+1, false).String()) } -// NOW function returns the current date and time. The function receives no arguments and therefore. The syntax of the function is: +// DATEDIF function calculates the number of days, months, or years between +// two dates. The syntax of the function is: +// +// DATEDIF(start_date,end_date,unit) +// +func (fn *formulaFuncs) DATEDIF(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "DATEDIF requires 3 number arguments") + } + startArg, endArg := argsList.Front().Value.(formulaArg).ToNumber(), argsList.Front().Next().Value.(formulaArg).ToNumber() + if startArg.Type != ArgNumber || endArg.Type != ArgNumber { + return startArg + } + if startArg.Number > endArg.Number { + return newErrorFormulaArg(formulaErrorNUM, "start_date > end_date") + } + if startArg.Number == endArg.Number { + return newNumberFormulaArg(0) + } + unit := strings.ToLower(argsList.Back().Value.(formulaArg).Value()) + startDate, endDate := timeFromExcelTime(startArg.Number, false), timeFromExcelTime(endArg.Number, false) + sy, smm, sd := startDate.Date() + ey, emm, ed := endDate.Date() + sm, em, diff := int(smm), int(emm), 0.0 + switch unit { + case "d": + return newNumberFormulaArg(endArg.Number - startArg.Number) + case "y": + diff = float64(ey - sy) + if em < sm || (em == sm && ed < sd) { + diff-- + } + case "m": + ydiff := ey - sy + mdiff := em - sm + if ed < sd { + mdiff-- + } + if mdiff < 0 { + ydiff-- + mdiff += 12 + } + diff = float64(ydiff*12 + mdiff) + case "md": + smMD := em + if ed < sd { + smMD-- + } + diff = endArg.Number - daysBetween(excelMinTime1900.Unix(), makeDate(ey, time.Month(smMD), sd)) - 1 + case "ym": + diff = float64(em - sm) + if ed < sd { + diff-- + } + if diff < 0 { + diff += 12 + } + case "yd": + syYD := sy + if em < sm || (em == sm && ed < sd) { + syYD++ + } + s := daysBetween(excelMinTime1900.Unix(), makeDate(syYD, time.Month(em), ed)) + e := daysBetween(excelMinTime1900.Unix(), makeDate(sy, time.Month(sm), sd)) + diff = s - e + default: + return newErrorFormulaArg(formulaErrorVALUE, "DATEDIF has invalid unit") + } + return newNumberFormulaArg(diff) +} + +// NOW function returns the current date and time. The function receives no +// arguments and therefore. The syntax of the function is: // // NOW() // diff --git a/calc_test.go b/calc_test.go index 6b7093e..6fc61b0 100644 --- a/calc_test.go +++ b/calc_test.go @@ -722,6 +722,20 @@ func TestCalcCellValue(t *testing.T) { // DATE "=DATE(2020,10,21)": "2020-10-21 00:00:00 +0000 UTC", "=DATE(1900,1,1)": "1899-12-31 00:00:00 +0000 UTC", + // DATEDIF + "=DATEDIF(43101,43101,\"D\")": "0", + "=DATEDIF(43101,43891,\"d\")": "790", + "=DATEDIF(43101,43891,\"Y\")": "2", + "=DATEDIF(42156,44242,\"y\")": "5", + "=DATEDIF(43101,43891,\"M\")": "26", + "=DATEDIF(42171,44242,\"m\")": "67", + "=DATEDIF(42156,44454,\"MD\")": "14", + "=DATEDIF(42171,44242,\"md\")": "30", + "=DATEDIF(43101,43891,\"YM\")": "2", + "=DATEDIF(42171,44242,\"ym\")": "7", + "=DATEDIF(43101,43891,\"YD\")": "59", + "=DATEDIF(36526,73110,\"YD\")": "60", + "=DATEDIF(42171,44242,\"yd\")": "244", // Text Functions // CHAR "=CHAR(65)": "A", @@ -1436,6 +1450,11 @@ func TestCalcCellValue(t *testing.T) { `=DATE("text",10,21)`: "DATE requires 3 number arguments", `=DATE(2020,"text",21)`: "DATE requires 3 number arguments", `=DATE(2020,10,"text")`: "DATE requires 3 number arguments", + // DATEDIF + "=DATEDIF()": "DATEDIF requires 3 number arguments", + "=DATEDIF(\"\",\"\",\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DATEDIF(43891,43101,\"Y\")": "start_date > end_date", + "=DATEDIF(43101,43891,\"x\")": "DATEDIF has invalid unit", // NOW "=NOW(A1)": "NOW accepts no arguments", // TODAY |