Forcing a specific date format

D

DianeMcP

I've set the date format within the cells to appear as dd-mon-yy
(12-Oct-05)....Doesn't excel automatically change an entry entered as Oct 12
2005 to 12-Oct-05?

If it is supposed to do that, why are my cells not automatically
reformatting to the syntax I want?
 
D

DianeMcP

Hi Anne,

When I went back into the spreadsheet, I just chose one of the date formats
that were already there, and it showed 04-Mar-05....it was probably me that
assumed that the formatting in the code was dd-mon-yy.

I've gone back into it and used "custom" and put in dd-mmm-yy.

--
Thanks,
Diane


Anne Troy said:
Likely because it should be dd-mmm-yy, not dd-mon-yy.
************
Anne Troy
www.OfficeArticles.com
Click to expand...
 
D

DianeMcP

Update: I tried the custom and "dd-mmm-yy" - and it still does not change an
entry from Sept 20, 2005 to 20-Sept-05. Any other suggestions, anyone?
Diane

Hi Anne,

When I went back into the spreadsheet, I just chose one of the date formats
that were already there, and it showed 04-Mar-05....it was probably me that
assumed that the formatting in the code was dd-mon-yy.

I've gone back into it and used "custom" and put in dd-mmm-yy.

--
Thanks,
Diane


--
Thanks,
Diane


Anne Troy said:
Likely because it should be dd-mmm-yy, not dd-mon-yy.
************
Anne Troy
www.OfficeArticles.com
Click to expand...
 
R

Roger Govier

Hi Diane

You could enter in another column
=MID(A1,FIND(" ",A1)+1,2)&"-"&LEFT(A1,FIND(" ",A1)-1)&"-"&RIGHT(A1,4)
Copy down as required.
Once complete, copy the whole range of data and Paste Special>Values to
convert from formulae to actual dates.

Regards

Roger Govier
 
A

Anne Troy

Diane: It sounds like maybe those entries are being seen as text? Can you
check for us? Copy any BLANK cell. Select those weird cells. Hit
Edit-->Paste special, Add. Then reformat however you want again. Does that
work?
************
Anne Troy
www.OfficeArticles.com

DianeMcP said:
Update: I tried the custom and "dd-mmm-yy" - and it still does not change
an
entry from Sept 20, 2005 to 20-Sept-05. Any other suggestions, anyone?
Diane

Hi Anne,

When I went back into the spreadsheet, I just chose one of the date
formats
that were already there, and it showed 04-Mar-05....it was probably me
that
assumed that the formatting in the code was dd-mon-yy.

I've gone back into it and used "custom" and put in dd-mmm-yy.
Click to expand...
 
D

DianeMcP

Hi Roger,
In your sample, is the A1 supposed to be the date column? or should I
substitute the A1 for the actual column being used for dates?
 
D

DianeMcP

Hi Anne,
Nope, that didn't work. I've tried the setting for date and picking an
existing sample of 14-Mar-01 and also custom and typing in dd-mmm-yy. It
still keeps the incorrect date format.
Any other ideas?
 
R

Roger Govier

Hi Diane

I assumed the dates were in column A and started at A1.
Substitute your actual cell references into he formula e.g. if your dates
are in column F starting at row 3, then change the formula to
=MID(F3,FIND(" ",F3)+1,2)&"-"&LEFT(F3,FIND(" ",F3)-1)&"-"&RIGHT(F3,4)

hope this is clear, but if not post back.

Regards

Roger Govier
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Format Date in Header 2
formatting of date 4
Week starts on Friday 3
Finding dates without attention to data formats 3
Forcing a date entry format of dd/mm/yyyy 1
Weird Excel date formatting problem 2
How do I change dd/mm/yy to dd-month-yy? 6
date format convert 3

Top