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

  • 1
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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!

Photo of George

George

  • 30 Points

Posted 3 years ago

  • 1
  • 1
case([contract length],

"Monthly",AdjustMonth([Contract Start Date], 1),

"1 Year",AdjustMonth([Contract Start Date], 12),

"2 Years",AdjustMonth([Contract Start Date], 24),

"3 Years",AdjustMonth([Contract Start Date], 36)

)
Photo of George

George

  • 30 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?
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.
Photo of George

George

  • 30 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..
what happens to the contract end date when you enter a contract start date and choose a contract length?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,044 Points 20k badge 2x thumb
var Number Length =
Case([CL],
  "Monthly", 1,
  "1 Year", 12,
  "2 Years", 24,
  "3 Years", 36, null);

Case($Length,
  1, AdjustMonth([CSD], 1),
  12, AdjustMonth([CSD], 12),
  24, AdjustMonth([CSD], 24),
  36, AdjustMonth([CSD], 36),
  null
)

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

Case([CL],
 "Monthly", AdjustMonth([CSD], 1),
  "1 Year", AdjustMonth([CSD], 12),
  "2 Year", AdjustMonth([CSD], 24),
  "3 Year", AdjustMonth([CSD], 36),
  null
)
Photo of George

George

  • 30 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)
Photo of George

George

  • 30 Points
Thanks! For some reason, that end field is still returning an empty value though. That's so strange!
Photo of George

George

  • 30 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...
double check your contract length field options and make sure they match the text in your formula exactly.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,044 Points 20k badge 2x thumb
It works for me
Photo of George

George

  • 30 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...
Photo of George

George

  • 30 Points
I double checked, the values are exactly the same. Would the "Multiple Choice" field have anything to do with it?
if you want to grant me temporary access I can hop in there and take a look: eric@cirrusops.com
Photo of George

George

  • 30 Points
Sure. Thank you Eric! Much appreciated. Invite in inbox.
Photo of George

George

  • 30 Points
It's in _DBID_CLIENTS table.
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.
Photo of George

George

  • 30 Points
Oh good lord!! Thanks so much! ... Starbucks card comin' your way Eric! Appreciate your help man!
You're welcome. Glad to help. I've had little things like this get me before too! LOL!