Help with changing an entry if a date falls within a date range

Lorri Rose

I want to automatically assign our year quarters to a date range so the formula would select Q1 IF date created is between A/B Q2 if date created is between C/D etc - I have no idea where to start can anyone help?

SoS | Dan Palenchar

Hello @Lorri Rose,

This is very doable! Find a published Sheet with the formulas here.

In the Sheet Summary section I added fields for Q1 Start, Q2 Start, Q3 Start, Q4 Start, and Next Year. These are needed so you can evaluate Date fields and determine which Quarter they fall into.

You can put these in manually or use these formulas that will adapt whenever you reach a new year:

• Q1 Start: =DATE(YEAR(TODAY()), 1, 1)
• Q2 Start: =DATE(YEAR(TODAY()), 4, 1)
• Q3 Start: =DATE(YEAR(TODAY()), 7, 1)
• Q4 Start: =DATE(YEAR(TODAY()), 10, 1)
• Next Year: =DATE(YEAR(TODAY())+1, 1, 1)

Technically it is not necessary to use the Sheet Summary fields, I just find it more organized. You could bypass this and put those date formulas directly into the formula below.

Now that we have our reference dates we can make a formula that evaluates each date with respect to when a Quarter Starts/Ends. This is that formula

=IF(AND(Date@row >= [Q1 Start]#, Date@row < [Q2 Start]#), "Q1", IF(AND(Date@row >= [Q2 Start]#, Date@row < [Q3 Start]#), "Q2", IF(AND(Date@row >= [Q3 Start]#, Date@row < [Q4 Start]#), "Q3", IF(AND(Date@row >= [Q4 Start]#, Date@row < [Next Year]#), "Q4"))))

The resulting Sheet looks like this:

Hope that helps! Link to published Sheet is here.

Lorri Rose

Dan, Thank you so much, I want to use it in the sheet itself so you have saved me hours of angst trying to fumble my way through the formula. I also like to sheet summary - thank you again for the formula and will use this for a report.

Paul Newcome

If your quarters are calendar based, you can use this:

="Q" + ROUNDUP(MONTH(Date@row) / 3)

There are also other simplified methods if you have something different such as a fiscal year instead of a calendar year.

Lorri Rose

HI Paul Q1 starts on the 1st April each year - how would I use your formula with this?

Paul Newcome

For an April 1 start, I would use this:

="Q" + IF(MONTH(Date@row) <= 3, 4, IF(MONTH(Date@row) <= 6, 1, IF(MONTH(Date@row) <= 9, 2, 3)))

Lorri Rose

Awesome thank you!

Paul Newcome

Happy to help. 👍️

Lorri Rose

Hiya, I can't get these to work - is it possibly because I'm using a UK date format? dd/mm/yy

Paul Newcome

The UK date format shouldn't make a difference because it should still be saved as a date on the back end. When you say that it doesn't work, are you getting an error message, an unexpected output, or something else?

Are you able to provide some screenshots?

Lorri Rose

I have input the formulae below, the column I want to work the formula off is called Date of Check. I am getting #unparseable when I input it.

=IF(AND(Date of Course@row >= [=DATE(YEAR(TODAY()), 4, 1)]#, Date of Check@row < [=DATE(YEAR(TODAY()), 7, 1)]#), "Q1", IF(AND(Date of Check@row >= [=DATE(YEAR(TODAY()), 7, 1)]#, Date of Check@row < [=DATE(YEAR(TODAY()), 10, 1)]#), "Q2", IF(AND(Date of Check@row >= [=DATE(YEAR(TODAY()), 10, 1)]#, Date of Check@row < [=DATE(YEAR(TODAY()), 1, 1)]#), "Q3", IF(AND(Date of Check@row >= [=DATE(YEAR(TODAY()), 1, 1)]#, Date of Check@row < [=DATE(YEAR(TODAY())+1, 1, 1)]#), "Q4"))))

I also tried this one where I removed the = before the date formula

=IF(AND(Date of Course@row >= [DATE(YEAR(TODAY()), 4, 1)]#, Date of Check@row < [DATE(YEAR(TODAY()), 7, 1)]#), "Q1", IF(AND(Date of Check@row >= [DATE(YEAR(TODAY()), 7, 1)]#, Date of Check@row < [DATE(YEAR(TODAY()), 10, 1)]#), "Q2", IF(AND(Date of Check@row >= [DATE(YEAR(TODAY()), 10, 1)]#, Date of Check@row < [DATE(YEAR(TODAY()), 1, 1)]#), "Q3", IF(AND(Date of Check@row >= [DATE(YEAR(TODAY()), 1, 1)]#, Date of Check@row < [DATE(YEAR(TODAY())+1, 1, 1)]#), "Q4"))))

Paul Newcome

There are a number of syntax issues with your posted formulas. Did you try the one I suggested?

Lorri Rose

yes, same result unfortunately. I'm probably missing something really simple

Paul Newcome

Ensure that any time you are referencing a column name that has spaces, number, and/or special characters in it, you are using [square brackets] around the column name.

[Column Name]@row

Does my suggested formula work with that update? If not, can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

Lorri Rose

Hi Paul, using the [ ] around my column name worked a treat. Thank you.

Paul Newcome

Happy to help. 👍️