Taking into account month AND year

Lila De Vera

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.

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

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

Lila De Vera

Perfect, thanks @David Tutwiler

David Tutwiler

No problem.