Formula - substract working days from a date

Platform discussions Special workflows & use cases
1

Hi there!

I tried finding answers on this community but couldn’t find any for my specific use case. I have a board where I want to use a combination of Date formulas and automations (based on dependencies) so that I can calculate the start date for a project, giving the fact that I have the Deadline and the working days needed for specific tasks. I am trying to go backwards and calculate the Start Date of that project if I know the deadline and the number of working days for each task.

The issue that I have is that the workdays formula doesn’t recognize negative numbers, it works with adding days, not extracting days from a specific date. And the Substract formula doesn’t do workdays, but calendar days.

Really appreciate your answers!

2

@Luiza

One solution is to use something like Integromat to do the calculation. This specific use case can also be made easier by incorporating Google Sheets to do the calculations.

To do this directly in monday formulas is a bit complicated; but “doable”.

The formula below is taken from a video about monday’s undocumented functions. It uses some of the undocumented functions, namely: GTE(), NETWORKDAYS(), INT() and SPLIT().

(You can find out more about some of the undocumented functions here: The monday Hidden Functions – The Monday Man and in this video: monday Formulas - Part 4: The SECRET Functions - YouTube. )

If you don’t care about holidays, you can simplify the formula by replacing the NETWORKDAYS() function with WORKDAYS() and making the necessary adjustments (removing the SPLIT()).

Included in the description of the above video is this formula…
BACKWARD WORKDAYS FORMULA:
(Calculates a new date subtracting WORKDAYS from a given date accounting for holidays.)
To use it…

  • set up columns {Date}(date or formula), {Days}(number or formula), {Holidays}(formula}, and another formula column to contain this formula.
  • If you want to have different names, set it up this way then after everything is working, change the column names. This formula will automatically change.
  • put your holidays in the {Holidays} column in a string with dates in the format YYYY/MM/DD separated by commas, e.g.: “2021/12/24,2021/12/31” (no spaces).
  • copy and paste this formula text into your formula column.
  • change your column headings (if you want).
IF(AND(GTE({Days},0),INT({Days})={Days},{Date}) ,FORMAT_DATE(
SWITCH({Days},
0, {Date},
INT(NETWORKDAYS(FORMAT_DATE({Date},"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))), {Date},
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7-2)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))), ADD_DAYS({Date},-1*INT({Days}/5*7-2)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7-1)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))), ADD_DAYS({Date},-1*INT({Days}/5*7-1)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7-0)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))), ADD_DAYS({Date},-1*INT({Days}/5*7-0)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+1)),"Y/M/D"), FORMAT_DATE({Date}, "Y/M/D"),SPLIT({Holidays},","))), ADD_DAYS({Date},-1*INT({Days}/5*7+1)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+2)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+2)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+3)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+3)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+4)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+4)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+5)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+5)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+6)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+6)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+7)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+7)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+8)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+8)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+9)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+9)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+10)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+10)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+11)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+11)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+12)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+12)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+13)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+13)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+14)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+14)),
INT(NETWORKDAYS(FORMAT_DATE(ADD_DAYS({Date},-1*INT({Days}/5*7+15)),"Y/M/D"),FORMAT_DATE({Date},"Y/M/D"),SPLIT({Holidays},","))),ADD_DAYS({Date},-1*INT({Days}/5*7+15))
, "ERROR"), "MMM D, YYYY"), "")

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Column Total in monday.com Formulas? YES!!!

1 Like