none
formulas in calculated field RRS feed

  • Question

  • I'm fairly new to using calculated fields in Sharepoint and I'm not very familiar with formula's.  Can anyone help me with the following?

    I have four columns in a sharepoint list. Column 1 is a choice column with three different options. The other three are date columns.

    I want to make a calculated 5th column that says "If column 1 is option A, I want the difference (in days) between the dates in column 2 & 3, If column 1 is not option A, I want the difference between the dates in column 3 & 4.

    I don't know if I'm just missing a space or a parenthesis or something like that..... or what.

    • Moved by Mike Walsh FIN Tuesday, May 12, 2009 10:59 AM admin (From:SharePoint - Development and Programming)
    Monday, May 11, 2009 7:23 PM

Answers

  • One thing that might be messing up is that with DATEDIF(start_date,end_date,unit) the end_date has to be later than the start_date or else you'll get an error.  So you get some "nice" nested if statements.  I'll assume that column 3 is supposed to be later than 2 and 4 later than 3 in order to get negative days.  If you just want the span, delete the *-1, but you do have to check for which date is later.  (Also, if you'd like to make it even more fun, nest a bunch of IF(NOT(ISBLANK([Column X] to make sure you aren't trying to calculate against empty fields, which will also cause an error.  You might run up against the nested-IF limit of seven, though.)  Otherwise:

    =IF([Column 1]="A",IF(([Column 2] <= [Column 3]),DATEDIF([Column 2],[Column 3],"D"),DATEDIF([Column 3],[Column 2],"D")*-1),IF(([Column 3] <= [Column 4]),DATEDIF([Column 3],[Column 4],"D"),DATEDIF([Column 4],[Column 3],"D")*-1))


    Broken down to make more sense:

    =IF([Column 1]="A",

        IF(([Column 2] <= [Column 3]),
            DATEDIF([Column 2],[Column 3],"D"),
            DATEDIF([Column 3],[Column 2],"D")*-1),

        IF(([Column 3] <= [Column 4]),
            DATEDIF([Column 3],[Column 4],"D"),
            DATEDIF([Column 4],[Column 3],"D")*-1)
    )



    Tuesday, May 12, 2009 1:37 AM

All replies

  • One thing that might be messing up is that with DATEDIF(start_date,end_date,unit) the end_date has to be later than the start_date or else you'll get an error.  So you get some "nice" nested if statements.  I'll assume that column 3 is supposed to be later than 2 and 4 later than 3 in order to get negative days.  If you just want the span, delete the *-1, but you do have to check for which date is later.  (Also, if you'd like to make it even more fun, nest a bunch of IF(NOT(ISBLANK([Column X] to make sure you aren't trying to calculate against empty fields, which will also cause an error.  You might run up against the nested-IF limit of seven, though.)  Otherwise:

    =IF([Column 1]="A",IF(([Column 2] <= [Column 3]),DATEDIF([Column 2],[Column 3],"D"),DATEDIF([Column 3],[Column 2],"D")*-1),IF(([Column 3] <= [Column 4]),DATEDIF([Column 3],[Column 4],"D"),DATEDIF([Column 4],[Column 3],"D")*-1))


    Broken down to make more sense:

    =IF([Column 1]="A",

        IF(([Column 2] <= [Column 3]),
            DATEDIF([Column 2],[Column 3],"D"),
            DATEDIF([Column 3],[Column 2],"D")*-1),

        IF(([Column 3] <= [Column 4]),
            DATEDIF([Column 3],[Column 4],"D"),
            DATEDIF([Column 4],[Column 3],"D")*-1)
    )



    Tuesday, May 12, 2009 1:37 AM
  • Moving to Admin. This isn't programming as defined
    WSS FAQ sites: http://wssv2faq.mindsharp.com and http://wssv3faq.mindsharp.com
    Total list of WSS 3.0 / MOSS 2007 Books (including foreign language) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx
    Tuesday, May 12, 2009 10:59 AM
  • Worked perfectly!  Thanks so much!

    I have one more if you have time that would be great.  Then I promise to leave you alone.

    I want to set up a column that will produce a date.  I want the formula to state that if Column 1 is Option A, Add 14 days to column 2, if not, add 45 days to column 3.

    I was trying to do it this way:

    =IF([Column 1]="A",DATE(YEAR([Column2]),MONTH([Column2]),DAY([Column2])+14),DATE(YEAR([Column3),MONTH([Column3]),DAY([Column3])+45))
    Monday, May 18, 2009 2:47 PM