How to Subtract Workdays

Platform discussions Special workflows & use cases
1

Hello Everyone, I am trying to subtract a calculated about of days from a date to give me a required start date. I know i can do this with the subtract days function but I need to to only subtract work days.

This is what i have tried:
FORMAT_DATE(WEEKDAYS({Rough-In Date},-1*({SD Creation Days}+{SD Review Days}+{SD Edit Days}+{AE Review Days}+{Fabrication Days})))

FORMAT_DATE(WORKDAY({Rough-In Date},-1*({SD Creation Days}+{SD Review Days}+{SD Edit Days}+{AE Review Days}+{Fabrication Days})))

I can add work days but it wont let me subtract. Any Ideas?

1 Like
2

Hey @zrodgers! Would you be happy to share a screenshot of the columns you have set up? Additionally, how are you currently calculating the the amount of days to subtract? This will just help with our testing :pray:

3

I found a solution

IF(AND(GTE({Sum Review Time},0),INT({Sum Review Time})={Sum Review Time},{Rough-In Date}) ,FORMAT_DATE(
SWITCH({Sum Review Time},
0, {Rough-In Date},
INT(NETWORKDAYS(FORMAT_DATE({Rough-In Date},"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))), {Rough-In Date},
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7-2)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))), ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7-2)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7-1)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))), ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7-1)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7-0)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))), ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7-0)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+1)), "Y/M/D"), FORMAT_DATE({Rough-In Date}, "Y/M/D"))), ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+1)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+2)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+2)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+3)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+3)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+4)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+4)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+5)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+5)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+6)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+6)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+7)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+7)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+8)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+8)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+9)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+9)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+10)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+10)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+11)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+11)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+12)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+12)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+13)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+13)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+14)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+14)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+15)),"Y/M/D"),FORMAT_DATE({Rough-In Date},"Y/M/D"))),ADD_DAYS({Rough-In Date},-1*INT({Sum Review Time}/5*7+15))
, "ERROR"), "MMM D, YYYY"), "")
1 Like
4

@zrodgers

That looks familiar. Wherever did you find it? :grin:

The main advantage of the original formula was that it could account for holidays.

For those that don’t care about holidays, here’s a better version. It’s much simpler and works no matter how many days you’re subtracting:

FORMAT_DATE(
   SUBTRACT_DAYS({Date}, - {Days} 
   - SWITCH(WEEKDAY({Date}, 1),  
      1, FLOOR(({Days} + 0 ) / 5) * 2, 
      2, FLOOR(({Days} + 1 ) / 5) * 2, 
      3, FLOOR(({Days} + 2 ) / 5) * 2, 
      4, FLOOR(({Days} + 3 ) / 5) * 2, 
      5, FLOOR(({Days} + 4 ) / 5) * 2, 
      6, CEILING(({Days} + 1) / 5) * 2,
      7, CEILING(({Days} + 1) / 5) * 2 - 1
   )),
"YYYY-MM-DD")

Jim - The Monday Man
Get Custom Apps, Integrations & Automations created for monday

2 Likes
5

P.S.

For those looking to push (or pull in this case) a date directly, try this recipe from Column Magic :
image

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday

2 Likes