diff options
| -rw-r--r-- | calc.go | 54 | ||||
| -rw-r--r-- | calc_test.go | 11 |
2 files changed, 64 insertions, 1 deletions
@@ -435,6 +435,7 @@ type formulaFuncs struct { // EFFECT // EDATE // ENCODEURL +// EOMONTH // ERF // ERF.PRECISE // ERFC @@ -12394,7 +12395,9 @@ func (fn *formulaFuncs) EDATE(argsList *list.List) formulaArg { if month.Number > 11 { y += int(math.Floor(float64(m) / 12)) } - m = int(math.Mod(float64(m), 12)) + if m = m % 12; m < 0 { + m += 12 + } if d > 28 { if days := getDaysInMonth(y, m); d > days { d = days @@ -12404,6 +12407,55 @@ func (fn *formulaFuncs) EDATE(argsList *list.List) formulaArg { return newNumberFormulaArg(result) } +// EOMONTH function returns the last day of the month, that is a specified +// number of months before or after an initial supplied start date. The syntax +// of the function is: +// +// EOMONTH(start_date,months) +// +func (fn *formulaFuncs) EOMONTH(argsList *list.List) formulaArg { + if argsList.Len() != 2 { + return newErrorFormulaArg(formulaErrorVALUE, "EOMONTH requires 2 arguments") + } + date := argsList.Front().Value.(formulaArg) + num := date.ToNumber() + var dateTime time.Time + if num.Type != ArgNumber { + dateString := strings.ToLower(date.Value()) + if !isDateOnlyFmt(dateString) { + if _, _, _, _, _, err := strToTime(dateString); err.Type == ArgError { + return err + } + } + y, m, d, _, err := strToDate(dateString) + if err.Type == ArgError { + return err + } + dateTime = time.Date(y, time.Month(m), d, 0, 0, 0, 0, time.Now().Location()) + } else { + if num.Number < 0 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + dateTime = timeFromExcelTime(num.Number, false) + } + months := argsList.Back().Value.(formulaArg).ToNumber() + if months.Type != ArgNumber { + return months + } + y, m := dateTime.Year(), int(dateTime.Month())+int(months.Number)-1 + if m < 0 { + y -= int(math.Ceil(-1 * float64(m) / 12)) + } + if m > 11 { + y += int(math.Floor(float64(m) / 12)) + } + if m = m % 12; m < 0 { + m += 12 + } + result, _ := timeToExcelTime(time.Date(y, time.Month(m+1), getDaysInMonth(y, m+1), 0, 0, 0, 0, time.UTC), false) + return newNumberFormulaArg(result) +} + // HOUR function returns an integer representing the hour component of a // supplied Excel time. The syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index ab282ca..6c2c649 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1479,6 +1479,11 @@ func TestCalcCellValue(t *testing.T) { "=EDATE(\"01/01/2021\",-1)": "44166", "=EDATE(\"01/31/2020\",1)": "43890", "=EDATE(\"01/29/2020\",12)": "44225", + "=EDATE(\"6/12/2021\",-14)": "43933", + // EOMONTH + "=EOMONTH(\"01/01/2021\",-1)": "44196", + "=EOMONTH(\"01/29/2020\",12)": "44227", + "=EOMONTH(\"01/12/2021\",-18)": "43677", // HOUR "=HOUR(1)": "0", "=HOUR(43543.5032060185)": "12", @@ -3447,6 +3452,12 @@ func TestCalcCellValue(t *testing.T) { "=EDATE(-1,0)": "#NUM!", "=EDATE(\"\",0)": "#VALUE!", "=EDATE(\"January 25, 100\",0)": "#VALUE!", + // EOMONTH + "=EOMONTH()": "EOMONTH requires 2 arguments", + "=EOMONTH(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=EOMONTH(-1,0)": "#NUM!", + "=EOMONTH(\"\",0)": "#VALUE!", + "=EOMONTH(\"January 25, 100\",0)": "#VALUE!", // HOUR "=HOUR()": "HOUR requires exactly 1 argument", "=HOUR(-1)": "HOUR only accepts positive argument", |