I want to use a formula to convert a date into a year and quarter

Elizabeth Keller

 i.e. if the date is 9/15/2020 I want to convert it to "Q3 2020"

Kelly Drake

There might be an easier way to do this.... but here's my take....

=IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) = 2, MONTH(Date@row) = 3)), "Q1 ", IF(OR(MONTH(Date@row) = 4, MONTH(Date@row) = 5, MONTH(Date@row) = 6)), "Q2 ", IF(OR(MONTH(Date@row) = 7, MONTH(Date@row) = 8, MONTH(Date@row) = 9)), "Q3 ", IF(OR(MONTH(Date@row) = 10, MONTH(Date@row) = 11, MONTH(Date@row) = 12)), "Q4 ")))) + YEAR(Date@row)

Note - the space after the quarter helps keep your format of "Q3 2020". You could add the year in after the space but the way this formula is written, it should work even if you roll over into multiple years.

David Joyeuse

Hi @Elizabeth Keller

Another possibility here is this, though @Kelly Drake works pretty fine as well.

="Q" + ROUNDUP((MONTH([Date]@row)/4)) + " " + YEAR([Date]@row)

Hope it helped!

Kelly Drake

There might be an easier way to do this.... but here's my take....

=IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) = 2, MONTH(Date@row) = 3)), "Q1 ", IF(OR(MONTH(Date@row) = 4, MONTH(Date@row) = 5, MONTH(Date@row) = 6)), "Q2 ", IF(OR(MONTH(Date@row) = 7, MONTH(Date@row) = 8, MONTH(Date@row) = 9)), "Q3 ", IF(OR(MONTH(Date@row) = 10, MONTH(Date@row) = 11, MONTH(Date@row) = 12)), "Q4 ")))) + YEAR(Date@row)

Note - the space after the quarter helps keep your format of "Q3 2020". You could add the year in after the space but the way this formula is written, it should work even if you roll over into multiple years.

Elizabeth Keller

thank you

David Joyeuse

Hi @Elizabeth Keller

Another possibility here is this, though @Kelly Drake works pretty fine as well.

="Q" + ROUNDUP((MONTH([Date]@row)/4)) + " " + YEAR([Date]@row)

Hope it helped!

Elizabeth Keller

Thanks so much to both of you. I have it working!

Kelly Drake

@David Joyeuse that's a way easier formula! (Sadly, we're on a weird FY calendar - Oct-Dec = Q1)... but still thanks for sharing!

Jeff Kunkel

https://community.smartsheet.com/discussion/comment/262691#Comment_262691

This is a great solution and was just what I was looking for! Just one note - in order to report quarters it needs a very slight adjustment:

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

Dividing the Month by 4 will result in your year being divided into thirds of four months each rather than quarters of three months each.

John Reynolds

https://community.smartsheet.com/discussion/comment/262691#Comment_262691

Actually, to find the Quarter with this, the roundup would be divided by 3, not 4. For example if December (12), was divided by 4 it would return 3, or Q-3, and July (7) would return Q-2. Dividing by 3 gives you the right Quarter (Q-4 and Q-3). Other than that, it is a very slick formula!

Ranjini Walter

Hi @Elizabeth Keller

For this formula :="Q" + ROUNDUP((MONTH([Date]@row)/3)) + " " + YEAR([Date]@row),what is Q what value does it the from the sheet?

I tried this formula but its returning an invalid operation error message.

Need some help with the logic.

Thanks

Ranjini Walter

NJohnson

Hello,

I have tried both versions and I get an "UNPARSEABLE" error each time. Suggestions?