# Trying to calculate the end date based on the start date. Having type problems..

• 1
• Question
• Updated 3 years ago

Trying to create a Date type formula that will calculate the end date a contract based on months.

The [Contract Length] is actually of type: Text.
When I save I'm getting the following error:
"The types of the arguments or the number of arguments supplied do not meet the requirements of the function If."

I'm not entirely sure where the type mismatch is, or whether using a variable in the if statement like I currently am is even legit.

var Number Length =
Case([Contract Length],
"Monthly", 1,
"1 Year", 12,
"2 Years", 24,
"3 Years", 36, null);

If (
\$Length =   1, (AdjustMonth([Contract Start Date], 1)),
\$Length = 12, (AdjustMonth([Contract Start Date], 12)),
\$Length = 24, (AdjustMonth([Contract Start Date], 24)),
\$Length = 36, (AdjustMonth([Contract Start Date], 36)), 0
)

Thoughts?

Thanks!

• 50 Points

Posted 3 years ago

• 1
• 110 Points
case([contract length],

)
• 50 Points
Thanks Eric. Not sure why I was overthinking this.
The formula worked, but for some reason, the target field (of type: Date) isn't actually getting populated with the target end date based on this formula. Any ideas why that may be happening?
• 110 Points
I am not sure if I understand correctly, but this worked in my test. This formula is intended for a formula date field that should get its start date from your contract start date and is then modified by your text multiple choice field contract length.
• 50 Points
That's exactly what I have.
My [Contract Start Date] is of type: Date
[Contract Length] is of type Text (Multiple Choice)
and the [Contract End Date] is a Date:Formula field.

In looking at the correction you provided, I figured that this should've done it. Hmmm..
• 110 Points
what happens to the contract end date when you enter a contract start date and choose a contract length?

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,204 Points
var Number Length =
Case([CL],
"Monthly", 1,
"1 Year", 12,
"2 Years", 24,
"3 Years", 36, null);

Case(\$Length,
null
)

But you are just cascading two Case statements so you could combine them into one

Case([CL],
null
)
• 50 Points
Interestingly.. nothing....
I have tried to create a "Numeric" field with the contract length, and tried using the end date formula builder, with the same result. (I may be doing something wrong I'm sure)
• 50 Points
Thanks! For some reason, that end field is still returning an empty value though. That's so strange!
• 50 Points
Here's an interesting thing I discovered.. if I choose "Monthly", it populates the date, but if I choose "1 Year, 2 Years, or 3 Years", it doesn't populate...
I had seen this before somewhere, I just don't know what I had done to resolve it...
• 110 Points
double check your contract length field options and make sure they match the text in your formula exactly.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,204 Points
It works for me
• 50 Points
That's odd. I remember when it happened to me in another case, I simply resorted to changing the fields in the Contract Length from 1 Year, to 12 , 2 years, 24 , etc ... so I don't think I ever found a solution for it...
• 50 Points
I double checked, the values are exactly the same. Would the "Multiple Choice" field have anything to do with it?
• 110 Points
if you want to grant me temporary access I can hop in there and take a look: eric@cirrusops.com
• 50 Points
Sure. Thank you Eric! Much appreciated. Invite in inbox.
• 50 Points
It's in _DBID_CLIENTS table.
• 110 Points
Resolved. the options in your formula did not match EXACTLY. Your options were 1 year, 2 years etc. The formula was written like this: 1 Year, 2 Years, etc. formulas involving text are case sensitive.
• 50 Points
Oh good lord!! Thanks so much! ... Starbucks card comin' your way Eric! Appreciate your help man!
• 110 Points
You're welcome. Glad to help. I've had little things like this get me before too! LOL!