Simpler Fiscal Year and Quarter Formula

Hi,

Admittedly, I'm very new when it comes to spreadsheet formulas. I was given this formula for fiscal year and quarter calculations but I was wondering if there was a simpler way to do it. Also, it's in a calendar year format and our fiscal year is July 1 to June 30. I appreciate your help.

=IF(ISDATE([Completion Target]@row), "FY" + IF(MONTH([Completion Target]@row) = 1, RIGHT(YEAR([Completion Target]@row), 2), RIGHT(YEAR([Completion Target]@row) + 1, 2)) + "-Q" + IF(OR(MONTH([Completion Target]@row) = 1, MONTH([Completion Target]@row) >= 11), 4, IF(MONTH([Completion Target]@row) >= 8, 3, IF(MONTH([Completion Target]@row) >= 5, 2, 1))))

Thanks!

Find more posts tagged with

Accepted answers

All comments

Neil Watson

Hi Ashley, we use this for the fiscal year.

="FY" + RIGHT(IF(MONTH(TODAY()) > 6, YEAR(TODAY()) + 1, YEAR(TODAY())), 2)

Which shows (for this fiscal year) FY22.

=IF(ISDATE([Completion Target]@row), "FY" +RIGHT(IF(MONTH(TODAY()) > 6, YEAR(TODAY()) + 1, YEAR(TODAY())), 2) + "-Q" + IF(OR(MONTH([Completion Target]@row) = 1, MONTH([Completion Target]@row) >= 11), 4, IF(MONTH([Completion Target]@row) >= 8, 3, IF(MONTH([Completion Target]@row) >= 5, 2, 1))))

Ashley Ferguson

Thank you so much. Any thoughts on what I need to adjust so it reflects our quarters properly?

Q1: July/August/September

Q2: October/November/December

Q3: January/February/March

Q4: April/May/June

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.