xl-formula v1.0.3
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-DDorMM-DD-YYYYorjavascript date notationorJavascript Date Object - For Math functions each
Columnin 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)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()