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