Here is my 2 cents worth.....
First a warning: The only thing I know about SharePoint is the name.
- The syntax you posted in the first post looks like an Excel formula in a cell.
- Be aware that "DATE" is a built in function.
If you use
MyVariable = Date
the variable will be equal to the current date.
If you use
Date = MyVariable
the system date will be set to the date in the variable (assuming the variable has a valid date)
So in your code from the first post, I think using
DATE is wrong.
Also, I think you should be using Immediate If (IIF) instead of the IF function.
To use the Immediate If (IIF) function, the syntax is
IIF(C,T,F)
where
C = the condition (must result in True or False)
T = the TRUE result and
F = the FALSE result
In your example in your first post,
all of the IF functions are missing the FALSE result part.
To calculate a date the way you have the calculation written, I think you should be using the
DATESERIAL() function.
Here is what you could try:
Code:
=IIF([Rank]="E1" Or [Rank]="E2", DATESERIAL(YEAR([PEBD])+6,MONTH([PEBD])+0,DAY([PEBD])-1),IIF([Rank]="E3",DATESERIAL(YEAR([PEBD])+10,MONTH([PEBD])+0,DAY([PEBD])-1), IIF([Rank]="E4", DATESERIAL(YEAR([PEBD])+12,MONTH([PEBD])+0,DAY([PEBD])-1), IIF([Rank]="E5", DATESERIAL(YEAR([PEBD])+20,MONTH([PEBD])+0,DAY([PEBD])-1), IIF([Rank]="E6", DATESERIAL(YEAR([PEBD])+22,MONTH([PEBD])+0,DAY([PEBD])-1), IIF([Rank]="E7", DATESERIAL(YEAR([PEBD])+24,MONTH([PEBD])+0,DAY([PEBD])-1), IIF([Rank]="E8", DATESERIAL(YEAR([PEBD])+26,MONTH([PEBD])+0,DAY([PEBD])-1), IIF([Rank]="E9", DATESERIAL(YEAR([PEBD])+30,MONTH([PEBD])+0,DAY([PEBD])-1),0))))))))
Broken apart, it looks like this
Code:
=IIF([Rank]="E1" Or [Rank]="E2", DATESERIAL(YEAR([PEBD])+6,MONTH([PEBD])+0,DAY([PEBD])-1),
IIF([Rank]="E3",DATESERIAL(YEAR([PEBD])+10,MONTH([PEBD])+0,DAY([PEBD])-1),
IIF([Rank]="E4", DATESERIAL(YEAR([PEBD])+12,MONTH([PEBD])+0,DAY([PEBD])-1),
IIF([Rank]="E5", DATESERIAL(YEAR([PEBD])+20,MONTH([PEBD])+0,DAY([PEBD])-1),
IIF([Rank]="E6", DATESERIAL(YEAR([PEBD])+22,MONTH([PEBD])+0,DAY([PEBD])-1),
IIF([Rank]="E7", DATESERIAL(YEAR([PEBD])+24,MONTH([PEBD])+0,DAY([PEBD])-1),
IIF([Rank]="E8", DATESERIAL(YEAR([PEBD])+26,MONTH([PEBD])+0,DAY([PEBD])-1),
IIF([Rank]="E9", DATESERIAL(YEAR([PEBD])+30,MONTH([PEBD])+0,DAY([PEBD])-1),0))))))))
Notice that "E1" and "E2" are in the first IIF because the calculated dates are the same calculation.
If [Rank] is not equal to E1 through E9, then a zero (indicated by the RED zero in the code above) is returned. This could be changed to an invalid date such as
1/1/1901