i have a formula like this
LEFT(FORMAT_DATE(ADD_DAYS(TODAY(), DIVIDE({Remaining time},8))),6)
which is nothing if but , it will calculate the new delivery date from by adding , remaning number days to “TODAY”.
now if the result is SAT or SUN , i want it to be on monday.
and second question ,
if the result date falls under list of given dates(public holidays) it should move to the next working day.
above second question is not mandatory or urgent
For you first question… Here is one way:
FORMAT_DATE(ADD_DAYS(TODAY(), DIVIDE({Remaining Time}, 8 ) + SWITCH(FORMAT_DATE(ADD_DAYS(TODAY(), DIVIDE({Remaining Time}, 8)), "E"), "6", 2, "7", 1, 0)), "MMM D")
Jim - Subscribe to The Monday Man
Watch Our Latest Video: monday Formulas - Part 3: Dates/Times, Strings and Status
… the REAL solution:
FORMAT_DATE(WORKDAY(TODAY(), DIVIDE({Remaining time}, 8), ARGS2ARRAY("2021/12/24", "2021/12/31")), "MMM DD")
Of course, you can adjust the holidays however you want.
Jim - Subscribe to The Monday Man
Watch Our Latest Video: monday Formulas: the SECRET Functions
Hi there,
i tried to use this:
WORKDAYS({End Data},{Start Date},{Holidays})
where “holidays” is a list of dates. I created another board where each item is the holiday name with a column of day off. add this board to my task board and add all the holidays to it on a given task.
but when i use the above formula i get the number of days and not working days. (start day is an holiday so i expect that the result to the end date will be smaller).
i also tried to use ARGS2ARRAY({Holidays}), but got the same results.
where have i got wrong?
after that, the problem is to calculate the end date since it depends on the working days. i have a start date and duration. how can i get the end date using that and working days?
It’s not clear to me what you are describing with the holidays board.
However, the main issue is the WORKDAYS() does not support the holidays parameter. Second, assuming that {Holidays} is a single string of dates delimited by “,” (commas)…
Try: NETWORKDAYS({Start Date}, {End Data}, SPLIT({Holidays}, ","))
Jim - The Monday Man
Column Magic 
- the magical columns toolbox app
Thanks for the answer. unfortunately, this doesn’t work also.
even when i tried to do this:
NETWORKDAYS({Start Date},{End Data},ARGS2ARRAY(“2022/10/10”, “2022/12/1”))
i still get 3 (expected is 2 since Oct 10 is in the holiday parameter)
while start date is Oct 10 2022 and end data is Oct 12 2022.
My only advice is to triple check everything. I just tested the exact formula and dates you gave, and it worked for me.
Jim - The Monday Man
Column Magic - the magical columns toolbox app