xl-formulas v1.1.5
xl-formulas
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
Install
npm install xl-formulas
Usage
let xl = require('xl-formulas')
Example:
let someday = xl.DAY("2020-12-31");
output: 31
Important Notes:
- 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'
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: new Date()
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
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
" console.log(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
" console.log(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()