IF formula with date range that defines a set time period

Jason H

Statement to solve for -

If FINISH date is between x and y dates, the it = 30-60 (time period in a drop down)

Formula created, but not working

=IF(Finish)42 >=DATE(2020, 12, 21) OR <=DATE(2020, 12, 25), "30-60")

Can anyone help solve for this so that I can automated setting a time period based on the task date?

Find more posts tagged with

Formulas

Accepted answers

All comments

Leibel S

@Jason H

Try the below.

=IF(AND([Finish]42 >=DATE(2020, 12, 21), [Finish]42 <=DATE(2020, 12, 25)), "30-60", "")

Jason H

@Liebel S - This worked, however, the only thing missing is it's ability to choose from the pick list "31-60" and populate that in the field, any ideas there?

Jason H

@Leibel S - This worked, however, the only thing missing is it's ability to choose from the pick list "31-60" option and populate that in the field, any ideas here?

To give you an idea, here are the timeframes I'm trying to wrap in an automated fashion so when Finish date is within range, the period drop down field auto populates. There are 7 time periods

S-Cl, Day 1, 2-30, 31-60, 61-90, 91-120, 120+

Leibel S

@Jason H

Sounds like you need something more like the below this would work for nay riw it goes in (add it as a column formula).

If you can explain the need for 'S-CI' I can incorporate it.

=IF([Finish]@row - TODAY() = 0, "Day 1", IF([Finish]@row - TODAY() < 31, "2-30", IF([Finish]@row - TODAY() < 61, "31-60", IF([Finish]@row - TODAY() < 91, "61-90", IF([Finish]@row - TODAY() < 121, "91-120", "120+")))))

Jason H

period ranges.PNG

@Leibel S - Thanks for putting together the formula! I've attached a screen shot of the "Period" and their date ranges for reference and image of my plan showing Finish date and the Period date with dropdown options.

Basics are, that I want to scan the FINISH date row and automate with a formula the correct Period range, located in the pick list. Similar to an IF statement example like this:

=IF[Finish]1, is data range >=(2020.8.5) OR <=(2020.9.5), "Day 1", IF[Finish]1, is data range >=(2020.10.5) OR <=(2020.11.5), "31-60", etc.

Finish_Period Ranges.PNG

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.