Separating out a date by Quarter and Year

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

Find more posts tagged with

Accepted answers

NJohnson

That worked!! Thank you!!

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.