WORKDAYS function cannot exclude public holidays (but NETWORKDAYS can)

I use workday to count Leadtime automatically for my production team. It only could exclude weekends but not public holidays. Is there a way to use the google calendars to count actual workdays and exclude all local public holidays?

@NeilWu

There is a way to include holidays in the WORKDAY() formula calculations… outside of Google calendar:

An excerpt from The monday Hidden Functions – The Monday Man

WORKDAY
Adds workdays (excludes Saturday and Sunday, and optionally, holidays) to a date and returns the new date. Similar to the ADD_DAYS function.
Syntax: WORKDAY(date, days_to_add [,holidays as array])
Example: WORKDAY(“2021/12/1”, 21, ARGS2ARRAY(“2021/12/24”, “2021/12/31”)) => “Jan 03, 2022”


Jim - The Monday Man
We Create Custom Solutions
:magic_wand: Column Magic :sparkles:- the magical columns toolbox app

Thanks Jim for the new information,

I understand the concept but It’s a little bit different compared to what I want to achieve.

I would like to return how many work days are left to a particular date.

I have a column contains dates called “dispatch date” , and another column called “lead time” which I set the formula:

WORKDAYS({Dispatch Date},today())-1

to get the actual work days left for manufacturing. I tried to include this ARGS2ARRY in but it didn’t work. Maybe the syntax I input was wrong. Would you mind giving me a example to include this new function into my formula to exclude future holidays please?

Your help will be much appreciated.

Regards
Neil

@NeilWu

Oops… I got WORKDAY from your post heading… WORKDAYS() does not allow for holidays. However NETWORKDAYS() does…

An excerpt from The monday Hidden Functions – The Monday Man

NETWORKDAYS
Returns the number of work days between two dates (inclusive, excluding Saturday and Sunday, and optionally, holidays).
Syntax: NETWORKDAYS(start_date, end_date [,holidays as array])
Example: NETWORKDAYS("2021/11/26", "2021/12/02", ARGS2ARRAY("2021/11/30", "2021/12/1"))


Jim - The Monday Man
We Create Custom Solutions
:magic_wand: Column Magic :sparkles: - the magical columns toolbox app

That’s super useful, Thanks a lot Jim

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.