converting a date to "Quarter 3 2023/24"

[scotthannaford1973]

Hi

I'd like to be able to show a date as Quarter and fiscal year, so instead of 10/4/24 it would be Q1 2024/25 and 10/12/24 would be Q3 2024/25 etc

so - quarter 1 starts in April.

Any help would be appreciated!

 

[Joe4]

Try this, for a date in cell A1:

="Q" & IF(MONTH(A1)<=3,4 & " " & YEAR(A1)-1 & "/" & TEXT(A1,"yy"),INT((MONTH(A1)-1)/3) & " " & TEXT(A1,"yyyy") & "/" & TEXT(YEAR(A1)+1,"yy"))

Assuming your dates are in d/m/yyyy format, here are the results for the first day of each month:

 

[scotthannaford1973]

Try this, for a date in cell A1:

="Q" & IF(MONTH(A1)<=3,4 & " " & YEAR(A1)-1 & "/" & TEXT(A1,"yy"),INT((MONTH(A1)-1)/3) & " " & TEXT(A1,"yyyy") & "/" & TEXT(YEAR(A1)+1,"yy"))

Assuming your dates are in d/m/yyyy format, here are the results for the first day of each month:
View attachment 109532

thanks, Joel - that's almost it, but it's doing 2024/05 instead of 2024/25

 

[Joe4]

thanks, Joel - that's almost it, but it's doing 2024/05 instead of 2024/25

Ah! How did I miss that? Minor correction:

="Q" & IF(MONTH(A1)<=3,4 & " " & YEAR(A1)-1 & "/" & TEXT(A1,"yy"),INT((MONTH(A1)-1)/3) & " " & TEXT(A1,"yyyy") & "/" & TEXT(EDATE(A1,12),"yy"))

Result:

Sorry about that!

 

[scotthannaford1973]

Ah! How did I miss that? Minor correction:

="Q" & IF(MONTH(A1)<=3,4 & " " & YEAR(A1)-1 & "/" & TEXT(A1,"yy"),INT((MONTH(A1)-1)/3) & " " & TEXT(A1,"yyyy") & "/" & TEXT(EDATE(A1,12),"yy"))

Result:
View attachment 109533

perfect - thanks, Joe - I'd never have worked that out!

 

[Joe4]

You are welcome.

It may be long, but not really that complicated. Just working out each bit and sewing it all together.
I imagine some brilliant mind here may be able to come up with a more concise solution, but it works!