Formula Help

Ronald Anderson · 2025-07-16T20:48:50+00:00

There was an error rendering this rich post.

I am seeking assistance with a specific formula.

I am creating an employee snow deactivation request and tracker. Employees can schedule time off during winter operations. The issue is that we have two periods, Preseson and In-Season. The Preseason period to submit your vacation is 7/15/25 thru 8/15/25 and In-Season is 10/1/25 thru 4/30/26. Requests cannot be more than 30 day out from the created date, but no less than two days. Here is the formula that is not working for the submission criteria:

=IF(Status@row = "Preseason", IF(AND([Created Date]@row >= [Start Date]@row - 30, [Created Date]@row <= [Start Date]@row + 2), 0, 1), IF(Status@row = "In-Season", IF(AND([Created Date]@row >= [Start Date]@row - 30, [Created Date]@row <= [Start Date]@row + 2), 0, 1, ""), ""))

It does not check the box, and I have no errors.

Thank you!

Ron Anderson

Find more posts tagged with

Formulas

IF(AND

IF(

Accepted answers

All comments

KPH

The formula that you shared will be unchecked if created date is on or after a date 30 days before start and on or before a date 2 days after the start. In all other situations it will return 1 (check the box). It does the same no matter if it is preseason or in-season and is not checking the duration or adding any restriction on the number of requests.

But let's get the first part to work. Did you try changing the +2 to -2?

This will uncheck the box if the created date is on or after a date 30 days before start and on or before a date 2 days before the start.

=IF(Status@row = "Preseason", IF(AND([Created Date]@row >= [Start Date]@row - 30, [Created Date]@row <= [Start Date]@row -2), 0, 1), IF(Status@row = "In-Season", IF(AND([Created Date]@row >= [Start Date]@row - 30, [Created Date]@row <= [Start Date]@row -2), 0, 1, ""), ""))

Corey is correct that there is an extra parameter in the second IF - it is superfluous, not causing an error. But to make it easier to follow, we can remove that extra bit:

=IF(Status@row = "Preseason", IF(AND([Created Date]@row >= [Start Date]@row - 30, [Created Date]@row <= [Start Date]@row - 2), 0, 1), IF(Status@row = "In-Season", IF(AND([Created Date]@row >= [Start Date]@row - 30, [Created Date]@row <= [Start Date]@row - 2), 0, 1), ""))

What about this is not what you want?

Ronald Anderson

Thank you, Cory, but this did not fix the issue. I am still not getting the box to check if any of the parameters are violated.

Ronald Anderson

Thank you for your time, KPH. I intended to use a formula to allow for a one-month pre-snow season period during which employees can submit one vacation request. The only parameter is that the request cannot exceed 14 days. I have other helper columns to identify that. Once the period changes to In-Season, employees may submit up to three requests that may not be more than 30 days out and no less than 2 days' notice.

I hope this makes sense.

Corey W.

@Ronald Anderson It looks like you have an extra, third then statement at the end (bold and italic below:

Corrected:

KPH

Hi Ron

I'm not sure if the box should be checked or unchecked when the request is valid, but it sounds like it doesn't check in at least one situation when you expect it to. With what you have the formula will return 0 (unchecked) only when both things are true - created date is on or after a date 30 days before start and on or before a date 2 days after the start. In all other situations it will return 1 (check the box).

Could it be that you meant to subtract 2 from the start date instead of add?

=IF(Status@row = "Preseason", IF(AND([Created Date]@row >= [Start Date]@row - 30, [Created Date]@row <= [Start Date]@row + 2), 0, 1), IF(Status@row = "In-Season", IF(AND([Created Date]@row >= [Start Date]@row - 30, [Created Date]@row <= [Start Date]@row + 2), 0, 1, ""), ""))

This will uncheck the box if the created date is on or after a date 30 days before start and on or before a date 2 days before the start

=IF(Status@row = "Preseason", IF(AND([Created Date]@row >= [Start Date]@row - 30, [Created Date]@row <= [Start Date]@row -2), 0, 1), IF(Status@row = "In-Season", IF(AND([Created Date]@row >= [Start Date]@row - 30, [Created Date]@row <= [Start Date]@row -2), 0, 1, ""), ""))

Is that it?

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.