Convert Quarters to Date

MelissaYE

I have a list of dates that are Quarter/year (in a format like Q4 2031, Q1 2038, etc.) and would like to use a formula to show the last date in the quarter.

So:

Q1 2031 = 03/31/2031

Q2 2027 = 06/30/2027

Q3 2033 = 09/30/2033

Q4 2035 = 12/31/2035

I would appreciate help creating the appropriate statement!

Melissa Yamada

Hello @MelissaYE

Try this:

=IF(LEFT([Quarter / Year]@row, 2) = "Q1", "03" + "/" + "31" + "/" + RIGHT([Quarter / Year]@row, 4), IF(LEFT([Quarter / Year]@row, 2) = "Q2", "06" + "/" + "30" + "/" + RIGHT([Quarter / Year]@row, 4), IF(LEFT([Quarter / Year]@row, 2) = "Q3", "09" + "/" + "30" + "/" + RIGHT([Quarter / Year]@row, 4), IF(LEFT([Quarter / Year]@row, 2) = "Q4", "12" + "/" + "31" + "/" + RIGHT([Quarter / Year]@row, 4)))))

Change [Quarter / Year]@row to your Qtr/Yr column on the sheet.

Melissa Yamada

Hello @MelissaYE

Try this:

=IF(LEFT([Quarter / Year]@row, 2) = "Q1", "03" + "/" + "31" + "/" + RIGHT([Quarter / Year]@row, 4), IF(LEFT([Quarter / Year]@row, 2) = "Q2", "06" + "/" + "30" + "/" + RIGHT([Quarter / Year]@row, 4), IF(LEFT([Quarter / Year]@row, 2) = "Q3", "09" + "/" + "30" + "/" + RIGHT([Quarter / Year]@row, 4), IF(LEFT([Quarter / Year]@row, 2) = "Q4", "12" + "/" + "31" + "/" + RIGHT([Quarter / Year]@row, 4)))))

Change [Quarter / Year]@row to your Qtr/Yr column on the sheet.

MelissaYE

This works perfectly, thanks so much!!!

Melissa Yamada

Happy to help! 😊