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
orMM-DD-YYYY
orjavascript date notation
orJavascript Date Object
- For Math functions each
Column
in excel can be considered anArray
- 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)
"1/1/2020"
Output: DAY
Converts a serial number to a day of the month
Syntax
DAY(give_Date)
Example
DAY("2020-12-31")
31
Output: DAYS
Returns the number of days between two dates
Syntax
DAYS(startDate, endDate)
Example
DAYS("2020-01-01", "2020-12-31")
366
Output: 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)
"4/30/2020"
Output: HOUR
Converts a serial number to an hour
Syntax
HOUR(date_time)
Example
HOUR('March 13, 08 04:20')
4
Output: MINUTE
Converts a serial number to a minute
Syntax
MINUTE(date_time)
Example
MINUTE('March 13, 08 04:20')
20
Output: MONTH
Converts a serial number to a month
Syntax
MONTH(give_Date)
Example
MONTH("2020-12-31")
11
Output: 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"])
1
Output: 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"])
2
Output: NOW
Returns the serial number of the current date and time
Syntax
NOW()
Example
NOW()
Current Time
Output: SECOND
Converts a serial number to a second
Syntax
SECOND(date_time)
Example
SECOND('March 13, 08 04:20:20')
20
Output: TIME
Returns the serial number of a particular time
Syntax
TIME(hour, minute, second = 0, _type = 'string' || 'number')
Example
TIME(13, 2, 3)
"02:02:03 PM"
Output: WEEKDAY
Converts a serial number to a day of the week
Syntax
WEEKDAY(give_Date)
Example
WEEKDAY("2020-12-31")
4
Output: 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")
53
Output: 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)])
"5/1/2020"
Output: 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)])
"5/1/2020"
Output: YEAR
Converts a serial number to a year
Syntax
YEAR(give_Date)
Example
YEAR("2020-12-31")
2020
Output: Math Functions
SUM
Adds its arguments
Syntax
SUM(sumRangeArray = [])
Example
SUM([1, 2, 3, 4, 5])
15
Output: 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])
9
Output: 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'""])
11
Output: COUNT
Counts how many numbers are in the list of arguments
Syntax
COUNT(countArray = [])
Example
COUNT([1, 2, 3, 4, 4, 4, 5, 6])
8
Output: 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")
3
Output: 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'""])
3
Output: PI
Returns the value of pi
Syntax
PI()
Example
PI()