Formula for Quater Dates?

tgnockles

Hello there,

I've already built a formula out so that it includes the Quarter and fiscal year, and it works great, however I was wondering if it is possible to make this formula include the extra 5 days of each overlapping quarter? I have no idea how to incorporate this into my working formula however. Any help would be greatly appreciated! Our quarters are:

Q1: Nov - Jan

Q2: Feb - Apr

Q3: May - July

Q4: August - Oct.

Here's the working formula I have: ="Q" + IF(OR(MONTH([Return Date]@row) >= 11, MONTH([Return Date]@row) = 1), "1FY", IF(MONTH([Return Date]@row) >= 8, "4FY", IF(MONTH([Return Date]@row) >= 5, "3FY", "2FY"))) + IF(MONTH([Return Date]@row) >= 11, RIGHT(INT(YEAR([Return Date]@row) + 1), 2), RIGHT(YEAR([Return Date]@row), 2))

So new quarters would be.

Q1: 11/6 - February 5th

Q2: 2/6 - 5/5

Q3: 5/6 - 8/5

Q4: 8/6 - 11/5

What would the new formula look like with keeping the above dates in mind?

Paul Newcome

You would have to use the DATE function.

=IF(AND([Return Date]@row>= DATE(yyyy, mm, dd), [Retunr Date]@row<= DATE(yyyy, mm, dd)),

tgnockles

@Paul Newcome, this is way above my knowledge level, how many times would I have to input that function into my current formula, and where do I place it? Also, is there a way to be able to calculate the quarter from the specific dates mentioned above without having to go into the formula once a year and change the year out from the formula that you mentioned? So far, I've tried this, and got the #INVALID OPERATION message:

="Q" + IF(OR(MONTH([Date traveler booked Airfare]@row) >= 11, =IF(AND([Return Date]@row >= DATE(2023, 11, 6), [Return Date]@row <= DATE(2023, 2, 5)), MONTH([Date traveler booked Airfare]@row) = 1), "1FY", IF(MONTH([Date traveler booked Airfare]@row) >= 8, "4FY", IF(MONTH([Date traveler booked Airfare]@row) >= 5, "3FY", "2FY"))) + IF(MONTH([Date traveler booked Airfare]@row) >= 11, RIGHT(INT(YEAR([Date traveler booked Airfare]@row) + 1), 2), RIGHT(YEAR([Date traveler booked Airfare]@row), 2)))

Paul Newcome

The AND function would go in place of your existing MONTH arguments.

So right now you are saying (for example)

.......IF(MONTH([Return Date]@row)>= 8, "4FY".......

What it would now say:

.......IF(AND([Return Date]@row>= DATE(2023, 08, 06), [Return Date]@row<= DATE(2023, 11, 05)), "4FY".......

As for the year, you would use a YEAR function within the DATE function to evaluate the year in the date column then subtract 1, add 1, or leave it alone depending on which way you need to go with it.

.......IF(AND([Return Date]@row>= DATE(YEAR([Return Date]@row) - 1, 11, 06), [Return Date]@row<= DATE(YEAR([Return Date]@row), 11, 05)), "1FY".......

tgnockles

@Paul Newcome thanks!!

Paul Newcome

Happy to help. 👍️