Help with WORKDAYS formula adding an extra day

Platform discussions
,
1

I have a formula where I am trying to use WORKDAYS and it is adding an extra day to the outcome (it is counting the start date). For example, I am trying to figure out how many working days a study is in Unassigned before it gets to Assigned. I also want the cell to show blank if Assigned date is not entered. How can I get it to subtract a 1. I tried adding a -1 at the end and it does not work.

Formula: IF({Date Assigned}=“”,“”,WORKDAYS({Date Assigned},{Date Unassigned}))

2

I figured it out! I had to do two things:

  1. In order to get the formula to work for me, I flipped the two dates (Unassigned first, then Assigned second). This results in the automation showing positive days instead of negative
  2. As for subtracting one day, I took the “WORKDAYS” portion of the automation, and encased it in its own parentheses in order to properly do math on it.

Here is the final formula I came up with:
IF({Date Assigned}=“”, “”, (WORKDAYS({Date Unassigned},{Date Assigned}) - 1))

image
image546×100 5.67 KB

image
image619×570 37.4 KB

Note: If the date columns aren’t both filled out, it will show an error. There may be a way around it, but I am unsure of what it might be. It’s possible that it’s just a monday formula limitation, unfortunately

3

I believe the reason this is returning an error (triangle with exclamation mark) is because its trying to subtract a -1 from a blank cell. The formula I am trying right now is:
IF({Date Assigned}=“”,“”,WORKDAYS({Date Assigned},{Date Unassigned}))-1

4

@lkaz

This should work:

IF(AND({Date Assigned},{Date Unassigned}),WORKDAYS(IF({Date Assigned},{Date Assigned},"1/1/1"),IF({Date Unassigned},{Date Unassigned},"1/1/1")) - 1,"")

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