Formula to return date based on certain conditions

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I'd like to create a date formula in a field called "Start Date" to return a date based on the specific options below :

If [Door] is equal to "Counter", then use one of the following dates,

If [Feasibility Overwrite Date] is not blank then use [Feasibility Overwrite Date],

If [Install Overwrite Date] is not blank then use [Install Overwrite Date] minus 84 days,

If [Feasibility Overwrite Date] and [Install Overwrite Date] are both blank then return Today()

Any thoughts?

Thanks, Sarah

Photo of Sarah

Sarah

  • 20 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ben

Ben

  • 30 Points
Are you asking whether you can nest if statements? If so the pseudo code would be:

if(
    [Door] is equal to "Counter",
    if(
        [Feasibility Overwrite Date] is not blank,
        [Feasibility Overwrite Date],
        if(
            [Install Overwrite Date] is not blank,
            [Install Overwrite Date] minus 84 days,
            Today()
        )
    ),
    blank (didn't see what you you wanted if door wasn't equal to counter)
)
Photo of Krissy

Krissy

  • 100 Points 100 badge 2x thumb
Hi Ben, I used your info above for a formula of my own.  Can you tell me what I've done incorrectly with this?  

IF(
     [Related Job Title (ref) - Department] is equal to "Warehouse",
     IF(
          [Last GMP Date] is not blank,
          [Last GMP Date],
          IF(
              [Last Annual Date] is not blank,
              [Last Annual Date],
              IF(
                   [Date of Day 1] is not blank,
                   [Date of Day 1]
                  )
             )
         ),
           IF(
               [Last GMP Date] is not blank,
               [Last GMP Date],
               IF(
                   [Last Annual Date] is not blank,
                   [Last Annual Date],
                   IF(
                       [Date of VA New Hire] is not blank,
                       [Date of VA New Hire],
                       )
                    )
                )
    )