Month() function doesn't work to convert January to 1

dngandbored

New Member
Joined
Nov 13, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi there, I tried to use the month() function to convert month (January, February etc) to numeric format in Excel. It doesn't work. I don't know what went wrong. Please see my screenshot below
I typed "March" in C4 and try to convert to "3" by using month( ) but I couldn't :( :cry:please help!!
1636858264747.png
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,309
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Welcome to the forum, you need a real date for the month function to work, try...

Book1
CD
1
2
3January1
4February2
5March3
6April4
7May5
8June6
9July7
10August8
11September9
12October10
13November11
14December12
Sheet3
Cell Formulas
RangeFormula
D3:D14D3=MONTH(DATEVALUE(C3&" 1"))
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,477
Office Version
  1. 2013
Platform
  1. Windows
Hi & welcome to MrExcel!

The MONTH worksheet function expects a date type instead of a text type. So tell us what you're trying to achieve.
 

dngandbored

New Member
Joined
Nov 13, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi & welcome to MrExcel!

The MONTH worksheet function expects a date type instead of a text type. So tell us what you're trying to achieve.
I basically want to rank my data from January to December, I have multi year data so I was thinking to convert 2014 January to date format so I can rank them...
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,309
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
That isn't the formula I posted, note where the space is in the " 1" (much easier if you copied what I posted by clicking the
1636859175564.png
and pasted into your sheet)
 

dngandbored

New Member
Joined
Nov 13, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

That isn't the formula I posted, note where the space is in the " 1" (much easier if you copied what I posted by clicking the View attachment 51185 and pasting into your sheet)
I just did and I got this.. could it be a version issue with Excel?

1636859292158.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,862
Office Version
  1. 365
Platform
  1. Windows
OP's original formula as posted works for me.

21 11 14.xlsm
CD
4March3
Month
Cell Formulas
RangeFormula
D4D4=MONTH(C4&1)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,862
Office Version
  1. 365
Platform
  1. Windows
In the image from post #4 it looks like you have incorrect quote marks - they need to be the 'vertical' quote marks not the sloped ones.

1636859687797.png


However, that does not explain the failure of the original formula that has no quote marks in it.
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,021
Latest member
naimathulla

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