Separating out a date by Quarter and Year

NJohnson

Hello,

I have an End Date column (which is formatted as mm/dd/yyyy). I need a formula to show as Q1-2022 (example) for any given date in the column.

In excel, I was able to find a formula that works but transferring it to Smartsheet I receive an UNPARSEABLE error message. That formula is: ="Q"&INT((MONTH(End Date)+2)/3)&YEAR(End Date)

I believe one of my issues is Smartsheet uses + instead of & but from there I feel lost (probably trying to figure it out for too long now). Any help is greatly appreciated.

Thank you in advance

NJohnson

That worked!! Thank you!!

Hudson_TMR

Consider if then logic as per below:

https://community.smartsheet.com/discussion/42386/return-value-if-between-dates

NJohnson

Hello,

Thank you for the suggestion.

This one works but it only gives me which Quarter: =IF(MONTH([End Date]@row) <= 3, "Q1", IF(MONTH([End Date]@row) <= 6, "Q2", IF(MONTH([End Date]@row) <= 9, "Q3", "Q4")))

I read that + is used to connect for Smartsheet instead of & but I get UNPARSEABLE when I tried to add it:

=IF(MONTH([End Date]@row) <= 3, "Q1" + YEAR([End Date], IF(MONTH([End Date]@row) <= 6, "Q2" =YEAR([End Date], IF(MONTH([End Date]@row) <= 9, "Q3" + YEAR([End Date], "Q4" + YEAR([End Date])))

I feel like I am close!!!

Paul McGuinness

Hi @NJohnson

You did have the detail but needed to take care of the Quarter calculation before adding the year piece on after that was worked out.

The below should work

=IF(MONTH([End date]@row) <= 3, "Q1", IF(MONTH([End date]@row) <= 6, "Q2", IF(MONTH([End date]@row) <= 9, "Q3", "Q4"))) + "-" + YEAR([End date]@row)

Hope that helps

Thanks

Paul

NJohnson

That worked!! Thank you!!