New formula function AND (#701) and update doc for the NewSheet (#714)

3 files changed, 67 insertions, 13 deletions

diff --git a/calc.go b/calc.go
index 111bc60..bc3b6e9 100644
--- a/calc.go
+++ b/calc.go
@@ -116,11 +116,11 @@ var tokenPriority = map[string]int{
 //
 //    ABS, ACOS, ACOSH, ACOT, ACOTH, AND, ARABIC, ASIN, ASINH, ATAN2, ATANH,
 //    BASE, CEILING, CEILING.MATH, CEILING.PRECISE, COMBIN, COMBINA, COS,
-//    COSH, COT, COTH, COUNTA, CSC, CSCH, DECIMAL, DEGREES, EVEN, EXP, FACT,
-//    FACTDOUBLE, FLOOR, FLOOR.MATH, FLOOR.PRECISE, GCD, INT, ISBLANK, ISERR,
-//    ISERROR, ISEVEN, ISNA, ISNONTEXT, ISNUMBER, ISO.CEILING, ISODD, LCM,
-//    LN, LOG, LOG10, MDETERM, MEDIAN, MOD, MROUND, MULTINOMIAL, MUNIT, NA,
-//    ODD, OR, PI, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANDBETWEEN,
+//    COSH, COT, COTH, COUNTA, CSC, CSCH, DATE, DECIMAL, DEGREES, EVEN, EXP,
+//    FACT, FACTDOUBLE, FLOOR, FLOOR.MATH, FLOOR.PRECISE, GCD, INT, ISBLANK,
+//    ISERR, ISERROR, ISEVEN, ISNA, ISNONTEXT, ISNUMBER, ISO.CEILING, ISODD,
+//    LCM, LN, LOG, LOG10, MDETERM, MEDIAN, MOD, MROUND, MULTINOMIAL, MUNIT,
+//    NA, ODD, OR, PI, POWER, PRODUCT, QUOTIENT, RADIANS, RAND, RANDBETWEEN,
 //    ROUND, ROUNDDOWN, ROUNDUP, SEC, SECH, SIGN, SIN, SINH, SQRT, SQRTPI,
 //    SUM, SUMIF, SUMSQ, TAN, TANH, TRUNC
 //
@@ -3308,3 +3308,45 @@ func (fn *formulaFuncs) OR(argsList *list.List) (result string, err error) {
 	result = strings.ToUpper(strconv.FormatBool(or))
 	return
 }
+
+// Date and Time Functions
+
+// DATE returns a date, from a user-supplied year, month and day.
+func (fn *formulaFuncs) DATE(argsList *list.List) (result string, err error) {
+	if argsList.Len() != 3 {
+		err = errors.New("DATE requires 3 number arguments")
+		return
+	}
+	var year, month, day int
+	if year, err = strconv.Atoi(argsList.Front().Value.(formulaArg).String); err != nil {
+		err = errors.New("DATE requires 3 number arguments")
+		return
+	}
+	if month, err = strconv.Atoi(argsList.Front().Next().Value.(formulaArg).String); err != nil {
+		err = errors.New("DATE requires 3 number arguments")
+		return
+	}
+	if day, err = strconv.Atoi(argsList.Back().Value.(formulaArg).String); err != nil {
+		err = errors.New("DATE requires 3 number arguments")
+		return
+	}
+	d := makeDate(year, time.Month(month), day)
+	result = timeFromExcelTime(daysBetween(excelMinTime1900.Unix(), d)+1, false).String()
+	return
+}
+
+// makeDate return date as a Unix time, the number of seconds elapsed since
+// January 1, 1970 UTC.
+func makeDate(y int, m time.Month, d int) int64 {
+	if y == 1900 && int(m) <= 2 {
+		d--
+	}
+	date := time.Date(y, m, d, 0, 0, 0, 0, time.UTC)
+	return date.Unix()
+}
+
+// daysBetween return time interval of the given start timestamp and end
+// timestamp.
+func daysBetween(startDate, endDate int64) float64 {
+	return float64(int(0.5 + float64((endDate-startDate)/86400)))
+}
diff --git a/calc_test.go b/calc_test.go
index 02b5161..c6a7dbc 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -407,14 +407,14 @@ func TestCalcCellValue(t *testing.T) {
 		"=TRUNC(99.999,-1)":  "90",
 		"=TRUNC(-99.999,2)":  "-99.99",
 		"=TRUNC(-99.999,-1)": "-90",
-		// Statistical functions
+		// Statistical Functions
 		// COUNTA
 		`=COUNTA()`:                       "0",
 		`=COUNTA(A1:A5,B2:B5,"text",1,2)`: "8",
 		// MEDIAN
 		"=MEDIAN(A1:A5,12)": "2",
 		"=MEDIAN(A1:A5)":    "1.5",
-		// Information functions
+		// Information Functions
 		// ISBLANK
 		"=ISBLANK(A1)": "FALSE",
 		"=ISBLANK(A5)": "TRUE",
@@ -443,6 +443,7 @@ func TestCalcCellValue(t *testing.T) {
 		"=ISODD(A2)": "FALSE",
 		// NA
 		"=NA()": "#N/A",
+		// Logical Functions
 		// AND
 		"=AND(0)":               "FALSE",
 		"=AND(1)":               "TRUE",
@@ -457,6 +458,10 @@ func TestCalcCellValue(t *testing.T) {
 		"=OR(0)":       "FALSE",
 		"=OR(1=2,2=2)": "TRUE",
 		"=OR(1=2,2=3)": "FALSE",
+		// Date and Time Functions
+		// 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",
 	}
 	for formula, expected := range mathCalc {
 		f := prepareData()
@@ -732,10 +737,10 @@ func TestCalcCellValue(t *testing.T) {
 		"=TRUNC()":      "TRUNC requires at least 1 argument",
 		`=TRUNC("X")`:   "#VALUE!",
 		`=TRUNC(1,"X")`: "#VALUE!",
-		// Statistical functions
+		// Statistical Functions
 		// MEDIAN
 		"=MEDIAN()": "MEDIAN requires at least 1 argument",
-		// Information functions
+		// Information Functions
 		// ISBLANK
 		"=ISBLANK(A1,A2)": "ISBLANK requires 1 argument",
 		// ISERR
@@ -756,6 +761,7 @@ func TestCalcCellValue(t *testing.T) {
 		`=ISODD("text")`: "#VALUE!",
 		// NA
 		"=NA(1)": "NA accepts no arguments",
+		// Logical Functions
 		// AND
 		`=AND("text")`: "#VALUE!",
 		`=AND(A1:B1)`:  "#VALUE!",
@@ -766,6 +772,12 @@ func TestCalcCellValue(t *testing.T) {
 		`=OR(A1:B1)`:                             "#VALUE!",
 		"=OR()":                                  "OR requires at least 1 argument",
 		"=OR(1" + strings.Repeat(",1", 30) + ")": "OR accepts at most 30 arguments",
+		// Date and Time Functions
+		// DATE
+		"=DATE()":               "DATE requires 3 number arguments",
+		`=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",
 	}
 	for formula, expected := range mathCalcError {
 		f := prepareData()
diff --git a/sheet.go b/sheet.go
index 44067fe..8da2e89 100644
--- a/sheet.go
+++ b/sheet.go
@@ -31,10 +31,10 @@ import (
 	"github.com/mohae/deepcopy"
 )
 
-// NewSheet provides function to create a new sheet by given worksheet name.
-// When creating a new spreadsheet file, the default worksheet will be
-// created. Returns the number of sheets in the workbook (file) after
-// appending the new sheet.
+// NewSheet provides the function to create a new sheet by given a worksheet
+// name and returns the index of the sheets in the workbook
+// (spreadsheet) after it appended. Note that when creating a new spreadsheet
+// file, the default worksheet named `Sheet1` will be created.
 func (f *File) NewSheet(name string) int {
 	// Check if the worksheet already exists
 	index := f.GetSheetIndex(name)