Count days between 2 dates only if the date is present in another column

Platform discussions
, ,
1

Hello! I think I am missing something in this formula. It works until I add the final component of “-1”. I am trying to calculate the workdays it takes to deliver a product. I noticed that when I use the WORKDAYS formula it includes the first date in the count. I was able to correct this by adding -1.

I only want the number of days calculated if we have a payment date, otherwise I want it to be blank.

Here is my current formula:
IF({Payment Date}=“”,“”, WORKDAYS({Delivery Date},{Payment Date})-1)

Without the -1, it works perfectly. When I add the -1, I get an error message stating “one of the parameters is invalid”.

2

Hi @khiggins try putting the -1 outside of the bracket so the deduction is made after the formula.

IF({Payment date}=“”, “”, WORKDAYS({Delivery date}, {Payment date}))-1

image
image522×192 8.14 KB

3

The issue is likely caused by how Monday. com’s formula field handles blank values and the WORKDAYS function. Here are a few things to check and a corrected version of your formula.

Possible Issues:

  1. Blank Value Handling:
  • The formula {Payment Date}="" might not correctly check for empty values. Instead, try using IF(EMPTY({Payment Date})...).
  1. WORKDAYS Function Limitations:
  • The WORKDAYS function requires valid date inputs. If one of the dates is missing or formatted incorrectly, it may cause an error.

Corrected Formula:

Try this instead:

IF(EMPTY({Payment Date}), "", WORKDAYS({Delivery Date}, {Payment Date}) - 1)

Why This Works:

  • EMPTY({Payment Date}) properly checks if the Payment Date field is blank.
  • WORKDAYS({Delivery Date}, {Payment Date}) - 1 correctly subtracts 1 to exclude the first day.

If you’re still getting an error, check that both {Delivery Date} and {Payment Date} are properly formatted as date columns in Monday. com. Let me know if it still doesn’t work!

Trevor

4

Hello @WLane

Thank you for your input. Yes, that seems to work, but I should clarify that the error shows up on rows that do not have a date in the Payment date column. When that date is not present, I get the triangle symbol that say “one of the parameters is invalid” in the column with the formula instead of it staying blank as instructed by the IF formula.

5

@khiggins

This will work for you:

IF(AND({Payment Date},{Delivery Date}), WORKDAYS(IF({Delivery Date},{Delivery Date},"1/1/1"),IF({Payment Date},{Payment Date},"1/1/1"))-1,"")

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

1 Like
6

Hello @JCorrell,

That worked! Thank you so much!

2 Likes
7

Hello @trevor13,

Thank you for your advice. I checked and made sure that the columns were formatted as a date column. I tried the formula you suggested, but the EMPTY() formula was not recognized. I looked up some similar options and tried ISBLANK(). This is the current formula I am using:

IF(NOT(ISBLANK({Payment Date})), WORKDAYS({Delivery Date}, {Payment Date}) - 1, "")

This works so long as there is a date in the Payment date column. After reviewing your response again, it seems that this formula would be dependent on the “Payment Date” value being there. Is this why I am seeing this error message of “one parameter is invalid” on the rows that do not have a date in the “Payment Date” column? I have attached an image of the column results I am seeing. If it has a payment date, I am returned the value. If it doesn’t have a payment date (top and bottom row), then I am returned the triangle symbol with the error message stated above.

image
image176×179 1.04 KB

Is there any way to code the formula to not show the error?

1 Like
8

Maybe try this?

IF(AND(ISBLANK({Payment Date}) = FALSE, ISBLANK({Delivery Date}) = FALSE), WORKDAYS({Delivery Date}, {Payment Date}) - 1, “”)