How to convert date to Month/Year only

Bryan123

New Member
Joined
May 23, 2019
Messages
41
Hi,

can you please help me convert a date to month and year only. I have series of dates with the format (dd-mm-yyyy). I have customed the format to mm-yyyy but the day is still showing especially when I create a table and choose month. If I can just delete or remove the day, it would be better. Thank you for your assistance.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
You cant remove the day from the date and it still remain a date. You can format it to look like mm-yyyy and then it remains a date or you can actually turn it to text but then it doesnt act like a date (eg you cant sort by it). What is it you want?
 
Upvote 0
Hi,
Well, my file has dates but doesn't have a column to which month those dates belong to. Since the file doesnt have the month column when it was first created, I'd like to create a new column that will automatically identify the month the dates belong. I'm going back and forth. Im sorry. I hope this makes sense.
 
Upvote 0
If A1 contains a date then this:

=0+TEXT(A1,"yyyymm")

creates a number (you may need to format to general) which can be sorted. Does that work for you?
 
Upvote 0
Assuming the date is in column A you can use one of the following formulas in the new column,

to get the month number use =MONTH(A1)
to get show month full name use =TEXT(A1,”MMMM”)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top