Taking into account month AND year

Hi everyone,

I have two columns. One is the date that the form was submitted, and the other tells us which quarter we received it. Our fiscal year is from April 1 to March 31. This formula works great, but I've realized it won't account for the change in year. Currently, we are in 2020/21, but as of April 1, we will be in 2021/22. How can I adjust my formula to identify the year?

I think Ideally it could appear Q1-20/21 or any variation really. I just have no idea how to incorporate the YEAR function.

Find more posts tagged with

Accepted answers

David Tutwiler

I think this might get what you're looking for:

="Q" + IF(MONTH(Created@row) >= 10, "3-" + RIGHT(YEAR(Created@row), 2) + "/" + (VALUE(RIGHT(YEAR(Created@row), 2)) + 1), IF(MONTH(Created@row) >= 7, "2-" + RIGHT(YEAR(Created@row), 2) + "/" + (VALUE(RIGHT(YEAR(Created@row), 2)) + 1), IF(MONTH(Created@row) >= 4, "1-" + RIGHT(YEAR(Created@row), 2) + "/" + (VALUE(RIGHT(YEAR(Created@row), 2)) + 1), IF(MONTH(Created@row) >= 1, "4-" + (VALUE(RIGHT(YEAR(Created@row), 2)) - 1 + "/" + RIGHT(YEAR(Created@row), 2))))))

All comments

David Tutwiler

I think this might get what you're looking for:

Lila De Vera

Perfect, thanks @David Tutwiler

David Tutwiler

No problem.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.