name:
src/Formulas/Date.ts
-rw-r--r--
39226
1/// <reference path="../../node_modules/moment/moment.d.ts"/>
2import * as moment from "moment";
3import {
4 ArgsChecker
5} from "../Utilities/ArgsChecker";
6import {
7 TypeConverter
8} from "../Utilities/TypeConverter";
9import {
10 NumError,
11 ValueError,
12 RefError
13} from "../Errors";
14
15/**
16 * Converts a provided year, month, and day into a date.
17 * @param year - The year component of the date.
18 * @param month - The month component of the date.
19 * @param day - The day component of the date.
20 * @returns {number} newly created date.
21 * @constructor
22 */
23let DATE = function (year, month, day) : number {
24 const FIRST_YEAR = 1900;
25 ArgsChecker.checkLength(arguments, 3, "DATE");
26 year = Math.abs(Math.floor(TypeConverter.firstValueAsNumber(year))); // No negative values for year
27 month = Math.floor(TypeConverter.firstValueAsNumber(month)) - 1; // Months are between 0 and 11.
28 day = Math.floor(TypeConverter.firstValueAsNumber(day)) - 1; // Days are also zero-indexed.
29 let m = moment.utc(TypeConverter.ORIGIN_MOMENT)
30 .add(2, "days")
31 .add(year < FIRST_YEAR ? year : year - FIRST_YEAR, 'years') // If the value is less than 1900, assume 1900 as start index for year
32 .add(month, 'months')
33 .add(day, 'days');
34 let dateAsNumber = TypeConverter.momentToDayNumber(m);
35 if (dateAsNumber < 0) {
36 throw new NumError("DATE evaluates to an out of range value " + dateAsNumber
37 + ". It should be greater than or equal to 0.");
38 }
39 return dateAsNumber;
40};
41
42/**
43 * Converts a provided date string in a known format to a date value.
44 * @param dateString - The string representing the date. Understood formats include any date format which is
45 * normally auto-converted when entered, without quotation marks, directly into a cell. Understood formats may depend on
46 * region and language settings.
47 * @returns {number} of days since 1900/1/1, inclusively.
48 * @constructor
49 */
50let DATEVALUE = function (dateString) : number {
51 ArgsChecker.checkLength(arguments, 1, "DATEVALUE");
52 dateString = TypeConverter.firstValueAsString(dateString);
53 let dateAsNumber;
54 try {
55 dateAsNumber = TypeConverter.stringToDateNumber(dateString);
56 } catch (e) {
57 throw new ValueError("DATEVALUE parameter '" + dateString + "' cannot be parsed to date/time.");
58 }
59
60 // If we've not been able to parse the date by now, then we cannot parse it at all.
61 return dateAsNumber;
62};
63
64
65/**
66 * Returns a date a specified number of months before or after another date.
67 * @param startDate - The date from which to calculate the result.
68 * @param months - The number of months before (negative) or after (positive) start_date to calculate.
69 * @returns {number} date a specified number of months before or after another date
70 * @constructor
71 */
72let EDATE = function (startDate, months) : number {
73 ArgsChecker.checkLength(arguments, 2, "EDATE");
74 let startDateNumber = TypeConverter.firstValueAsDateNumber(startDate, true); // tell firstValueAsDateNumber to coerce boolean
75 if (startDateNumber < 0) {
76 throw new NumError("Function EDATE parameter 1 value is " + startDateNumber+ ". It should be greater than or equal to 0.");
77 }
78 months = Math.floor(TypeConverter.firstValueAsNumber(months));
79 // While momentToDayNumber will return an inclusive count of days since 1900/1/1, moment.Moment.add assumes exclusive
80 // count of days.
81 return TypeConverter.momentToDayNumber(moment.utc(TypeConverter.ORIGIN_MOMENT).add(startDateNumber, "days").add(months, "months"));
82};
83
84
85/**
86 * Returns a date representing the last day of a month which falls a specified number of months before or after another
87 * date.
88 * @param startDate - The date from which to calculate the the result.
89 * @param months - The number of months before (negative) or after (positive) start_date to consider. The last
90 * calendar day of the calculated month is returned.
91 * @returns {number} the last day of a month
92 * @constructor
93 */
94let EOMONTH = function (startDate, months) : number {
95 ArgsChecker.checkLength(arguments, 2, "EOMONTH");
96 let startDateNumber = TypeConverter.firstValueAsDateNumber(startDate, true); // tell firstValueAsDateNumber to coerce boolean
97 if (startDateNumber < 0) {
98 throw new NumError("Function EOMONTH parameter 1 value is " + startDateNumber + ". It should be greater than or equal to 0.");
99 }
100 months = Math.floor(TypeConverter.firstValueAsNumber(months));
101 return TypeConverter.momentToDayNumber(moment.utc(TypeConverter.ORIGIN_MOMENT)
102 .add(startDateNumber, "days")
103 .add(months, "months")
104 .endOf("month"));
105};
106
107
108/**
109 * Returns the day of the month that a specific date falls on, in numeric format.
110 * @param date - The date from which to extract the day. Must be a reference to a cell containing a date, a
111 * function returning a date type, or a number.
112 * @returns {number} day of the month
113 * @constructor
114 */
115let DAY = function (date) : number {
116 ArgsChecker.checkLength(arguments, 1, "DAY");
117 date = TypeConverter.firstValueAsDateNumber(date, true); // tell firstValueAsDateNumber to coerce boolean
118 if (date < 0) {
119 throw new NumError("Function DAY parameter 1 value is " + date + ". It should be greater than or equal to 0.");
120 }
121 return TypeConverter.numberToMoment(date).date();
122};
123
124
125/**
126 * Returns the number of days between two dates.
127 * @param endDate most recently occurring
128 * @param startDate not most recently occurring
129 * @returns {number} of days between start_date and end_date
130 * @constructor
131 */
132let DAYS = function (endDate, startDate) : number {
133 ArgsChecker.checkLength(arguments, 2, "DAYS");
134 endDate = TypeConverter.firstValueAsDateNumber(endDate, true); // tell firstValueAsDateNumber to coerce boolean
135 startDate = TypeConverter.firstValueAsDateNumber(startDate, true); // tell firstValueAsDateNumber to coerce boolean
136 return endDate - startDate;
137};
138
139
140/**
141 * Returns the difference between two days based on the 360 day year used in some financial interest calculations.
142 * @param startDate - The start date to consider in the calculation. Must be a reference to a cell containing
143 * a date, a function returning a date type, or a number.
144 * @param endDate - The end date to consider in the calculation. Must be a reference to a cell containing a
145 * date, a function returning a date type, or a number.
146 * @param methodToUse - [ OPTIONAL - 0 by default ] - An indicator of what day count method to use.
147 * 0 indicates the US method - Under the US method, if start_date is the last day of a month, the day of month of
148 * start_date is changed to 30 for the purposes of the calculation. Furthermore if end_date is the last day of a month
149 * and the day of the month of start_date is earlier than the 30th, end_date is changed to the first day of the month
150 * following end_date, otherwise the day of month of end_date is changed to 30.
151 * Any other value indicates the European method - Under the European method, any start_date or end_date that falls on
152 * the 31st of a month has its day of month changed to 30.
153 * @returns {number} of days between two dates
154 * @constructor
155 */
156let DAYS360 = function (startDate, endDate, methodToUse?) : number {
157 ArgsChecker.checkLengthWithin(arguments, 2, 3, "DAYS360");
158 startDate = TypeConverter.numberToMoment(TypeConverter.firstValueAsDateNumber(startDate, true)); // tell firstValueAsDateNumber to coerce boolean
159 endDate = TypeConverter.numberToMoment(TypeConverter.firstValueAsDateNumber(endDate, true)); // tell firstValueAsDateNumber to coerce boolean
160 methodToUse = methodToUse ? TypeConverter.firstValueAsBoolean(methodToUse) : false;
161 let smd = 31;
162 let emd = 31;
163 let sd = startDate.date();
164 let ed = endDate.date();
165 if (methodToUse) {
166 sd = (sd === 31) ? 30 : sd;
167 ed = (ed === 31) ? 30 : ed;
168 }
169 else {
170 if (startDate.month() === 1) {
171 smd = startDate.daysInMonth();
172 }
173 if (endDate.month() === 1) {
174 emd = endDate.daysInMonth();
175 }
176 sd = (sd === smd) ? 30 : sd;
177 if (sd === 30 || sd === smd) {
178 ed = (ed === emd) ? 30 : ed;
179 }
180 }
181 return 360 * (endDate.year() - startDate.year()) + 30 * (endDate.month() - startDate.month()) + (ed - sd);
182};
183
184
185/**
186 * Returns the month of the year a specific date falls in, in numeric format.
187 * @param date - The date from which to extract the month. Must be a reference to a cell containing a date, a
188 * function returning a date type, or a number.
189 * @returns {number} month of the year that the input date falls on.
190 * @constructor
191 */
192let MONTH = function (date) : number {
193 ArgsChecker.checkLength(arguments, 1, "MONTH");
194 date = TypeConverter.firstValueAsDateNumber(date, true); // tell firstValueAsDateNumber to coerce boolean
195 if (date < 0) {
196 throw new NumError("Function MONTH parameter 1 value is " + date + ". It should be greater than or equal to 0.");
197 }
198 return TypeConverter.numberToMoment(date).month() + 1;
199};
200
201
202/**
203 * Returns the year specified by a given date.
204 * @param date - The date from which to calculate the year. Must be a cell reference to a cell containing a
205 * date, a function returning a date type, or a number.
206 * @returns {number} year of the input date
207 * @constructor
208 */
209let YEAR = function (date) : number {
210 ArgsChecker.checkLength(arguments, 1, "YEAR");
211 date = TypeConverter.firstValueAsDateNumber(date, true); // tell firstValueAsDateNumber to coerce boolean
212 if (date < 0) {
213 throw new NumError("Function YEAR parameter 1 value is " + date + ". It should be greater than or equal to 0.");
214 }
215 return TypeConverter.numberToMoment(date).year();
216};
217
218
219/**
220 * Returns a number representing the day of the week of the date provided.
221 * @param date - The date for which to determine the day of the week. Must be a reference to a cell containing
222 * a date, a function returning a date type, or a number.
223 * @param offsetType - [ OPTIONAL - 1 by default ] - A number indicating which numbering system to use to represent
224 * weekdays. By default counts starting with Sunday = 1. If type is 1, days are counted from Sunday and the value of
225 * Sunday is 1, therefore the value of Saturday is 7. If type is 2, days are counted from Monday and the value of Monday
226 * is 1, therefore the value of Sunday is 7. If type is 3, days are counted from Monday and the value of Monday is 0,
227 * therefore the value of Sunday is 6.
228 * @returns {number} day of week
229 * @constructor
230 */
231let WEEKDAY = function (date, offsetType?) : number {
232 ArgsChecker.checkLengthWithin(arguments, 1, 2, "WEEKDAY");
233 date = TypeConverter.firstValueAsDateNumber(date, true); // tell firstValueAsDateNumber to coerce boolean
234 offsetType = offsetType ? TypeConverter.firstValueAsNumber(offsetType) : 1;
235 if (date < 0) {
236 throw new NumError("Function WEEKDAY parameter 1 value is " + date + ". It should be greater than or equal to 0.");
237 }
238 let day = TypeConverter.numberToMoment(date).day();
239 if (offsetType === 1) {
240 return day + 1;
241 } else if (offsetType === 2) {
242 if (day === 0) {
243 return 7;
244 }
245 return day;
246 } else if (offsetType === 3) {
247 if (day === 0) {
248 return 6;
249 }
250 return day - 1;
251 } else {
252 throw new NumError("Function WEEKDAY parameter 2 value " + day + " is out of range.");
253 }
254};
255
256
257/**
258 * Returns a number representing the week of the year where the provided date falls. When inputting the date, it is best
259 * to use the DATE function, as text values may return errors.
260 *
261 * Behind the scenes, there are two week numbering "systems" used for this function: System 1 - The first week of the
262 * year is considered to be the week containing January 1, which is numbered week 1. System 2 - The first week of the
263 * year is considered to be the week containing the first Thursday of the year, which is numbered as week 1. System 2 is
264 * the approach specified in ISO 8601, also known as the European system for numbering weeks.
265 *
266 * @param date - The date for which to determine the week number. Must be a reference to a cell containing a
267 * date, a function returning a date type, or a number.
268 * @param shiftType - [ OPTIONAL - default is 1 ] - A number representing the day that a week starts on as well as
269 * the system used for determining the first week of the year (1=Sunday, 2=Monday).
270 * @returns {number} representing week number of year.
271 * @constructor
272 */
273let WEEKNUM = function (date, shiftType?) : number {
274 // Given a moment, an array of days of the week for shifting, will calculate the week number.
275 function calculateWeekNum(dm : moment.Moment, shifterArray : Array<number>) : number {
276 let startOfYear = moment.utc(dm).startOf("year");
277 let weeksCount = 1;
278 let d = moment.utc(dm).startOf("year").add(6 - shifterArray[startOfYear.day()], "days");
279 while (d.isBefore(dm)) {
280 d.add(7, "days");
281 weeksCount++;
282 }
283 return weeksCount;
284 }
285
286 ArgsChecker.checkLengthWithin(arguments, 1, 2, "WEEKNUM");
287 date = TypeConverter.firstValueAsDateNumber(date, true); // tell firstValueAsDateNumber to coerce boolean
288 shiftType = shiftType ? TypeConverter.firstValueAsNumber(shiftType) : 1;
289 if (date < 0) {
290 throw new NumError("Function YEAR parameter 1 value is " + date + ". It should be greater than or equal to 0.");
291 }
292 let dm = TypeConverter.numberToMoment(date);
293 let week = dm.week();
294 let dayOfWeek = dm.day(); // between 1 and 7, inclusively
295 if (shiftType === 1) {
296 // If this weekYear is not the same as the year, then we're technically in "week 53"
297 // See https://momentjs.com/docs/#/get-set/week-year/ for more info.
298 if (dm.weekYear() !== dm.year()) {
299 week = dm.weeksInYear() + 1;
300 }
301 return week;
302 } else if (shiftType === 2 || shiftType === 11) {
303 if (dm.weekYear() !== dm.year()) {
304 week = dm.weeksInYear() + 1;
305 }
306 if (dayOfWeek === 0) { // sunday shift back
307 return week - 1;
308 }
309 return week;
310 } else if (shiftType === 12) {
311 if (dm.weekYear() !== dm.year()) {
312 week = dm.weeksInYear() + 1;
313 }
314 if (dayOfWeek <= 1) { // sunday, monday shift back
315 return week - 1;
316 }
317 return week;
318 } else if (shiftType === 13) {
319 if (dm.weekYear() !== dm.year()) {
320 week = dm.weeksInYear() + 1;
321 }
322 if (dayOfWeek <= 2) { // sunday, monday, tuesday shift back
323 return week - 1;
324 }
325 return week;
326 } else if (shiftType === 14) {
327 return calculateWeekNum(dm, [3, 4, 5, 6, 0, 1, 2]);
328 } else if (shiftType === 15) {
329 return calculateWeekNum(dm, [2, 3, 4, 5, 6, 0, 1]);
330 } else if (shiftType === 16) {
331 return calculateWeekNum(dm, [1, 2, 3, 4, 5, 6, 0]);
332 } else if (shiftType === 17) {
333 return calculateWeekNum(dm, [0, 1, 2, 3, 4, 5, 6]);
334 } else if (shiftType === 21) {
335 return dm.isoWeek();
336 } else {
337 throw new NumError("Function WEEKNUM parameter 2 value " + shiftType + " is out of range.");
338 }
339};
340
341
342/**
343 * Calculates the number of days, months, or years between two dates.
344 * @param startDate - The start date to consider in the calculation. Must be a reference to a cell containing
345 * a DATE, a function returning a DATE type, or a number.
346 * @param endDate - The end date to consider in the calculation. Must be a reference to a cell containing a
347 * DATE, a function returning a DATE type, or a number.
348 * @param unit - A text abbreviation for unit of time. For example,"M" for month. Accepted values are "Y": the
349 * number of whole years between start_date and end_date, "M": the number of whole months between start_date and
350 * end_date, "D": the number of days between start_date and end_date, "MD": the number of days between start_date and
351 * end_date after subtracting whole months, "YM": the number of whole months between start_date and end_date after
352 * subtracting whole years, "YD": the number of days between start_date and end_date, assuming start_date and end_date
353 * were no more than one year apart.
354 * @returns {number} number of days, months, or years between two dates.
355 * @constructor
356 */
357let DATEDIF = function (startDate, endDate, unit) : number {
358 ArgsChecker.checkLength(arguments, 3, "DATEDIF");
359 startDate = TypeConverter.firstValueAsDateNumber(startDate, true);
360 endDate = TypeConverter.firstValueAsDateNumber(endDate, true);
361 unit = TypeConverter.firstValueAsString(unit);
362 let unitClean = unit.toUpperCase();
363 let startMoment = TypeConverter.numberToMoment(startDate);
364 let endMoment = TypeConverter.numberToMoment(endDate);
365
366 if (startDate > endDate) {
367 throw new NumError("Function DATEDIF parameter 1 (" + startDate.toString() +
368 ") should be on or before Function DATEDIF parameter 2 (" + endDate.toString() + ").");
369 }
370
371 if (unitClean === "Y") {
372 return Math.floor(endMoment.diff(startMoment, "years"));
373 } else if (unitClean === "M") {
374 return Math.floor(endMoment.diff(startMoment, "months"));
375 } else if (unitClean === "D") {
376 return endDate - startDate;
377 } else if (unitClean === "MD") {
378 let s = startMoment;
379 while(s.isBefore(endMoment)) {
380 s.add(1, "month");
381 }
382 s.subtract(1, "month");
383 let days = endMoment.diff(s, "days");
384 return s.date() === endMoment.date() ? 0 : days;
385 } else if (unitClean === "YM") {
386 let s = startMoment;
387 while(s.isBefore(endMoment)) {
388 s.add(1, "year");
389 }
390 s.subtract(1, "year");
391 let months = Math.floor(endMoment.diff(s, "months"));
392 return months === 12 ? 0 : months;
393 } else if (unitClean === "YD") {
394 let s = startMoment;
395 while(s.isBefore(endMoment)) {
396 s.add(1, "year");
397 }
398 s.subtract(1, "year");
399 let days = Math.floor(endMoment.diff(s, "days"));
400 return days >= 365 ? 0 : days;
401 } else {
402 throw new NumError("Function DATEDIF parameter 3 value is " + unit +
403 ". It should be one of: 'Y', 'M', 'D', 'MD', 'YM', 'YD'.");
404 }
405};
406
407
408/**
409 * Returns the number of years, including fractional years, between two dates using a specified day count convention.
410 *
411 * Further reading:
412 *
413 * * http://christian-fries.de/blog/files/2013-yearfrac.html
414 *
415 * * http://finmath.net/finmath-lib/
416 *
417 * @param startDate - The start date to consider in the calculation. Must be a reference to a cell
418 * containing a date, a function returning a date type, or a number.
419 * @param endDate - The end date to consider in the calculation. Must be a reference to a cell containing
420 * a date, a function returning a date type, or a number.
421 * @param dayCountConvention - [ OPTIONAL - 0 by default ] - An indicator of what day count method to
422 * use.
423 * @returns {number}the number of years, including fractional years, between two dates
424 * @constructor
425 */
426let YEARFRAC = function (startDate, endDate, dayCountConvention?) : number {
427 ArgsChecker.checkLengthWithin(arguments, 2, 3, "YEARFRAC");
428 startDate = TypeConverter.firstValueAsDateNumber(startDate, true);
429 endDate = TypeConverter.firstValueAsDateNumber(endDate, true);
430 dayCountConvention = dayCountConvention ? TypeConverter.firstValueAsNumber(dayCountConvention) : 0;
431
432 let s = TypeConverter.numberToMoment(startDate);
433 let e = TypeConverter.numberToMoment(endDate);
434 if (e.isBefore(s)) {
435 let me = moment.utc(e);
436 e = moment.utc(s);
437 s = me;
438 }
439 let syear = s.year();
440 let smonth = s.month();
441 let sday = s.date();
442 let eyear = e.year();
443 let emonth = e.month();
444 let eday = e.date();
445
446
447 let feb29Between = function (date1, date2) {
448 // Requires year2 == (year1 + 1) or year2 == year1
449 // Returns TRUE if February 29 is between the two dates (date1 may be February 29), with two possibilities:
450 // year1 is a leap year and date1 <= February 29 of year1
451 // year2 is a leap year and date2 > February 29 of year2
452 let mar1year1 = moment.utc(new Date(date1.year(), 2, 1));
453 if (moment.utc([date1.year()]).isLeapYear() && date1.diff(mar1year1) < 0 && date2.diff(mar1year1) >= 0) {
454 return true;
455 }
456 let mar1year2 = moment.utc(new Date(date2.year(), 2, 1));
457 if (moment.utc([date2.year()]).isLeapYear() && date2.diff(mar1year2) >= 0 && date1.diff(mar1year2) < 0) {
458 return true;
459 }
460 return false;
461 };
462
463 switch (dayCountConvention) {
464 // US (NASD) 30/360
465 case 0:
466 // Note: if eday == 31, it stays 31 if sday < 30
467 if (sday === 31 && eday === 31) {
468 sday = 30;
469 eday = 30;
470 } else if (sday === 31) {
471 sday = 30;
472 } else if (sday === 30 && eday === 31) {
473 eday = 30;
474 } else if (smonth === 1 && emonth === 1 && s.daysInMonth() === sday && e.daysInMonth() === eday) {
475 sday = 30;
476 eday = 30;
477 } else if (smonth === 1 && s.daysInMonth() === sday) {
478 sday = 30;
479 }
480 return Math.abs(((eday + emonth * 30 + eyear * 360) - (sday + smonth * 30 + syear * 360)) / 360);
481 // Actual/actual
482 case 1:
483 let ylength = 365;
484 if (syear === eyear || ((syear + 1) === eyear) && ((smonth > emonth) || ((smonth === emonth) && (sday >= eday)))) {
485 if (syear === eyear && moment.utc([syear]).isLeapYear()) {
486 ylength = 366;
487 } else if (feb29Between(s, e) || (emonth === 1 && eday === 29)) {
488 ylength = 366;
489 }
490 return Math.abs((endDate - startDate) / ylength);
491 } else {
492 let years = (eyear - syear) + 1;
493 let days = moment.utc([eyear+1]).startOf("year").diff(moment.utc([syear]).startOf("year"), 'days');
494 let average = days / years;
495 return Math.abs((endDate - startDate) / average);
496 }
497 // Actual/360
498 case 2:
499 return Math.abs(e.diff(s, 'days') / 360);
500 // Actual/365
501 case 3:
502 return Math.abs(e.diff(s, 'days') / 365);
503 // European 30/360
504 case 4:
505 sday = sday === 31 ? 30 : sday;
506 eday = eday === 31 ? 30 : eday;
507 // Remarkably, do NOT change February 28 or February 29 at ALL
508 return Math.abs(((eday + emonth * 30 + eyear * 360) - (sday + smonth * 30 + syear * 360)) / 360);
509 }
510 throw new NumError("Function YEARFRAC parameter 3 value is " + dayCountConvention + ". Valid values are between 0 and 4 inclusive.");
511};
512
513
514/**
515 * Returns the fraction of a 24-hour day the time represents.
516 * @param timeString - The string that holds the time representation. Eg: "10am", "10:10", "10:10am", "10:10:11",
517 * or "10:10:11am".
518 * @returns {number} representing the fraction of a 24-hour day
519 * @constructor
520 */
521let TIMEVALUE = function (timeString) : number {
522 ArgsChecker.checkLength(arguments, 1, "TIMEVALUE");
523 timeString = TypeConverter.firstValueAsString(timeString);
524 try {
525 return TypeConverter.stringToTimeNumber(timeString);
526 } catch (e) {
527 throw new ValueError("TIMEVALUE parameter '" + timeString + "' cannot be parsed to date/time.");
528 }
529};
530
531const MILLISECONDS_IN_DAY = 86400000;
532
533/**
534 * Returns the hour component of a specific time, in numeric format.
535 * @param time - The time from which to calculate the hour component. Must be a reference to a cell containing
536 * a date/time, a function returning a date/time type, or a number.
537 * @returns {number}
538 * @constructor
539 */
540let HOUR = function (time) : number {
541 ArgsChecker.checkLength(arguments, 1, "HOUR");
542 time = TypeConverter.firstValueAsTimestampNumber(time);
543 if (time % 1 === 0) {
544 return 0;
545 }
546 let m = moment.utc([1900]).add(time * MILLISECONDS_IN_DAY, "milliseconds");
547 return m.hour();
548};
549
550
551/**
552 * Returns the minute component of a specific time, in numeric format.
553 * @param time - The time from which to calculate the minute component. Must be a reference to a cell
554 * containing a date/time, a function returning a date/time type, or a number.
555 * @returns {number} minute of the time passed in.
556 * @constructor
557 */
558let MINUTE = function (time) : number {
559 ArgsChecker.checkLength(arguments, 1, "MINUTE");
560 time = TypeConverter.firstValueAsTimestampNumber(time);
561 if (time % 1 === 0) {
562 return 0;
563 }
564 let m = moment.utc([1900]).add(time * MILLISECONDS_IN_DAY, "milliseconds");
565 return m.minute();
566};
567
568/**
569 * Returns the second component of a specific time, in numeric format.
570 * @param time - The time from which to calculate the second component. Must be a reference to a cell
571 * containing a date/time, a function returning a date/time type, or a number.
572 * @returns {number} second component of a specific time.
573 * @constructor
574 */
575let SECOND = function (time) : number {
576 ArgsChecker.checkLength(arguments, 1, "SECOND");
577 time = TypeConverter.firstValueAsTimestampNumber(time);
578 if (time % 1 === 0) {
579 return 0;
580 }
581 let m = moment.utc([1900]).add(time * MILLISECONDS_IN_DAY, "milliseconds");
582 return m.second();
583};
584
585
586/**
587 * Returns the number of net working days between two provided days.
588 * @param startDate - The start date of the period from which to calculate the number of net working days.
589 * @param endDate - The end date of the period from which to calculate the number of net working days.
590 * @param holidays - [ OPTIONAL ] - A range or array constant containing the date serial numbers to consider
591 * holidays. The values provided within an array for holidays must be date serial number values, as returned by N or
592 * date values, as returned by DATE, DATEVALUE or TO_DATE. Values specified by a range should be standard date values or
593 * date serial numbers.
594 * @returns {number} the number of net working days between two provided dates.
595 * @constructor
596 */
597let NETWORKDAYS = function (startDate, endDate, holidays?) : number {
598 ArgsChecker.checkLengthWithin(arguments, 2, 3, "NETWORKDAYS");
599 startDate = TypeConverter.firstValueAsDateNumber(startDate, true);
600 endDate = TypeConverter.firstValueAsDateNumber(endDate, true);
601 let hasHolidays = (holidays !== undefined);
602 let cleanHolidays = [];
603 if (hasHolidays) {
604 holidays = (holidays instanceof Array) ? holidays : [holidays];
605 if (holidays.length === 0) {
606 throw new RefError("Reference does not exist.");
607 }
608 for (let holidayDateValue of holidays) {
609 if (typeof holidayDateValue === "number") {
610 cleanHolidays.push(holidayDateValue);
611 } else {
612 throw new ValueError("NETWORKDAYS expects number values. But '" + holidayDateValue + "' is a " +
613 (typeof holidayDateValue) + " and cannot be coerced to a number.")
614 }
615 }
616 }
617 // Handle cases in which the start date is not before the end date.
618 let didSwap = startDate > endDate;
619 if (didSwap) {
620 let swap = endDate;
621 endDate = startDate;
622 startDate = swap;
623 }
624
625 let countMoment = moment.utc(TypeConverter.numberToMoment(startDate));
626 let weekendDays = [6, 0]; // Default weekend_days.
627 let days = endDate - startDate + 1;
628 let networkDays = days;
629 let j = 0;
630 while (j < days) {
631 if (weekendDays.indexOf(countMoment.day()) >= 0) {
632 networkDays--;
633 } else if (hasHolidays && cleanHolidays.indexOf(TypeConverter.momentToDayNumber(countMoment)) > -1) {
634 networkDays--;
635 }
636 countMoment.add(1, 'days');
637 j++;
638 }
639 // If the we swapped the start and end date, the result should be a negative number of network days.
640 if (didSwap) {
641 return networkDays * -1;
642 }
643 return networkDays;
644};
645
646/**
647 * Returns the number of networking days between two provided days excluding specified weekend days and holidays.
648 * @param startDate - The start date of the period from which to calculate the number of net working days.
649 * @param endDate - The end date of the period from which to calculate the number of net working days.
650 * @param weekend - [ OPTIONAL - 1 by default ] - A number or string representing which days of the week are
651 * considered weekends. String method: weekends can be specified using seven 0’s and 1’s, where the first number in the
652 * set represents Monday and the last number is for Sunday. A zero means that the day is a work day, a 1 means that the
653 * day is a weekend. For example, “0000011” would mean Saturday and Sunday are weekends. Number method: instead of using
654 * the string method above, a single number can be used. 1 = Saturday/Sunday are weekends, 2 = Sunday/Monday, and this
655 * pattern repeats until 7 = Friday/Saturday. 11 = Sunday is the only weekend, 12 = Monday is the only weekend, and this
656 * pattern repeats until 17 = Saturday is the only weekend.
657 * @param holidays - [ OPTIONAL ] - A range or array constant containing the dates to consider as holidays.
658 * The values provided within an array for holidays must be date serial number values, as returned by N or date values,
659 * as returned by DATE, DATEVALUE or TO_DATE. Values specified by a range should be standard date values or date serial
660 * numbers.
661 * @returns {number} of networking days between two provided days
662 * @constructor
663 */
664let NETWORKDAYS$INTL = function (startDate, endDate, weekend?, holidays?) : number {
665 ArgsChecker.checkLengthWithin(arguments, 2, 4, "NETWORKDAYS$INTL");
666 startDate = TypeConverter.firstValueAsDateNumber(startDate, true);
667 endDate = TypeConverter.firstValueAsDateNumber(endDate, true);
668 let weekendDays = [];
669 if (weekend !== undefined) {
670 weekend = TypeConverter.firstValue(weekend);
671 if (typeof weekend === "string") {
672 if (!/^[0-1]{6,}$/.test(weekend)) {
673 throw new NumError("Function NETWORKDAYS.INTL parameter 3 requires a number in the format '0000011'. "
674 + "Actual value is '" + weekend + "'");
675 }
676 let ws = weekend.split("");
677 for (let i = 0; i < ws.length; i++) {
678 if (ws[i] === "1") {
679 weekendDays.push(i === 6 ? 0 : i + 1);
680 }
681 }
682 } else if (typeof weekend === "number") {
683 switch (weekend) {
684 case 1:
685 weekendDays = [0, 6];
686 break;
687 case 2:
688 case 3:
689 case 4:
690 case 5:
691 case 6:
692 case 7:
693 weekendDays = [weekend, weekend - 1];
694 break;
695 case 11:
696 case 12:
697 case 13:
698 case 14:
699 case 15:
700 case 16:
701 case 17:
702 weekendDays = [weekend - 10];
703 break;
704 default:
705 throw new NumError("Function NETWORKDAYS.INTL parameter 3 requires a number in the range 1-7 or 11-17. "
706 + "Actual number is " + weekend + ".");
707 }
708 } else {
709 throw new ValueError("Function NETWORKDAYS.INTL parameter 4 expects number values. But '" + weekend
710 + "' cannot be coerced to a number.");
711 }
712 } else {
713 weekendDays = [0, 6];
714 }
715 let hasHolidays = holidays !== undefined;
716 let cleanHolidays = [];
717 if (hasHolidays) {
718 if (holidays === 0) {
719 throw new RefError("Reference does not exist.");
720 }
721 for (let holidayDateValue of holidays) {
722 if (typeof holidayDateValue === "number") {
723 cleanHolidays.push(holidayDateValue);
724 } else {
725 throw new ValueError("NETWORKDAYS.INTL expects number values. But '" + holidayDateValue + "' is a " +
726 (typeof holidayDateValue) + " and cannot be coerced to a number.")
727 }
728 }
729 }
730 // Handle cases in which the start date is not before the end date.
731 let didSwap = startDate > endDate;
732 if (didSwap) {
733 let swap = endDate;
734 endDate = startDate;
735 startDate = swap;
736 }
737
738 let countMoment = moment.utc(TypeConverter.numberToMoment(startDate));
739 let days = endDate - startDate + 1;
740 let networkDays = days;
741 let j = 0;
742 while (j < days) {
743 if (weekendDays.indexOf(countMoment.day()) >= 0) {
744 networkDays--;
745 } else if (hasHolidays && cleanHolidays.indexOf(TypeConverter.momentToDayNumber(countMoment)) > -1) {
746 networkDays--;
747 }
748 countMoment.add(1, 'days');
749 j++;
750 }
751 // If the we swapped the start and end date, the result should be a negative number of network days.
752 if (didSwap) {
753 return networkDays * -1;
754 }
755 return networkDays;
756};
757
758/**
759 * Returns the current date and time as a date value.
760 * @returns {number} representing the current date and time.
761 * @constructor
762 */
763let NOW = function () : number {
764 ArgsChecker.checkLength(arguments, 0, "NOW");
765 return TypeConverter.momentToNumber(moment.utc());
766};
767
768/**
769 * Returns the current date as a date value.
770 * @returns {number} today
771 * @constructor
772 */
773let TODAY = function () : number {
774 ArgsChecker.checkLength(arguments, 0, "TODAY");
775 return TypeConverter.momentToNumber(moment.utc().startOf("day"));
776};
777
778
779/**
780 * Converts a provided hour, minute, and second into a time. Will silently recalculate numeric time values which fall
781 * outside of valid ranges. Eg: TIME(24, 0, 0) is the same as TIME(0, 0, 0).
782 * @param hours - The hour component of the time.
783 * @param minutes - The minute component of the time.
784 * @param seconds - The second component of the time.
785 * @returns {number} time of day
786 * @constructor
787 */
788let TIME = function (hours, minutes, seconds) : number {
789 ArgsChecker.checkLength(arguments, 3, "TIME");
790 hours = Math.floor(TypeConverter.firstValueAsNumber(hours));
791 minutes = Math.floor(TypeConverter.firstValueAsNumber(minutes));
792 seconds = Math.floor(TypeConverter.firstValueAsNumber(seconds));
793 let e = TypeConverter.unitsToTimeNumber(hours, minutes, seconds);
794 if (e < 0) {
795 throw new NumError("TIME evaluates to an out of range value " + e + ". It should be greater than or equal to 0.");
796 }
797 return e;
798};
799
800
801/**
802 * Calculates the end date after a specified number of working days.
803 * @param startDate - The date from which to begin counting.
804 * @param numberOfDays - The number of working days to advance from start_date. If negative, counts backwards. If
805 * not an integer, truncate.
806 * @param holidays - [ OPTIONAL ] - A range or array constant containing the dates to consider holidays. The
807 * values provided within an array for holidays must be date serial number values, as returned by N or date values, as
808 * returned by DATE, DATEVALUE or TO_DATE. Values specified by a range should be standard date values or date serial
809 * numbers.
810 * @returns {number} end date after a specified number of working days.
811 * @constructor
812 */
813let WORKDAY = function (startDate, numberOfDays, holidays?) : number {
814 ArgsChecker.checkLengthWithin(arguments, 2, 3, "WORKDAY");
815 startDate = TypeConverter.firstValueAsDateNumber(startDate, true);
816 numberOfDays = TypeConverter.firstValueAsNumber(numberOfDays);
817 let hasHolidays = (holidays !== undefined);
818 let cleanHolidays = [];
819 if (hasHolidays !== undefined) {
820 if (holidays instanceof Array) {
821 if (holidays.length === 0) {
822 throw new RefError("Reference does not exist.");
823 }
824 for (let holidayDateValue of holidays) {
825 if (typeof holidayDateValue === "number") {
826 cleanHolidays.push(holidayDateValue);
827 } else {
828 throw new ValueError("WORKDAY expects number values. But '" + holidayDateValue + "' is a " +
829 (typeof holidayDateValue) + " and cannot be coerced to a number.")
830 }
831 }
832 } else {
833 cleanHolidays.push(TypeConverter.valueToNumber(holidays));
834 }
835 }
836
837 let weekendDays = [0, 6];
838 let countMoment = moment.utc(TypeConverter.numberToMoment(startDate));
839 let j = 0;
840 while (j < numberOfDays) {
841 countMoment.add(1, 'days');
842 if (weekendDays.indexOf(countMoment.day()) < 0 && cleanHolidays.indexOf(TypeConverter.momentToDayNumber(countMoment)) < 0) {
843 j++;
844 }
845 }
846 return TypeConverter.momentToDayNumber(countMoment);
847};
848
849
850/**
851 * Calculates the date after a specified number of workdays excluding specified weekend days and holidays.
852 * @param startDate - The date from which to begin counting.
853 * @param numberOfDays - The number of working days to advance from start_date. If negative, counts backwards.
854 * @param weekend - [ OPTIONAL - 1 by default ] - A number or string representing which days of the week are
855 * considered weekends. String method: weekends can be specified using seven 0’s and 1’s, where the first number in the
856 * set represents Monday and the last number is for Sunday. A zero means that the day is a work day, a 1 means that the
857 * day is a weekend. For example, “0000011” would mean Saturday and Sunday are weekends. Number method: instead of using
858 * the string method above, a single number can be used. 1 = Saturday/Sunday are weekends, 2 = Sunday/Monday, and this
859 * pattern repeats until 7 = Friday/Saturday. 11 = Sunday is the only weekend, 12 = Monday is the only weekend, and this
860 * pattern repeats until 17 = Saturday is the only weekend.
861 * @param holidays - [ OPTIONAL ] - A range or array constant containing the dates to consider holidays.
862 * @returns {number}
863 * @constructor
864 */
865let WORKDAY$INTL = function (startDate, numberOfDays, weekend?, holidays?) : number {
866 ArgsChecker.checkLengthWithin(arguments, 2, 3, "WORKDAY$INTL");
867 startDate = TypeConverter.firstValueAsDateNumber(startDate, true);
868 numberOfDays = TypeConverter.firstValueAsNumber(numberOfDays);
869 let weekendDays = [];
870 if (weekend !== undefined) {
871 weekend = TypeConverter.firstValue(weekend);
872 if (typeof weekend === "string") {
873 if (!/^[0-1]{6,}$/.test(weekend)) {
874 throw new NumError("Function WORKDAY.INTL parameter 3 requires a number in the format '0000011'. "
875 + "Actual value is '" + weekend + "'");
876 }
877 let ws = weekend.split("");
878 for (let i = 0; i < ws.length; i++) {
879 if (ws[i] === "1") {
880 weekendDays.push(i === 6 ? 0 : i + 1);
881 }
882 }
883 } else if (typeof weekend === "number") {
884 switch (weekend) {
885 case 1:
886 weekendDays = [0, 6];
887 break;
888 case 2:
889 case 3:
890 case 4:
891 case 5:
892 case 6:
893 case 7:
894 weekendDays = [weekend, weekend - 1];
895 break;
896 case 11:
897 case 12:
898 case 13:
899 case 14:
900 case 15:
901 case 16:
902 case 17:
903 weekendDays = [weekend - 10];
904 break;
905 default:
906 throw new NumError("Function WORKDAY.INTL parameter 3 requires a number in the range 1-7 or 11-17. "
907 + "Actual number is " + weekend + ".");
908 }
909 } else {
910 throw new ValueError("Function WORKDAY.INTL parameter 4 expects number values. But '" + weekend
911 + "' cannot be coerced to a number.");
912 }
913 } else {
914 weekendDays = [0, 6];
915 }
916 let hasHolidays = (holidays !== undefined);
917 let cleanHolidays = [];
918 if (hasHolidays) {
919 if (holidays instanceof Array) {
920 if (holidays.length === 0) {
921 throw new RefError("Reference does not exist.");
922 }
923 for (let holidayDateValue of holidays) {
924 if (typeof holidayDateValue === "number") {
925 cleanHolidays.push(holidayDateValue);
926 } else {
927 throw new ValueError("WORKDAY expects number values. But '" + holidayDateValue + "' is a " +
928 (typeof holidayDateValue) + " and cannot be coerced to a number.")
929 }
930 }
931 } else {
932 cleanHolidays.push(TypeConverter.valueToNumber(holidays));
933 }
934 }
935 let countMoment = moment.utc(TypeConverter.numberToMoment(startDate));
936 let j = 0;
937 while (j < numberOfDays) {
938 countMoment.add(1, 'days');
939 if (weekendDays.indexOf(countMoment.day()) < 0 && cleanHolidays.indexOf(TypeConverter.momentToDayNumber(countMoment)) < 0) {
940 j++;
941 }
942 }
943 return TypeConverter.momentToDayNumber(countMoment);
944};
945
946export {
947 DATE,
948 DATEVALUE,
949 DATEDIF,
950 DAYS,
951 DAY,
952 DAYS360,
953 EDATE,
954 EOMONTH,
955 MONTH,
956 YEAR,
957 WEEKDAY,
958 WEEKNUM,
959 YEARFRAC,
960 TIMEVALUE,
961 HOUR,
962 MINUTE,
963 SECOND,
964 NETWORKDAYS,
965 NETWORKDAYS$INTL,
966 NOW,
967 TODAY,
968 TIME,
969 WORKDAY,
970 WORKDAY$INTL
971}