Long formula shorter and how to say "*" all days?

[albertc30]

Hi all.

I hope everyone is well and been keeping safe.

I have tried this formula and I was happy untill now as, one it's way too big and two, it's not really doing what I wanted it to do.

=IF(C9=DATEVALUE("01/21"),"Jan",IF(C9=DATEVALUE("02/21"),"Feb",IF(C9=DATEVALUE("03/21"),"Mar",IF(C9=DATEVALUE("04/21"),"Apr",IF(C9=DATEVALUE("05/21"),"May",IF(C9=DATEVALUE("06/21"),"Jun", IF(C9=DATEVALUE("07/21"),"Jul", IF(C9=DATEVALUE("08/21"),"Aug", IF(C9=DATEVALUE("09/21"),"Sep", IF(C9=DATEVALUE("10/21"),"Oct",IF(C9=DATEVALUE("11/21"),"Nov", IF(C9=DATEVALUE("12/21"),"Dec",))))))))))))

The above only works for the first day of the month. I just tried say 01/02/21 in C9 and I get Feb but if I try 02/02/21 it shows nothing.

Now, I tried to do it the old way by ("*/02/21") using the * but the formula doesn't like it.

What amI doing wrong here please?

Much appreciated for all the help.

Thanks.
Albert.

 

[MARK858]

Albert, I'm a bit confused. It looks like all you are trying to do is return the mmm of any given date so why can't you just do

Book2
	C	D
9	15/03/2021	Mar
Sheet2

Cell Formulas
Range	Formula
D9	D9	=TEXT(C9,"mmm")

 

[Michael M]

Why not use

=TEXT(C9,"mmm")

 

[Toadstool]

Hi Albertc30,

Here's a couple of easier ways to convert a date to its month:

Albertc30.xlsx
	C	D	E	F	G
1	Date		CHOOSE		TEXT
2	01-Jan-21		Jan		Jan
3	26-Jan-21		Jan		Jan
4	20-Feb-21		Feb		Feb
5	17-Mar-21		Mar		Mar
6	11-Apr-21		Apr		Apr
7	06-May-21		May		May
8	31-May-21		May		May
9	25-Jun-21		Jun		Jun
10	20-Jul-21		Jul		Jul
11	14-Aug-21		Aug		Aug
12	08-Sep-21		Sep		Sep
13	03-Oct-21		Oct		Oct
14	28-Oct-21		Oct		Oct
15	22-Nov-21		Nov		Nov
16	17-Dec-21		Dec		Dec
Sheet1

Cell Formulas
Range	Formula
C3:C16	C3	=C2+25
E2:E16	E2	=CHOOSE(MONTH(C2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
G2:G16	G2	=TEXT(C2,"mmm")

 

[steve the fish]

Or if its just for visuals then you can just format the cells. Custom mmm

 

[albertc30]

Hi Albertc30,

Here's a couple of easier ways to convert a date to its month:

Albertc30.xlsx
	C	D	E	F	G
1	Date		CHOOSE		TEXT
2	01-Jan-21		Jan		Jan
3	26-Jan-21		Jan		Jan
4	20-Feb-21		Feb		Feb
5	17-Mar-21		Mar		Mar
6	11-Apr-21		Apr		Apr
7	06-May-21		May		May
8	31-May-21		May		May
9	25-Jun-21		Jun		Jun
10	20-Jul-21		Jul		Jul
11	14-Aug-21		Aug		Aug
12	08-Sep-21		Sep		Sep
13	03-Oct-21		Oct		Oct
14	28-Oct-21		Oct		Oct
15	22-Nov-21		Nov		Nov
16	17-Dec-21		Dec		Dec
Sheet1

Cell Formulas
Range	Formula
C3:C16	C3	=C2+25
E2:E16	E2	=CHOOSE(MONTH(C2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
G2:G16	G2	=TEXT(C2,"mmm")

Hi.

Much appreciated for your aproach. I must admit I have never seen or used the choose function.

Thanks to everyone. As always, ready to give a helping hand.
Much appreciated.

Regards,
Albert