Combining month label with year

[gauchofife]

Hello,

I have some complicated date formulas built off of a previous setup. Basically the complicated formulas (not shown) are all already built off of these simple date formulas / results:





Essentially, Row 2 is pulling the three letter month names based on the date in A1. When you change A1, the month names change to the three months of that quarter.

Row 4 is fine.

But rows 5-6 basically need to combine the month names from Row 2 with the year that is entered in A1 (i.e. they just need to be year 2024 and not 2025 here). I am working on rebuilding everything so that the month names are dates instead and everything feeds off that. But for now, I just need to adjust the formulas in A5:C7 so that the results are 2024 instead.

Thank you in advance!

(I tried installing the xl2bb add-in but it didn't work for me unfortunately, so I apologize that these are screenshots. Hoping that it's an easy solution )

 

[Artik]

Try:

A2    =TEXT(DATE(YEAR(A1),(FLOOR((MONTH(A1)-1)/3,1)*3)+1,1),"mmm")
B2    =TEXT(DATE(YEAR(A1),(FLOOR((MONTH(A1)-1)/3,1)*3)+2,1),"mmm")
C2    =TEXT(DATE(YEAR(A1),(FLOOR((MONTH(A1)-1)/3,1)*3)+3,1),"mmm")

A4    =DATE(YEAR(A1),(FLOOR((MONTH(A1)-1)/3, 1)*3)+1,1)
A5    =A4
A6    =EOMONTH(A5,0)

B4    =A4
B5    =DATE(YEAR(A5),MONTH(A5)+1,1)
B6    =EOMONTH(B5,0)

C4    =A4
C5    =DATE(YEAR(A5),MONTH(A5)+2,1)
C6    =EOMONTH(C5,0)

Artik

 

[gauchofife]

This works, thank you Artik!