IF/AND formula to enter variable text determined by date.

I am trying to use an IF/AND formula in a column to say that if the date in column Work Completion Date is within a certain date range, enter "Week1", if the dates fall within the next date range, enter "Week 2" all the way to Week 9.

I built out the below and it is not causing an error but there is nothing populating in the cell.

=IF(AND([Work Completion Date]@row >= DATE(20, 10, 18), [Work Completion Date]@row <= DATE(20, 10, 13)), "Week 0", IF(AND([Work Completion Date]@row >= DATE(20, 10, 25), [Work Completion Date]@row <= DATE(20, 10, 19)), "Week 1", IF(AND([Work Completion Date]@row >= DATE(20, 11, 1), [Work Completion Date]@row <= DATE(20, 10, 26)), "Week 2", "")))


Best Answer

Answers

  • Hi Paul, I just switched them but it is still showing as blank.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you show what you are now using?

  • =IF(AND([Work Completion Date]@row <= DATE(20, 10, 18), [Work Completion Date]@row >= DATE(20, 10, 13)), "Week 0", IF(AND([Work Completion Date]@row <= DATE(20, 10, 25), [Work Completion Date]@row >= DATE(20, 10, 19)), "Week 1", IF(AND([Work Completion Date]@row <= DATE(20, 11, 1), [Work Completion Date]@row >= DATE(20, 10, 26)), "Week 2", "")))


  • I figured it out! And your solution did help! I was entering the year within the date as 20 but it needed to be the full 2020. After updating that, with the update you also suggested it is now working!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That was going to be my next suggestion. I have never tested using a 2 digit year, so I couldn't have said for sure whether or not that would have affected it. Glad it is working for you now.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!