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])))))
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!
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.
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.
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.
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.