Referencing a date range in formulas

[Laura_Viar]

Hello! I’m wondering how to reference a date range in a formula. I’d need to generate estimated grade levels for students by birth date. I would like to format this as a nested IF formula (ex. IF(Birthdate = [DATE RANGE], “Kindergarten”, IF(Birthdate=[DATE RANGE], “1st Grade”, (IF…and so on) - how do I reference a date range in this case?

Thanks for any assistance!

• Meg

[Chester_McLaugh]

Nested IF statements work like an IF/ELSE statement, and the AND() allows you to test both the beginning of the range and the end of the range.

If you have a field named, “dob” use this formula in another field:

IF(
	AND(dob >= "2014-01-01", dob <= "2014-12-31"),
	"Date Range One",
	IF(
		AND(dob >= "2015-01-01",dob <= "2015-12-31"),
		"Date Range Two",
		IF(
			AND(dob >= "2016-01-01",dob <= "2016-12-31"),
			"Date Range Three",
			"No Match"
		)	
	)		
)

[Tim_Wilson]

You don’t usually have to test both end of your range if you do your tests in the right order. Test the youngest grade first to see if their dob is less than the maximum, if not then you know the student is older and you don’t have to check agains the bottom of the range. Then test to see if the student is in 1st grade by checking the maximum value for dob (you already checked for K, so the test will never get to this point if the student is a kindergartener).

This approach makes your series of if statements more concise if a bit more abstract.

[Chester_McLaugh]

@Tim_Wilson Great point! I guess I got a little too literal with the request :blush:

Here’s how that might look

IF(
	dob <= "2014-12-31",
	"Born 2014 or earlier",
	IF(
		dob <= "2015-01-01",
		"Born in 2015",
		IF(
			dob <= "2016-12-31",
			"Born in 2016",
			"Born 2017 or later"
		)	
	)		
)

[Scott_Heliker]

I have a similar question.
I need to perform a request once a week to the api.
I need to check all Events we have for a Ballroom in Air Table that have the date in a date field labeled
"Start Date" that are happening between today and +14 days from now.

How might this search formula look?
Thanks for your help here.