Formatting months within a quarter

[bdenn]

Hello,
I have a simple data validation of each quarter in a year, C4. Once the quarter has been chosen it displays each month within that quarter in cells: F4,I4,& L4.

=IFS($C$4="Quarter 1","January-2021",$C$4="Quarter 2","April-2021",$C$4="Quarter 3","July-2021",$C$4="Quarter 4","October-2021")
=IFS($C$4="Quarter 1","February-2021",$C$4="Quarter 2","May-2021",$C$4="Quarter 3","August-2021",$C$4="Quarter 4","November-2021")
=IFS($C$4="Quarter 1","March-2021",$C$4="Quarter 2","June-2021",$C$4="Quarter 3","September-2021",$C$4="Quarter 4","December-2021")

Currently these months which are displayed are not in date format. How can I get them to be dates and not general text. Also, I do not what to mess with the year once it goes to 2022, cell A1 displays the current year and will be changed to 01/01/2022 once we hit the new year.

cells C6,F6,& I6 are all formatted to mm/dd/yyyy which you will see if not working.

Thank you for your time,
Bdenn

Quarters.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	01/01/2021		Year Start Date													Quarters
2	10/09/2021		Today's Date													Quarter 1
3																Quarter 2
4			Quarter 2			April-2021			May-2021			June-2021				Quarter 3
5																Quarter 4
6			April-2021			May-2021			June-2021			YTD
7			Sold	Profit	Shipping	Sold	Profit	Shipping	Sold	Profit	Shipping	Sold	Profit	Shipping
8	Product 1
9	Product 2
10	Product 3
11	Product 4
12	Product 5
Sheet1

Cell Formulas
Range	Formula
A2	A2	=TODAY()
F4	F4	=IFS($C$4="Quarter 1","January-2021",$C$4="Quarter 2","April-2021",$C$4="Quarter 3","July-2021",$C$4="Quarter 4","October-2021")
I4	I4	=IFS($C$4="Quarter 1","February-2021",$C$4="Quarter 2","May-2021",$C$4="Quarter 3","August-2021",$C$4="Quarter 4","November-2021")
L4	L4	=IFS($C$4="Quarter 1","March-2021",$C$4="Quarter 2","June-2021",$C$4="Quarter 3","September-2021",$C$4="Quarter 4","December-2021")
C6,F6,I6	C6	=F4

Cells with Data Validation
Cell	Allow	Criteria
C4:E4	List	=$P$2:$P$5

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

For F4 use

=DATE(YEAR(A1),RIGHT(C4)*3-2,1)

and just change the -2 to -1 for I4 & remove that part for L4

 

[bdenn]

Hello,
That worked great thank you for the information.

-Bdenn

Quarters.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	01/01/2021		Year Start Date													Quarters
2	10/09/2021		Today's Date													Quarter 1
3																Quarter 2
4			Quarter 3			July-2021			August-2021			September-2021				Quarter 3
5																Quarter 4
6			Jul-2021			Aug-2021			Sep-2021			YTD
7			Sold	Profit	Shipping	Sold	Profit	Shipping	Sold	Profit	Shipping	Sold	Profit	Shipping
8	Product 1
9	Product 2
10	Product 3
11	Product 4
12	Product 5
Sheet1

Cell Formulas
Range	Formula
A2	A2	=TODAY()
F4	F4	=DATE(YEAR(A1),RIGHT(C4)*3-2,1)
I4	I4	=DATE(YEAR(A1),RIGHT(C4)*3-1,1)
L4	L4	=DATE(YEAR(A1),RIGHT(C4)*3,1)
C6,F6,I6	C6	=F4

Cells with Data Validation
Cell	Allow	Criteria
C4:E4	List	=$P$2:$P$5

 

[Fluff]

You're welcome & thanks for the feedback.
Thanks also for updating your profile.