Auto fill in period of date based on the date in a certain cell

[Goddess]

Hi,

I'm trying to get my spreadsheet to auto fill up the yellow cells, based on the info in the green cell. Example as below:

Cell A6 is the first 3 months period from cell B1. For example, if B1 is 1 Feb 21, then A6 will be "Feb - Apr 21". Cells B6:D6 are subsequent 3 months period from the corresponding cells on the left. Is there anyway to do it?

Thanks!

 

[maabadi]

How About:

Book1
	A	B	C	D	E
1	From	6/1/2021
2	To	5/31/2021
3
4
5	Session 1	Session 2	Session 3	Session 4
6	Jun - Aug 21	Sep - Nov 21	Dec - Feb 22	Mar - May 22
7
8
9
Sheet3

Cell Formulas
Range	Formula
A6:D6	A6	=CONCAT(TEXT(EOMONTH($B$1,(COLUMNS($A$6:A6)-1)*3),"MMM"), " - ", TEXT(EOMONTH($B$1,COLUMNS($A$6:A6)*3-2) + 1,"MMM"), " ",TEXT(EOMONTH($B$1,COLUMNS($A$6:A6)*3-2) + 1,"YY"))

 

[maabadi]

Or this:

Book1
	A	B	C	D	E	F	G
1	From	6/1/2021
2	To	6/30/2022
3
4
5	Session 1	Session 2	Session 3	Session 4	Session 5	Session 6
6	Jun - Aug 21	Sep - Nov 21	Dec - Feb 22	Mar - May 22	Jun - Jun 22
7
8
9
Sheet3

Cell Formulas
Range	Formula
A6:F6	A6	=IF(INT(((YEAR($B$2)-YEAR($B$1))*12 + MONTH($B$2)-MONTH($B$1) + 1)/3)>=COLUMNS($A$6:A6),CONCAT(TEXT(EOMONTH($B$1,(COLUMNS($A$6:A6)-1)*3),"MMM"), " - ", TEXT(EOMONTH($B$1,COLUMNS($A$6:A6)*3-2) + 1,"MMM"), " ",TEXT(EOMONTH($B$1,COLUMNS($A$6:A6)*3-2) + 1,"YY")),IF((((YEAR($B$2)-YEAR($B$1))*12 + MONTH($B$2)-MONTH($B$1) + 4)) >COLUMNS($A$6:A6) * 3,CONCAT(TEXT(EOMONTH($B$1,(COLUMNS($A$6:A6)-1)*3),"MMM"), " - ", TEXT(EOMONTH($B$2,0),"MMM"), " ",TEXT(EOMONTH($B$2,0),"YY")),""))

 

[Goddess]

=IF(INT(((YEAR($B$2)-YEAR($B$1))*12 + MONTH($B$2)-MONTH($B$1) + 1)/3)>=COLUMNS($A$6:A6),CONCAT(TEXT(EOMONTH($B$1,(COLUMNS($A$6:A6)-1)*3),"MMM"), " - ", TEXT(EOMONTH($B$1,COLUMNS($A$6:A6)*3-2) + 1,"MMM"), " ",TEXT(EOMONTH($B$1,COLUMNS($A$6:A6)*3-2) + 1,"YY")),IF((((YEAR($B$2)-YEAR($B$1))*12 + MONTH($B$2)-MONTH($B$1) + 4)) >COLUMNS($A$6:A6) * 3,CONCAT(TEXT(EOMONTH($B$1,(COLUMNS($A$6:A6)-1)*3),"MMM"), " - ", TEXT(EOMONTH($B$2,0),"MMM"), " ",TEXT(EOMONTH($B$2,0),"YY")),""))

Hi Maabadi,

Thanks so much!

 

[Peter_SSs]

This is a fair bit shorter & I think still does what you want?

21 09 26.xlsm
	A	B	C	D	E
1		1-Jan-21
2		31-Dec-21
3
4
5
6	Jan - Mar 21	Apr - Jun 21	Jul - Sep 21	Oct - Dec 21
Date Ranges

Cell Formulas
Range	Formula
A6:E6	A6	=IF(EDATE($B1,COLUMNS($A:A)*3-3)>$B2,"",TEXT(EDATE($B1,COLUMNS($A:A)*3-3),"mmm")&TEXT(MIN(EDATE($B1,COLUMNS($A:A)*3-1),$B2)," - mmm yy"))

 

[Goddess]

This is a fair bit shorter & I think still does what you want?

21 09 26.xlsm
	A	B	C	D	E
1		1-Jan-21
2		31-Dec-21
3
4
5
6	Jan - Mar 21	Apr - Jun 21	Jul - Sep 21	Oct - Dec 21
Date Ranges

Cell Formulas
Range	Formula
A6:E6	A6	=IF(EDATE($B1,COLUMNS($A:A)*3-3)>$B2,"",TEXT(EDATE($B1,COLUMNS($A:A)*3-3),"mmm")&TEXT(MIN(EDATE($B1,COLUMNS($A:A)*3-1),$B2)," - mmm yy"))

Hi Peter,

Thanks!!

 

[Peter_SSs]

You're welcome.