Calc Help

[DarrenNichols]

I'm trying to get my completed date field to update based on my plan review schedule.  This is what I have come up with but getting an error.  Any help is much appreciated.

thanks,

IF(VALUEOF([Plan Review Schedule],"Quarterly"),DATEADD(DAY, 90, [Completed Date])
IF(VALUEOF([Plan Review Schedule],"Semi-annual"),DATEADD(DAY, 180, [Completed Date])
IF(VALUEOF([Plan Review Schedule],"Annual"),DATEADD(DAY, 365, [Completed Date])))))

[SeanGriesheimer]

Darren, you posted this on the NetWitness group, fyi, so your answers might be limited here. Putting on my Archer hat for a minute, I believe (if this is an exact copy) that your two issues might just be:

1) The lack of commas at the end of the first two rows.

2) You don't have a final THEN statement. So, "If Quarterly, Add 90 days, If Semi-Annual, Add 180 days, If Annual, Add 365 days," but what if none of those is chosen? Even if the field is a VL with only those options available, you'll potentially have blanks or other mistaken values. A simple "" to return a null value should work.

[SeanGriesheimer]

You also had one-too-many closed-parens. This should work.

IF(VALUEOF([Plan Review Schedule],"Quarterly"),DATEADD(DAY, 90, [Completed Date]),
IF(VALUEOF([Plan Review Schedule],"Semi-annual"),DATEADD(DAY, 180, [Completed Date]),
IF(VALUEOF([Plan Review Schedule],"Annual"),DATEADD(DAY, 365, [Completed Date]),"")))

NOTE: I haven't worked on Archer day-to-day in over 2 years, so please test this out!

[DarrenNichols]

Whoops, Thanks for helping Sean.  I guess I was in a bit of a hurry to get this posted.  Ive since added this formula and although it validates I get an error on the Completed date field when I create a record and change the values of the Review Schedule field. 

The errror reads _invalid expression 

Any other ideas here?  Got to be getting close.

Thanks again.

[SeanGriesheimer]

What version are you on? I wonder if there's an issue with the DATEADD function? I liked to treat date fields as numbers and just add values. So try this:

IF(VALUEOF([Plan Review Schedule],"Quarterly"),[Completed Date] + 90,
IF(VALUEOF([Plan Review Schedule],"Semi-annual"),[Completed Date] + 180,
IF(VALUEOF([Plan Review Schedule],"Annual"),[Completed Date] + 365,"")))

It's been so long I'm not 100% sure that "[Completed Date] + 90" is even valid or if there's some other syntax you should use, but in general if you just treat the Date field like a numeric field, you should get the same results. I've found that to actually be more consistent at times.

[DarrenNichols]

5.5 SP4 P3

I'm getting the same error this version. 

Ill also post this out on the Archer forum to see if anyone there can help.

I appreciate your help thus far.  Calcs always a challenge for me.