Formula to populate a "Year" if Create date falls between 2 column dates

Linda F

I created a form that request the Calendar year for funding. They range from 2022 to 2028 and each year has Quarters. Sample quarter is between April 15 - July 15 so if I want the formula to populate me a Calendar year 2022 if the create date falls between the April - July dates. Here is the formula that works but is missing the end date. =IF(Created@row > [Begin Date]@row, "2022").

This will populate on the sheet so the applicants don't erroneously pick the wrong calendar year and remove the field from the form. Our sheet will be able to do the math for the applicant.

My date fields are Created, Begin Date and Cutoff Date.

Jeff Reisman

@Linda F I'm having trouble following the logic here. If the Created date for the row is after the Begin Date for the row, set the year to 2022. I don't see how that relates to quarter dates.

Can you give us some additional info, screen shots, etc?

Linda F

Understandable. I missed an important part. So the semesters have a begin and cutoff date. If Create date falls between begin and cutoff date of April 15, 2022 to July 15, 2022 then the Semester should be "Fall" and 2022 based on month and year. So on and so forth. Hope that makes sense.

Jeff Reisman

@Linda F

You may be overthinking it. You want the year that corresponds with the Begin Date and Created date, yes? Just use the YEAR function on one of those date fields.

=YEAR(Created@row)

Linda F

Okay, simple enough. What about the Semester? How do I use the begin date and Cutoff date to populate the semester. They include for example Fall which is April 15 - July 15.

Jeff Reisman

@Linda F

You could string together some IFs to evaluate the dates and set the Semester value. Your screenshot shows 4/15/22 - 7/15/22 to be "Summer II". So for example:

=IF([Begin Date]@row = DATE(2022, 4, 15), "Summer II", IF([Begin Date]@row = DATE(2022, 7, 15), "Fall", IF([Begin Date]@row = DATE(2022, 10, 15), "Fall II", IF([Begin Date]@row = DATE(2023, 1, 15), "Winter", "")

Linda F

That works but according to my chart below, if I submit an application between April and July then the semester will be Fall. So then the formula doesn't work and the response is not correct. If I apply on July 15 then my application would still be submitted for Fall semester and the create date will give me the year 2022. I need a formula to populate the semester in one column and year in the other.