2017-08-15 12:22 PM
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])))))
2017-08-15 12:39 PM
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.
2017-08-15 12:41 PM
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!
2017-08-15 02:00 PM
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.
2017-08-15 02:06 PM
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.
2017-08-15 02:13 PM
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.