You are viewing limited content. For full access, please sign in.

Question

Question

Form field calculation preventing submit

Forms
Updated May 30, 2019
asked on May 14, 2019

I have 2 date fields that I am calculating the months from. Both date fields are not required, neither is the calculated months field. However when I try to submit the form, it gives me this error:

It works when I enter dates or something into the Base Contract Term (Months) field. I tried setting this field to a default value of 0 but it still won't let me submit without entering anything.

0 0

Answer

SELECTED ANSWER
replied on May 14, 2019

I think the below calculation might help. Date_1 is the beginning date and Date_2 is the ending date.

=IF(OR(Date_1="",Date_2=""),"",DATEDIF(Date_1,Date_2,"M"))

What the above is doing is making sure the field displays nothing if the fields are empty. If they are both full, then run the date difference calculation.

1 0
View 8 previous replies
replied on May 14, 2019

Another nice touch would be to hide the "Base Contract Term" field with a Field Rule when either of the date fields are empty.

The revised calculation will ensure it doesn't hit any validation errors, but it can be helpful to hide it and ignore the value if it doesn't apply.

1 0
replied on May 30, 2019

What would be the formula if the two dates were in a collection?

0 0
replied on May 30, 2019

You would just wrap your variables in the Index function.

INDEX(Date_1,ROW())

0 0
replied on May 30, 2019

Thanks, Jason.  I've plugged something in but I'm getting this:

I also tried (after looking at some of the other ones):

Wondering if you can see what I've done wrong?

My variables for my table are:

 

0 0
replied on May 30, 2019

0 0
replied on May 30, 2019 • Show version history

You can't wrap the INDEX around so many objects like that, it can only go around a single variable (i.e., it needs to be the innermost function).

For example,

DATEDIF(INDEX(Request_Details.Date_Arriving,ROW()),INDEX(Request_Details.Date_Leaving,ROW()),"D")

1 0
replied on May 30, 2019

Okay, that gets me a different error message.  Are we getting closer?  :)

0 0
replied on May 30, 2019 • Show version history

Yes, before the last parentheses, you need the last parameter for the DATEDIF to tell it if you want to calculate days, months, etc. (I missed that in my last example so I updated it).

0 0
replied on May 30, 2019

Added the "M" back in.  Was that part of the DATED IF?  I'm guessing not, because it didn't work.  So, what was it I needed to add where?

0 0
replied on May 30, 2019

Yes, that's part of the DATEDIF. In your screenshot you put it in the INDEX function. I updated my previous example, but it should go at the very end before the closing parentheses of the DATEDIF function.

1 0
replied on May 30, 2019 • Show version history

Oh, my gosh, it's now working!  Thank you so much, Jason!!

with this:

=DATEDIF(INDEX(Request_Details.Date_Arriving,ROW()),INDEX(Request_Details.Date_Leaving,ROW()),"D")

0 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...