Hi all,
I’ve seen some discussion similar to mine below, but they seem to always refer to dates based on ADDING days rather than SUBTRACTING them. Anyway…
I have a Date column (“TX Date”) that will always be several months in the future when it’s filled in.
A number column (“Lead Time”) says how many days before that date we need to start work.
I then have a formula column (“Brief Due”) that provides a new date based on TX Date minus the number of days in Lead Time.
My formula works but can sometimes provide a date that lands on a weekend. In these instances, I would like it to move forward to the next working day.
This can be done manually by adjusting the Lead Time value but it would be neater if the formula managed this itself.
This is my existing formula:
IF({Lead Time (days)}<1,“Update Lead Time column”,FORMAT_DATE((SUBTRACT_DAYS({TX Date},{Lead Time (days)})), “ddd, MMM D”))
Any suggestions very welcome - thanks
GCavin
(Gilles Cavin - Reinventing Formulas in monday.com)
2
You used to have the option to use the WORKDAY function, but it doesn’t seem to work anymore.
If you don’t want the date to fall on a week-end, you could use:
It checks if the week day of the calculated date is a Saturday (7) or Sunday (1). If a Saturday, it subtract another day, if a Sunday 2 more days.
Now that only sort of works, because it doesn’t tell you if you passed over a week-end while going backward. If the date falls on a Friday, it means you counted the week-end as 2 working days.
First, it doesn’t use the formula column, but rather casts its results to regular column. In this case, you would create a date column instead of your formula column.
Then AFB allows you to define your work week and enter company holidays in the app settings.
Once this is done, you simply use the ADDWORKINGDAYS function like this:
