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

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
Nov 12, 2010
Messages
13,989
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Why not use
Excel Formula:
=TEXT(C9,"mmm")
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,392
Office Version
  1. 2016
Platform
  1. Windows
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")
 
Solution

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
Or if its just for visuals then you can just format the cells. Custom mmm
 

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top