NxAdjust - Move to the nearest workday

Examines whether the given date falls on a weekend or a holiday (i.e. non-working day), and returns the nearest working business day using a Business Day Convention (BDC).

 

Syntax

NxAdjust(Date, Convention, Holidays, Weekends)

Date is a serial date number that represents a given date. If missing, the current date is used.

Convention is the Business Day Convention (BDC): 1=Following, 2=Following Modified, 3=Preceding, 4=Preceding Modified, 5=Unadjusted (default)

ASC Description
1 Following - The following business day
2 Following Modified - the following business day as long as this falls within the same month; otherwise the preceding business day.
3 Preceding - The preceding business day
4 Preceding Modified - The preceding business day as long as it falls within the same month; otherwise the following business day.
5 Unadjusted - Leaves the date unchanged

Holidays is a specific calendar code, array of holidays codes or an array of holiday dates. If missing, the U.S. federal holidays are used.

Weekends is the weekend number (1-7,11-17) or the weekend string value (7-character string). If missing, the western weekend (i.e. 1, "Saturday, Sunday") is used.

 

Remarks

  1. NxAdjust accounts for situations when a payment date falls on a weekend or holiday, and calculates the corresponding value date.
  2. If a given date or any of the holiday/calendar code is invalid, NxAdjust returns the #VALUE! error.
  3. If the buisness day convention (BDC) value is less than one(1) or greater than five (5), NxAdjust returns the #VALUE! error.
  4. For more weekend conventions and codes, see the Weekend category.
  5. For a full list of supported calendars and their corresponding codes, visit the Calendar category.
  6. Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

Examples

Example 1: December 25 is considered to be a U.S. government holiday.



  Formula Description (Result)
  =NxAdjust(DATE(2012,5,27),1,"USD") Tuesday, May 29, 2012
  =NxAdjust(DATE(2012,3,31),2) Friday, March 30, 2012
  =NxAdjust(DATE(2012,5,27),3,"USD") Friday, May 25, 2012
  =NxAdjust(DATE(2012,4,1),4) Monday, April 02, 2012
  =NxAdjust(DATE(2012,4,1),5) Sunday, April 1, 2012

Files Examples

References

Have more questions? Submit a request

0 Comments