Due Date set to Previous Working Day based on Event Date

Platform discussions
, ,
1

Hello All,

This might be something really easy, but I seem to be struggling with this.

I need my formula column to output the due date - and should be the working day before the event date (delivery date).

That means, if the delivery date is Tuesday 13/1/26, Due Date Should be Monday 12/1/26.
If the delivery date is Monday 12/1/26, due date should be Friday, 9/1/26.
Even if the delivery date is a Sat or Sun, due date should be friday.

This is what I have so far:

IF(

WORKDAYS(SUBTRACT_DAYS({Delivery Date}, 1), SUBTRACT_DAYS({Delivery Date}, 1)) = 1,

SUBTRACT_DAYS({Delivery Date}, 1),

IF(

WORKDAYS(SUBTRACT_DAYS({Delivery Date}, 1), {Delivery Date}) = 1,

SUBTRACT_DAYS({Delivery Date}, 3),

SUBTRACT_DAYS({Delivery Date}, 2)

)

)

Can anyone please guide me on how to achieve this?

2

Which product (CRM/Work Management, etc) and level (Pro/Enterprise) do you have?

3

Hello @ayesha_chopra
You can solve this just by checking the weekday and subtracting the right number of days.

Formula:

IF(
  WEEKDAY({Delivery Date},1)=1,
  SUBTRACT_DAYS({Delivery Date},3),
  IF(
    WEEKDAY({Delivery Date},1)=7,
    SUBTRACT_DAYS({Delivery Date},2),
    IF(
      WEEKDAY({Delivery Date},1)=6,
      SUBTRACT_DAYS({Delivery Date},1),
      SUBTRACT_DAYS({Delivery Date},1)
    )
  )
)

Monday → Friday
Saturday or Sunday → Friday
Other days → previous day

Dr. Tanvi Sachar
Monday Certified Partner, Monday Wizard

4

Hello Tanvi!
Thank you for your response.
WEEKDAY() Function - I cannot seem to find this in the list if functions available to build a formula column.
I did put the provided formula in my column, and as you can see in the attachment - WEEKDAY() is not blue as it should be like other functions.
However, it is giving an output - but I do not think it is expected. E.g., is Delivery Date is June 29 2026, the output is June 28 when it should be June 26.
What do you suggest?

image
image328×263 5.9 KB

5

Yep, this is just a weekday numbering mismatch.

In monday, WEEKDAY() counts like this:
Sunday = 1, Monday = 2, … Saturday = 7

So use this formula:
IF(
{Delivery Date}=“”,
“”,
IF(
WEEKDAY({Delivery Date})=2,
SUBTRACT_DAYS({Delivery Date},3),
IF(
WEEKDAY({Delivery Date})=1,
SUBTRACT_DAYS({Delivery Date},2),
IF(
WEEKDAY({Delivery Date})=7,
SUBTRACT_DAYS({Delivery Date},1),
SUBTRACT_DAYS({Delivery Date},1)
)
)
)
)

That gives you
Monday → Friday
Saturday or Sunday → Friday
Everything else → day before

1 Like
6

Hello, @drtanvisachar!
Thank you so much for your help!
I have not tried this method, but with some inspiration from your WEEKDAY() function use, I have got the following code (you are right, it was a weekday numbering mismatch!)

FORMAT_DATE

(IF(WEEKDAY({Delivery Date})=1,SUBTRACT_DAYS({Delivery Date},2),

IF(WEEKDAY({Delivery Date})=2,SUBTRACT_DAYS({Delivery Date},3),

IF(WEEKDAY({Delivery Date})=3,SUBTRACT_DAYS({Delivery Date},1),

IF(WEEKDAY({Delivery Date})=4,SUBTRACT_DAYS({Delivery Date},1),

IF(WEEKDAY({Delivery Date})=5,SUBTRACT_DAYS({Delivery Date},1),

IF(WEEKDAY({Delivery Date})=6,SUBTRACT_DAYS({Delivery Date},1),

IF(WEEKDAY({Delivery Date})=7,SUBTRACT_DAYS({Delivery Date},1),“”))))))))

Thanks a lot - my query is solved.

7

Hi!
Work Management, Enterprise.

8

Hi @ayesha_chopra Love it. Nice job working it through, and you nailed the root cause.

Yep, that mismatch in weekday numbering is exactly what was throwing things off. Your logic now covers all the cases correctly, and since it’s working for your scenarios, that’s the only thing that really matters.

Thanks for sharing the final version too.

1 Like
9

If you ever want this to live outside of a formula, you can handle it in the Workflow Center instead. Set a rule so that when the delivery or event date column changes, it updates another column (the due date) to match that event date. Then add an action to push the due date back by one business day.

Desiree - www.thecleverclovers.com

2 Likes