# Calculated Date Column with Nested IF/OR statements

1. ## Calculated Date Column with Nested IF/OR statements

I am trying to calculate a Date based on two different columns in a SharePoint List: Rank column (conditional) and PEBD column (unconditional).

Essentially, I need to do the following:

IF <RANK> is E1, then the value of the column is <PEBD> + 6 YEARS

OR

IF <RANK> is E2, then the value of the column is <PEBD> + 6 YEARS

OR

IF <RANK> is E3, then the value of the column is <PEBD> + 10 YEARS

I need to do this all the way until E9.

Here is what I came up with, which is apparently a legitimate formula, but only returns a value of "1" if the conditions are met:

Code:
`=OR(IF([Rank]="E1",DATE(YEAR([PEBD])+6,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E2",DATE(YEAR([PEBD])+6,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E3",DATE(YEAR([PEBD])+10,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E4",DATE(YEAR([PEBD])+12,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E5",DATE(YEAR([PEBD])+20,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E6",DATE(YEAR([PEBD])+22,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E7",DATE(YEAR([PEBD])+24,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E8",DATE(YEAR([PEBD])+26,MONTH([PEBD])+0,DAY([PEBD])-1)),IF([Rank]="E9",DATE(YEAR([PEBD])+30,MONTH([PEBD])+0,DAY([PEBD])-1)))`
TIA  Reply With Quote

2. this looks like Excel thinking. In Access you make a conversion table and join in the query,
no nested formula is needed:

tConvertYrs table:
rank, years
E1, 6
E2, 6
E3, 10

in the query add tConvertYrs.Years + [pebd]  Reply With Quote

3. Thank you for the reply. This is for a calculated column in SharePoint.  Reply With Quote

4. Master of Nothing Windows 7 32bit Access 2010 32bit           Join Date
Sep 2010
Location
Posts
8,732
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  Reply With Quote

5. Originally Posted by ssanfu 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
Thank you for the detailed reply. I tried it out and it looks like it's not going to work. In searching around some, it appears calculated columns in SharePoint are limited as far as formulas go (see here: https://sharepoint.rackspace.com/cal...lumns-tutorial). I tried the "nested IF" option, but like I said, it only returned 1 or 0.

I have also tried another option that you may be able to speak to. Instead of asking the column to calculate a date, I simply asked it to return a specified number of days based on the rank of the member in that record. Something like this:

=IF(Rank="E1","2194",IF(Rank="E2","2194",IF(Rank=" E3","3654",IF(Rank="E4","4384",IF(Rank="E5","7304" ,IF(Rank="E6","8034",IF(Rank="E7","8764",9494))))) ))

This is an example of the "nested IF" that actually worked. The number represents <365> X <applicable number of years> - <1 day>

My idea was to add this column and the "PEBD" column mentioned earlier and just have it hidden in the background.

Unfortunately, the calculation between the two ends up being off by a few days.

Any thoughts on another possible work around where multiple columns could be involved to achieve the calculation?  Reply With Quote

6. Master of Nothing Windows 7 32bit Access 2010 32bit           Join Date
Sep 2010
Location
Posts
8,732
Sorry, I am in way over my head at this point. I tried doing some quick searching, but it looks like I would have to spend a lot of time to learn Sharepoint.

I am curious about how you came up with the number of days. I used 365/year, so for 6 years I get 2,190 and you have 2,194. (off by 4 days)
For 20 years, I get 7,300 and you have 7,304. (also off by 4 days)

I'll have to step away now....  Reply With Quote

7. Originally Posted by ssanfu Sorry, I am in way over my head at this point. I tried doing some quick searching, but it looks like I would have to spend a lot of time to learn Sharepoint.

I am curious about how you came up with the number of days. I used 365/year, so for 6 years I get 2,190 and you have 2,194. (off by 4 days)
For 20 years, I get 7,300 and you have 7,304. (also off by 4 days)

I'll have to step away now....

Yes, good catch. Originally I put 2190, but I added 4 days later on because 2190 did not give me the correct date (I found it was off by 4 days). Idk if that has something to do with leap years or some other thing. In any case, adding four days did not work across all Ranks - it only worked for one. I appreciate you trying to help.  Reply With Quote

Curtesy of Stack Exchange user "@willman":

=DATE(YEAR(PEBD)(IF(Rank="E1","6",IF(Rank="E2","6" ,IF(Rank="E3","10",IF(Rank="E4","12",IF(Rank="E5", "20",IF(Rank="E6","22",IF(Rank="E7","24",26))))))) ),MONTH(PEBD),DAY(PEBD)-1)

The issue with my original formula was that there were multiple DATE formulas nested within the nested IF statements. It should have been the other way around; 1 DATE formula with nested IF statements inside it.  Reply With Quote

calculated column, sharepoint 