xl-formula

Excel like Formulas and syntax for Javascript

Usage no npm install needed!

<script type="module">
  import xlFormula from 'https://cdn.skypack.dev/xl-formula';
</script>

README

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