Results 1 to 8 of 8
  1. #1
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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]

  3. #3
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    Thank you for the reply. This is for a calculated column in SharePoint.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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

  5. #5
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    Quote Originally Posted by ssanfu View Post
    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?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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....

    Good luck with your project....

  7. #7
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    Quote Originally Posted by ssanfu View Post
    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....

    Good luck with your project....
    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.

  8. #8
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    ANSWER RETRIEVED FROM STACK EXCHANGE:

    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. multiple nested iif statements
    By slimjen in forum Forms
    Replies: 5
    Last Post: 09-05-2016, 02:57 PM
  2. Nested If Statements?
    By shoelesscraig in forum Reports
    Replies: 7
    Last Post: 12-02-2015, 01:44 PM
  3. Getting mixed up with nested If statements
    By tonygg in forum Access
    Replies: 3
    Last Post: 03-28-2015, 08:53 PM
  4. Nested Select Statements in FROM clause
    By neonslip in forum Queries
    Replies: 11
    Last Post: 11-03-2010, 10:58 AM
  5. Nested IIF statements?
    By laavista in forum Access
    Replies: 9
    Last Post: 06-22-2010, 10:35 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums