How to get month and year from date

Tyrel Smith

New Member
Joined
Nov 20, 2020
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
Could someone please help me.

I have a 2 columns one with a date and I want to put the month of the date in the other, see below

11/01/2020I want the month and year of the date here ie. Jan 2020
12/02/2020ie. Feb 2020
13/03/2020Ie. Mar 2020
14/04/2020ie. April 2020

Please help.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,121
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

If you are looking for a text result then try this

20 11 21.xlsm
AB
1
211/01/2020Jan 2020
312/02/2020Feb 2020
413/03/2020Mar 2020
514/04/2020Apr 2020
Month Year
Cell Formulas
RangeFormula
B2:B5B2=TEXT(A2,"mmm yyyy")
 

Tyrel Smith

New Member
Joined
Nov 20, 2020
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the MrExcel board!

If you are looking for a text result then try this

20 11 21.xlsm
AB
1
211/01/2020Jan 2020
312/02/2020Feb 2020
413/03/2020Mar 2020
514/04/2020Apr 2020
Month Year
Cell Formulas
RangeFormula
B2:B5B2=TEXT(A2,"mmm yyyy")
That's the problem I'm having I tried that but because I have other things leading off of this column it cant be text so I want to sort of copy that what is in the first column and format it to show only the month and year
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,121
Office Version
  1. 365
Platform
  1. Windows
In that case just use this formula

20 11 21.xlsm
AB
1
211/01/2020Jan 2020
312/02/2020Feb 2020
413/03/2020Mar 2020
514/04/2020Apr 2020
Month Year
Cell Formulas
RangeFormula
B2:B5B2=A2


.. and format that result column with a custom format of mmm yyyy like this

1605961877101.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,121
Office Version
  1. 365
Platform
  1. Windows
I tried that but when I do that it stays the same it doesn't want to format.
In that case most likely your original 'dates' are text. Follow the same process as above but change the cell formula to
Excel Formula:
=A2+0
 

Forum statistics

Threads
1,144,158
Messages
5,722,824
Members
422,460
Latest member
VBA_Noob01

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