xl-formula

Excel like Formulas and syntax for Javascript

Objective

The main objective of this Library is to provide most used functions in Excel with similar syntax.

Notable Functions:

The most used math functions like SUMIF, SUMIFS, COUNTIF, COUNTIFS and the Date functions DAYS, NETWORKDAYS, WORKDAY, EOMONTH

List of All Functions:

Date's	Time's	Spl Day Funcs	Business Days	Sum's	Count's	Math
DATE	TIME	DAYS	WORKDAY	SUM	COUNT	PI
DAY	HOUR	EOMONTH	WORKDAYINTL	SUMIF	COUNTIF
MONTH	MINUTE	WEEKDAY	NETWORKDAYS	SUMIFS	COUNTIFS
YEAR	SECOND	WEEKNUM	NETWORKDAYSINTL
NOW

Install

npm install xl-formula

Usage

let xl = require('xl-formula')

Example: let someday = xl.DAY("2020-12-31"); output: 31

Important Notes:

• Syntax is mostly identical to Excel formulas
• Dates can be in the format of YYYY-MM-DD or MM-DD-YYYY or javascript date notation or Javascript Date Object
• For Math functions each Column in excel can be considered an Array
• All the functions are in UPPER CASE

Available Functions

Date Functions

DATE

Returns the serial number of a particular date

Syntax

DATE(year, month, day)

Example

DATE(2020,0,1)

Output: "1/1/2020"

---

DAY

Converts a serial number to a day of the month

Syntax

DAY(give_Date)

Example

DAY("2020-12-31")

Output: 31

---

DAYS

Returns the number of days between two dates

Syntax

DAYS(startDate, endDate)

Example

DAYS("2020-01-01", "2020-12-31")

Output: 366

---

EOMONTH

Returns the serial number of the last day of the month before or after a specified number of months

Syntax

EOMONTH(startDate, months)

Example

EOMONTH("2020-03-03",1)

Output: "4/30/2020"

---

HOUR

Converts a serial number to an hour

Syntax

HOUR(date_time)

Example

HOUR('March 13, 08 04:20')

Output: 4

---

MINUTE

Converts a serial number to a minute

Syntax

MINUTE(date_time)

Example

MINUTE('March 13, 08 04:20')

Output: 20

---

MONTH

Converts a serial number to a month

Syntax

MONTH(give_Date)

Example

MONTH("2020-12-31")

Output: 11

---

NETWORKDAYS

Returns the number of whole workdays between two dates

Syntax

NETWORKDAYS(startDate, endDate, holidays = [])

Example

NETWORKDAYS("2020-04-21", "2020-04-25", ["2020-04-21", "2020-04-22", "2020-04-24"])

Output: 1

---

NETWORKDAYSINTL

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days

Syntax

NETWORKDAYSINTL(startDate, endDate, holidays = [])

Example

NETWORKDAYSINTL("2020-04-21", "2020-04-25", 12, ["2020-04-21", "2020-04-22", "2020-04-24"])

Output: 2

---

NOW

Returns the serial number of the current date and time

Syntax

NOW()

Example

NOW()

Output: Current Time

---

SECOND

Converts a serial number to a second

Syntax

SECOND(date_time)

Example

SECOND('March 13, 08 04:20:20')

Output: 20

---

TIME

Returns the serial number of a particular time

Syntax

TIME(hour, minute, second = 0, _type = 'string' || 'number')

Example

TIME(13, 2, 3)

Output: "02:02:03 PM"

---

WEEKDAY

Converts a serial number to a day of the week

Syntax

WEEKDAY(give_Date)

Example

WEEKDAY("2020-12-31")

Output: 4

---

WEEKNUM

Converts a serial number to a number representing where the week falls numerically with a year

Syntax

WEEKNUM(give_Date)

Example

WEEKNUM("2020-12-31")

Output: 53

---

WORKDAY

Returns the serial number of the date before or after a specified number of workdays

Syntax

WORKDAY(startDate, no_Of_Days, holidays = [])

Example

WORKDAY("2020-04-21", 10, [new Date(2020, 3, 22), new Date(2020, 3, 21)])

Output: "5/1/2020"

---

WORKDAYINTL

Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days

Syntax

WORKDAYINTL(startDate, no_Of_Days, holidays = [])

Example

WORKDAYINTL("2020-04-21", 10, 1,[new Date(2020, 3, 22), new Date(2020, 3, 21)])

Output: "5/1/2020"

---

YEAR

Converts a serial number to a year

Syntax

YEAR(give_Date)

Example

YEAR("2020-12-31")

Output: 2020

---

Math Functions

SUM

Adds its arguments

Syntax

SUM(sumRangeArray = [])

Example

SUM([1, 2, 3, 4, 5])

Output: 15

---

SUMIF

Adds the cells specified by a given criteria

Syntax

SUMIF(rangeArray = [], criteria, sumRangeArray = [])

Example

SUMIF(["a", "b", "a", "c", "d", "f", "a", "g"], "a", [1, 2, 3, 4, 4, 4, 5, 6])

Output: 9

---

SUMIFS

Adds the cells in a range that meet multiple criteria

Syntax

SUMIFS(sumRange = [], criterias = [])

Example

SUMIFS([1, 2, 3, 4, 4, 4, 5, 6], [
[""apple"", ""apple"", ""apple"", ""cheese"", ""apple"", ""orange"", ""apple"", ""grape""], ""=='apple'"",
[1, 2, 3, 3, 3, 4, 5, 6], "">=2"",
[""arun"", ""babu"", ""arun"", ""babu"", ""dheera"", ""adhi"", ""vaidhi"", ""vaidhi""], ""!=='arun'""])

Output: 11

---

COUNT

Counts how many numbers are in the list of arguments

Syntax

COUNT(countArray = [])

Example

COUNT([1, 2, 3, 4, 4, 4, 5, 6])

Output: 8

---

COUNTIF

Counts the number of cells within a range that meet the given criteria

Syntax

COUNTIF = (rangeArray = [], criteria)

Example

COUNTIF(["a", "b", "a", "c", "d", "f", "a", "g"], "a")

Output: 3

---

COUNTIFS

Counts the number of cells within a range that meet multiple criteria

Syntax

COUNTIFS(criterias = [])

Example

COUNTIFS([
[""apple"", ""apple"", ""apple"", ""cheese"", ""apple"", ""orange"", ""apple"", ""grape""], ""=='apple'"",
[1, 2, 3, 3, 3, 4, 5, 6], "">=2"",
[""arun"", ""babu"", ""arun"", ""babu"", ""dheera"", ""adhi"", ""vaidhi"", ""vaidhi""], ""!=='arun'""])

Output: 3

---

PI

Returns the value of pi

Syntax

PI()

Example

PI()

Output: 3.141592654

---