Date Format

[dodokh]

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

 

[MarcelBeug]

Try format cells - custom format d-mmm-yy

 

[dodokh]

Try format cells - custom format d-mmm-yy

Same problem .. not working.

 

[saint001]

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]

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.

 

[saint001]

No worries

 

[Peter_SSs]

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]

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]

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]

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