Hello everyone!
I’m trying to create a formula for my work board.
I need to include the due date, that must be 1 day before the publishing date (ignoring weekends). So I needed help with a formula to subtract 1 workday.
The current formula I use is
FORMAT_DATE(SUBTRACT_DAYS({Publishing Date},1),“MMM DD”)
But it doesn’t work if the Publishing Date is on Mondays.
Any ideas?
Thank you
Hi Pedro,
You need to check first what day of the week your publishing date is. You do this with
WEEKDAY({Publishing Date},1)
This returns 1 for Monday, 2 for Tuesday, etc.
So, if it is a monday, you remove 3 days, if not 1 day:
IF(WEEKDAY({Publishing Date},1)=1,ADD_DAYS({Publishing Date},-3),ADD_DAYS({Publishing Date},-1))
To keep the same formatting you used in your formula:
FORMAT_DATE(IF(WEEKDAY({Publishing Date},1)=1,ADD_DAYS({Publishing Date},-3),ADD_DAYS({Publishing Date},-1)), "MMM-DD")
Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.
Thanks! It worked just fine!
I just added one more line of code to make it work for Sundays too.
FORMAT_DATE(IF(WEEKDAY({Previsão de Publicação},1)=1,ADD_DAYS({Previsão de Publicação},-3), IF(WEEKDAY({Previsão de Publicação},1)=7,ADD_DAYS({Previsão de Publicação},-2),ADD_DAYS({Previsão de Publicação},-1))), "MMM DD")