You are viewing limited content. For full access, please sign in.

Question

Question

NETWORKDAYS function, what is the format for the optional holidays, weekdays data

Updated March 6, 2024
asked on November 13, 2018 • Show version history

In the Laserfiche Administration Guide:

Format:  NETWORKDAYS(startDate, endDate, [holidays], [weekDays])

Description: 

The NETWORKDAYS function returns the whole number of work days between two date objects.

"=NETWORKDAYS(DATE(2017, 1, 6), DATE(2017, 1, 14))" returns "6"

 

I need some help.  Please explain the format of the data for [holidays], [weekDays] and provide a working example in LF Forms.  The first 2 parameters work perfectly in the Advanced Calculation of a field but I cannot seem to pass a valid holiday date in to have the formula to exclude it from the calculation.

 

1 0

Answer

SELECTED ANSWER
replied on November 13, 2018 • Show version history

Hi Mary,

In the formula, the [holidays] parameter is looking for an array of dates. To build this array, you can use the DATEVALUE function to format each holiday you would like to exclude, and list them separated by commas inside of square brackets as follows:

=NETWORKDAYS(DATE(2017,1,6),DATE(2017,1,14), [DATEVALUE("2017-1-13"), DATEVALUE("2017-1-9")])

Alternatively, to make the holidays a bit easier to centrally manage, you can store a list of holidays in a database, and create a lookup rule to populate a hidden table on your form with that list. Then, you can feed the NETWORKDAYS formula the array of values from that table (for the example below, my lookup populates a column called “Holidays” in a table called “Table”):

=NETWORKDAYS(DATE(2017,1,6),DATE(2017,1,14), Table.Holidays)

2 0
replied on January 19, 2022

This is actually pretty cool! Thanks so much for this!

0 0
replied on March 6, 2024

Would this work in a table? I have a table with a start date, end date, and days taken fields inside. I have the formula working to calculate days taken just fine but when I add the holidays table in, it doesn't take it into account. 

 

=IF(AND(INDEX(sellbackDates.sDate,ROW())<>"",INDEX(sellbackDates.eDate,ROW())<>""),NETWORKDAYS(INDEX(sellbackDates.sDate,ROW()),INDEX(sellbackDates.eDate,ROW()),HolidayTable.Holiday,[1,0,0,0,0,0,1]),INDEX(0,ROW()))

 

0 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...