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

Find more posts tagged with

Accepted answers

Paul Newcome

Try switching your arguments. Right now you have it saying for Week 0 that if the date is greater than 18 Oct and less than 13 Oct. You cannot have a date greater than the 18th and less than the 13 at the same time. If you switch it to say less than the 18 and greater than the 13th (as well as the same switch for the rest of your week numbers) then you should be good to go.

All comments

Paul Newcome

kleinsb

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

Paul Newcome

Can you show what you are now using?

kleinsb

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

kleinsb

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.