Date Format

dodokh

Board Regular
Joined
Sep 11, 2009
Messages
133
Hello

How can I change 1-12-2015 to 1-Dec-15 ?

I have tried format cells > Date but it didn't work.

Thank in advance
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

saint001

New Member
Joined
Mar 13, 2016
Messages
5
I have also faced this problem in the past. Here is what I had done.


1. select the column where you are having date
2. Go to "Data" tab
3. Choose "Text to column"
4. Choose "Delimited" from the pop-up
5. Click "Next"
6. Uncheck all (tab, semicolumn,comma,space & other)
7. Click "next"
8.From "Column data format" select "Date"
9. Select "DMY"
10. Click "Finish"


You might wonder why I said the above 10 steps. This is to transform the column to proper date format. Now you can format it as you want. In your case
11. Select the column with date
12. Go to "Format" & select the type d-mmm-yy
13. Click "Ok"


Hope this helps
 

dodokh

Board Regular
Joined
Sep 11, 2009
Messages
133

ADVERTISEMENT

I have also faced this problem in the past. Here is what I had done.


1. select the column where you are having date
2. Go to "Data" tab
3. Choose "Text to column"
4. Choose "Delimited" from the pop-up
5. Click "Next"
6. Uncheck all (tab, semicolumn,comma,space & other)
7. Click "next"
8.From "Column data format" select "Date"
9. Select "DMY"
10. Click "Finish"


You might wonder why I said the above 10 steps. This is to transform the column to proper date format. Now you can format it as you want. In your case
11. Select the column with date
12. Go to "Format" & select the type d-mmm-yy
13. Click "Ok"


Hope this helps

Done.

Thank you.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

1. select the column where you are having date
2. Go to "Data" tab
3. Choose "Text to column"
4. Choose "Delimited" from the pop-up
5. Click "Next"
6. Uncheck all (tab, semicolumn,comma,space & other)
7. Click "next"
8.From "Column data format" select "Date"
9. Select "DMY"
10. Click "Finish"
A few additional comments on this approach that might save you a few mouse-clicks. :)

4. This step is not actually required. The method will work fine with either Delimited or Fixed width chosen.
6. If you have chosen 'Delimited' at step 4, unless you happen to have the particular date separator (in this OP's case a "-" character) in the 'Other' box, there is no need to remove any existing delimiters.
8. You can also save yourself this click, just performing step 9 will automatically mark the 'Date' option for you.
 

Alsooneo

New Member
Joined
Sep 21, 2015
Messages
7
Hi,

I have a similar issue with re-formatting the date column from Mar 05 2015 to 5/3/2015. I tried Format cells, I used the format painter and I also tried the steps you listed in the workflow above. It simply would not update the date format.



Alsooneo
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a similar issue with re-formatting the date column from Mar 05 2015 to 5/3/2015. I tried Format cells, I used the format painter and I also tried the steps you listed in the workflow above. It simply would not update the date format.



Alsooneo
In your case, you would need to
- remove Space from the Delimited options, if it is marked
- choose MDY in the Date option list because that is the format of your date as it is at the moment.
 

Alsooneo

New Member
Joined
Sep 21, 2015
Messages
7
In your case, you would need to
- remove Space from the Delimited options, if it is marked
- choose MDY in the Date option list because that is the format of your date as it is at the moment.

Hi Peter,

Many thanks - it works - the key for me is selecting the MDY in the Date option as the date was in that format. I then re-format it to dd/mm/yyyy in EXCEL.

Regards,

Alsooneo
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,024
Members
414,037
Latest member
Roamingsmile

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