Nested If Statements with Dates

[Courtney_B]

Hello!
I was hoping someone could create a formula for me. I am new to the formula area and what I want to do is FAR beyond my brain power. Wanted to know if this type of formula was possible…

Fields:
“Date” (which is a date field)
“Day of Week” Field (which is a formula using DATEFORMAT to get the day of week)
“Complete” (which is a Check box)

The formula I am hoping for would replace my “Day of week” section:
INCLUDES:
• Dateformat to show date of week
• IF statement that says “Next Week” if the date is next week
• IF statement that says “Completed” if the Complete box is checked

I am creating a task list and I want this all in one field, so I can group. I want the tasks to be organized by the day of the week, for it to say “Next Week” if the date falls in the next week, and for it to say “Completed” if the box is checked.

Is this possible? Your help is MOST appreciated! Thank you!!

[JonathanBowen]

Hi @Courtney_B - is this what you are after?

The formula for this is:

IF(
  {Completed},
  'Completed',
  IF(
    DATETIME_FORMAT(Date, 'WW') = DATETIME_FORMAT(TODAY(), 'WW'),
    DATETIME_FORMAT(Date, 'MM/DD/YYYY'),
    IF(
      DATETIME_FORMAT(Date, 'WW') = DATETIME_FORMAT(TODAY(), 'WW') + 1,
      'Next Week',
      'Something else'
    )
  )
)

I’ve added in a ‘Something else’ option as I think the set of possibilities you are after isn’t exhaustive, e.g. a task that is due the week after next or a task in the past that isn’t completed. If you don’t want this you can just change it or blank it out.

JB