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

Options

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?

Tags:
«1

• ✭✭✭✭✭✭
edited 06/24/24
Options

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.

School of Sheets (Smartsheet Partner)

If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

• Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

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)))

• Options

Awesome thank you!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

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

• ✭✭✭✭✭✭
Options

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?

• Options

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"))))

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!