Fiscal Quarter Issue

Genevieve P.

Hi @tgnockles

I'm glad I could help! I have to admit I don't even remember writing this formula.

Yes, we can adjust it for your dates, we'll just want to add in the DAY function. Try the following:

="Q" + IF(OR(AND(MONTH(Date@row) >= 11, DAY(Date@row) >= 6), MONTH(Date@row) = 1, AND(MONTH(Date@row) = 2, DAY(Date@row) < 6)), "1FY", IF(OR(AND(MONTH(Date@row) = 8, DAY(Date@row) >= 6), MONTH(Date@row) >= 9), "4FY", IF(OR(AND(MONTH(Date@row) = 5, DAY(Date@row) >= 6), MONTH(Date@row) >= 6), "3FY", "2FY"))) + IF(MONTH(Date@row) >= 11, RIGHT(INT(YEAR(Date@row) + 1), 2), RIGHT(YEAR(Date@row), 2))

Cheers,

Genevieve

tgnockles

@Genevieve P.

Worked like an absolute charm, thanks again!!!

tgnockles

@Genevieve P. I am working on this same sheet and am coming across an issue with using multiple forms to populate the same row. I was wondering if I have a destination sheet, where I need to reference 3 types of data from 2 other support sheets that I have made. Those are (cost of airfare, date travel was booked, and cost on non-airfare). One of the support sheets has a form that will record airfare cost, and travel date booked from recipients. The other sheet will record cost on non-airfare from its recipients. I was wondering if you know whether or not there's a cross-reference formula for each of the 3 columns that I can reference back to my main destination sheet? As future information is entered and auto populated on those support sheets, then it will be referenced/pulled into the corresponding/matching row and column from where the other matching information already is. I cannot figure out if it supposed to be a VLOOKUP, INDEX, COLLECT, or some other type of formula that would bring that information back to my destination sheet.

Genevieve P.

Hi @tgnockles

It sounds like either an INDEX(MATCH or an INDEX(COLLECT combination should work for you!

Check out this Help Article and let us know if you need more help: Formula combinations for cross sheet references

If you do want help setting this up, it would be useful to see screen captures of your support sheet(s) and your intake sheet, but please block out sensitive data.

Thanks!

Genevieve

Kariv

Hi @Genevieve P. I've been trying to follow your formula to convert a calendar date to a FY date, and I can't quite get it right. Hoping you can help. Our FY starts June 1, so our quarters would look like this:

Q1: months 6-8 + 1 yr

Q2: Months 9-11 +1 yr

Q3: Month 12 + 1 yr and Months 1, 2

Q4: Months 3, 4, 5

The formula I have come up with is below. It is working great except for calendar dates in December are showing as Q2 + 1 yr, and it should be Q3 + 1 yr. For example, if the calendar date is 12/02/2024 the fiscal year should be Q3 2025, but it is calculating as Q2 2025

=IFERROR(IF(MONTH([Finish Date]@row) >= 9, "Q2 " + (YEAR([Finish Date]@row) + 1), IF(MONTH([Finish Date]@row) >= 6, "Q1 " + (YEAR([Finish Date]@row) + 1), IF(MONTH([Finish Date]@row) >= 3, "Q4 " + YEAR([Finish Date]@row), "Q3 " + YEAR([Finish Date]@row)))), "")

Genevieve P.

Hey @Kariv

The reason you're seeing Q2 is because your first statement looks to see if the month is >= 9, which is inclusive of 12.

There are lots of ways to do this, but an easy one is to add another IF statement at the front specifically for December:

=IFERROR(IF(MONTH([Finish Date]@row) = 12, "Q3 " + (YEAR([Finish Date]@row) + 1), IF(MONTH([Finish Date]@row) >= 9, "Q2 " + (YEAR([Finish Date]@row) + 1), IF(MONTH([Finish Date]@row) >= 6, "Q1 " + (YEAR([Finish Date]@row) + 1), IF(MONTH([Finish Date]@row) >= 3, "Q4 " + YEAR([Finish Date]@row), "Q3 " + YEAR([Finish Date]@row))))), "")

Cheers,

Genevieve

Kariv

Thank you, @Genevieve P. ! Formulas are not my strong suit, but I'm slowly getting better. You always provide such helpful explanations!

I appreciate you and this forum!

Kari

Genevieve P.

Thanks for the kind words! I'm glad I could help 🙂