# 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", "")))

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

• 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!

• 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!