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

#### albertc30

##### Well-known Member
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.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### MARK858

##### MrExcel MVP
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
CD
915/03/2021Mar
Sheet2
Cell Formulas
RangeFormula
D9D9=TEXT(C9,"mmm")

#### Michael M

##### Well-known Member
Why not use
Excel Formula:
``=TEXT(C9,"mmm")``

##### Well-known Member
Hi Albertc30,

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

Cell Formulas
RangeFormula
C3:C16C3=C2+25
E2:E16E2=CHOOSE(MONTH(C2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
G2:G16G2=TEXT(C2,"mmm")

#### steve the fish

##### Well-known Member
Or if its just for visuals then you can just format the cells. Custom mmm

#### albertc30

##### Well-known Member
Hi Albertc30,

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

Cell Formulas
RangeFormula
C3:C16C3=C2+25
E2:E16E2=CHOOSE(MONTH(C2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
G2:G16G2=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

Replies
2
Views
147
Replies
5
Views
151
Replies
4
Views
108
Replies
3
Views
103
Replies
3
Views
59

1,127,743
Messages
5,626,619
Members
416,195
Latest member
tonmcg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back