diff options
| -rw-r--r-- | calc.go | 52 | ||||
| -rw-r--r-- | calc_test.go | 15 |
2 files changed, 67 insertions, 0 deletions
@@ -419,6 +419,7 @@ type formulaFuncs struct { // DATEVALUE // DAY // DAYS +// DAYS360 // DB // DDB // DEC2BIN @@ -12330,6 +12331,57 @@ func (fn *formulaFuncs) DAYS(argsList *list.List) formulaArg { return newNumberFormulaArg(end.Number - start.Number) } +// DAYS360 function returns the number of days between 2 dates, based on a +// 360-day year (12 x 30 months). The syntax of the function is: +// +// DAYS360(start_date,end_date,[method]) +// +func (fn *formulaFuncs) DAYS360(argsList *list.List) formulaArg { + if argsList.Len() < 2 { + return newErrorFormulaArg(formulaErrorVALUE, "DAYS360 requires at least 2 arguments") + } + if argsList.Len() > 3 { + return newErrorFormulaArg(formulaErrorVALUE, "DAYS360 requires at most 3 arguments") + } + startDate := toExcelDateArg(argsList.Front().Value.(formulaArg)) + if startDate.Type != ArgNumber { + return startDate + } + endDate := toExcelDateArg(argsList.Front().Next().Value.(formulaArg)) + if endDate.Type != ArgNumber { + return endDate + } + start, end := timeFromExcelTime(startDate.Number, false), timeFromExcelTime(endDate.Number, false) + sy, sm, sd, ey, em, ed := start.Year(), int(start.Month()), start.Day(), end.Year(), int(end.Month()), end.Day() + method := newBoolFormulaArg(false) + if argsList.Len() > 2 { + if method = argsList.Back().Value.(formulaArg).ToBool(); method.Type != ArgNumber { + return method + } + } + if method.Number == 1 { + if sd == 31 { + sd-- + } + if ed == 31 { + ed-- + } + } else { + if getDaysInMonth(sy, sm) == sd { + sd = 30 + } + if ed > 30 { + if sd < 30 { + em++ + ed = 1 + } else { + ed = 30 + } + } + } + return newNumberFormulaArg(float64(360*(ey-sy) + 30*(em-sm) + (ed - sd))) +} + // ISOWEEKNUM function returns the ISO week number of a supplied date. The // syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index c7333c5..c9891a3 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1476,6 +1476,15 @@ func TestCalcCellValue(t *testing.T) { "=DAYS(2,1)": "1", "=DAYS(INT(2),INT(1))": "1", "=DAYS(\"02/02/2015\",\"01/01/2015\")": "32", + // DAYS360 + "=DAYS360(\"10/10/2020\", \"10/10/2020\")": "0", + "=DAYS360(\"01/30/1999\", \"02/28/1999\")": "28", + "=DAYS360(\"01/31/1999\", \"02/28/1999\")": "28", + "=DAYS360(\"12/12/1999\", \"08/31/1999\")": "-101", + "=DAYS360(\"12/12/1999\", \"11/30/1999\")": "-12", + "=DAYS360(\"12/12/1999\", \"11/30/1999\",TRUE)": "-12", + "=DAYS360(\"01/31/1999\", \"03/31/1999\",TRUE)": "60", + "=DAYS360(\"01/31/1999\", \"03/31/2000\",FALSE)": "420", // EDATE "=EDATE(\"01/01/2021\",-1)": "44166", "=EDATE(\"01/31/2020\",1)": "43890", @@ -3447,6 +3456,12 @@ func TestCalcCellValue(t *testing.T) { "=DAYS(0,\"\")": "#VALUE!", "=DAYS(NA(),0)": "#VALUE!", "=DAYS(0,NA())": "#VALUE!", + // DAYS360 + "=DAYS360(\"12/12/1999\")": "DAYS360 requires at least 2 arguments", + "=DAYS360(\"12/12/1999\", \"11/30/1999\",TRUE,\"\")": "DAYS360 requires at most 3 arguments", + "=DAYS360(\"12/12/1999\", \"11/30/1999\",\"\")": "strconv.ParseBool: parsing \"\": invalid syntax", + "=DAYS360(\"12/12/1999\", \"\")": "#VALUE!", + "=DAYS360(\"\", \"11/30/1999\")": "#VALUE!", // EDATE "=EDATE()": "EDATE requires 2 arguments", "=EDATE(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", |