Date Format Query

Microsoft Excel 2003 (full product)
March 9, 2010 at 09:13:30
Specs: Windows XP
Hi - Hoping someone can help me with this!

I'm using MS Excel 2003.

I have about 2000 dates in the following format; but I need the dates to display in this format; dd/mm/yy.

I've tried selecting the column and then going to Format -> Cells -> Number -> Date -> dd/mm/yy but it doesn't change the exisiting format.

I've also tried Format -> Cells -> Number -> Custom --> dd/mm/yy to no provail.

I've attempted creating a basic macro where I record changing the date manually, but when I run it the actual dates change to the date I changed manually whilst recording the macro.

Also Data -> Text to Columns --> Delimited --> Next --> Next doesn't offer a solution to my problem either :(

Does anybody out there know how I can get arounf this?

See More: Date Format Query

March 9, 2010 at 10:27:45

The reason for your problem is that Excel did not recognize as a date, and it has left it as text.

Because it is text, Excel can't display the date in different formats.

The solution is to create a date that Excel recognizes, out of the text.

If you have a date ( in cell A11 for example, put this formula in the adjacent cell:


The formula can be dragged down alongside columns of dates as required.

Copy the new date cells and use PasteSpecial - Paste Values, to paste the new Excel dates back into the cells with the original dates. Format the cells as required, e.g., dd/mm/yy


Report •

March 9, 2010 at 10:30:39
Here's what I did:

With 21.02.10 in A1, I used this in B1:


I then Custom Formatted this as dd/mm/yy

Then I did a Copy...Paste Special Values to turn the formula into an actual date.

Report •

March 9, 2010 at 11:00:06
Then again you can always do a Search & Replace

Hightlight your column with the text dates
then Search for a Period
and Replace with a Slash


Report •

Related Solutions

March 9, 2010 at 11:06:13

Mike's suggestion looks good - and simple.

It worked when I tried it.


Report •

March 9, 2010 at 11:37:27
In your OP you said:

I need the dates to display in this format; dd/mm/yy.

Unless I'm missing something, Mike's suggestion does not create dates. It leaves the string as a text string that can not be used as a date in other formulae.

If that's OK with you, then run with it, but if you need actual dates then I'm not sure that it will work for you.

Report •

March 9, 2010 at 11:47:53
It leaves the string as a text string

No, it converts the text string to a Date.

Try it.


Report •

March 9, 2010 at 11:53:34
Hi DerbyDad03,

I started with text 10.02.10 in A11 which was not recognized as a date by Excel.

I then ran Find/Replace . > /

The resulting 'text' had actually been converted to an Excel date.
The cell accepted different date formats, and in another cell I entered =A11+1 which returned 11 Feb 2010.

It even worked when the cell was formatted as Text before entering 10.02.10


Report •

March 9, 2010 at 15:52:26
Based on the formats the OP mentioned - going to dd/mm/yy - I am assuming that 21.12.10 is a valid entry. 21.Dec.2010

Try the replace . with / on that 21.12.10 and let me know if it works for you.

I've tried it on 2 machines and the resulting string 21/12/10 does not act like a date.

Report •

March 9, 2010 at 16:29:07

It worked fine.

It converted to a date (21 Dec 2010) and behaved like a date:
+1 yielded 22 December.


Report •

March 9, 2010 at 17:12:48
I don't know what you are doing different, but it's not working for me.

Brand new spreadsheet, before the Edit:

A1 gets 21.12.10   B1 gets =MONTH(A1) Returns #VALUE!
A2 gets 12.21.10   B2 gets =MONTH(B2) Returns #VALUE!

- Edit - Replace...
- Find what: .
- Replace with: /
- Replace All

Spreadsheet after the Edit:

A1 becomes 21/12/10   B1 gets =MONTH(A1) Returns #VALUE!
A2 becomes 12/21/10   B2 gets =MONTH(B2) Returns 12

A1 remains Left justified (text) 
A2 changes to Right justified (date)

=A1+1 returns #VALUE!
=A2+1 returns 12/22/10

A1 will not accept any date formats
A2 accepts all date formats

Report •

March 9, 2010 at 18:10:57
DerbyDad, your correct.

Any Day greater than 12 will prevent the date conversion.

So a date of 12.01.10 will be converted
(to December 1, 2010 which is wrong)
while a date of 13.01.10 will not be converted at all.

Seems like the Search&Replace method is only valid for mm/dd/yy formats.


Report •

March 9, 2010 at 18:24:14
BUT, the Search&Replace method will work if you have your System Date set to the United Kingdom format of dd/mm/yy

But if your looking for US style format, then it produces an incorrect date, same as above.

So, if your in the US and you import English dates, Search & Replace won't work and conversely if your in England and you import US Dates, Search & Replace won't work.
Only if your import dates that are in the system default type does it work.


Report •

March 10, 2010 at 01:03:46
Thanks for all the responses;

I used the formula provided by DerbyDad03 and it has resolved my issue!


Thank you all very much!

Report •

March 10, 2010 at 03:55:02
Good detective work Mike!

Report •

March 10, 2010 at 04:42:18
HI, Mike, DerbyDad03,

The conversions all worked on my system, presumably because, as Mike points out, the system date I use is in day, month, year order, (dd/mmm/yy).

I was able to correctly convert all 365 dates in a year, just using the replace method.


Report •

Ask Question