RSA Archer calculated Date

Question

I'm new to Archer.

I'm trying to create a calculated date field based on 2 other fields.

• Field A (Date Field)
• Field B (Value List): Annually (365 days);Semi (182 Days); Monthly (30 Days)
• Field C (Date Field)

What I want to see is, if Field A has value 6/12/2017 and Field B has value annually (365 days), Field C should show me the value 6/12/2018

Thank you

Answer 1

It's very easy :)

Please write the below calculation in FIELD C: (use DATEADD inbuilt Function)

IF([FIELD B] =VALUEOF(FIELD B], "Annually"), DATEADD(DAY, 365,[FIELD A]),

IF([FIELD B] =VALUEOF(FIELD B], "Semi"), DATEADD(DAY, 182,[FIELD A]),

DATEADD(DAY, 30,[FIELD A])))

Hope this will help!!

Answer 2

Another approach is to capture the Day Month and Year in off layout fields. Example shown below. This may need some tweaking of how to combine it back into a Calculated Date Field. Benefit of this approach is it handles leap years and and we aren't concerned about how many days are in each month. Simply adding so many days to the date will result in so many unwanted results and to handle that will result in some real complex solution.

note: formatting of calculation is taken from RSA's documentation on debugging calculations.

Example

Helper Day

DAY([Field A])

Helper Month

IF(
    [Field B] = VALUEOF(FIELD B], "Semi")
,   MONTH([Field A])+6
,   IF(
       [Field B] = VALUEOF(FIELD B], "Monthly")
    ,  MONTH([Field A])+1
    ,  MONTH([Field A])
    )
)

Helper Year

IF(
   [Field B] = VALUEOF(FIELD B], "Annually")
,  YEAR([Field A])+1
,  IF(
      [Helper Month] > 12
   ,  YEAR([Field A] + ([Helper Month] - 12))
   ,  YEAR([Field A])
   )
)

Field C

IF(
   [Helper Month] > 12
   ,  DATEFORMAT(CONCATENATE([Helper Day],"/",[Helper Month] -12,"/",[Helper Year]))
   ,  DATEFORMAT(CONCATENATE([Helper Day],"/",[Helper Month] -12,"/",[Helper Year]))
)