converting excel dates in Excel 2003

Microsoft Office excel 2003
June 17, 2010 at 10:15:31
Specs: Windows XP
For some reason as I have added dates to
cells, they are not getting converted to the
Date type I have selected: 15/03/2009

I type in my entries in this format:
March 15, 2009

(usually it converts immediately but not this time)

I've tried selecting the columns of cells with dates, Select>Format>Cells>Date and Type but nothing changed (although one date was converted to

Any suggestions?

See More: converting excel dates in Excel 2003

Report •

June 17, 2010 at 11:41:55
Dat's cuz they're probably not being recognized as dates by Excel.

My guess from afar is that the cells are formatted as Text and aren't accepting any other formats. Excel is finicky like that sometimes.

Try this with one of your stubborn "dates"...let's assume it's in A1


Now format that cell anyway you want.

Then do a Copy...PasteSpecial...Values to eliminate the formula.

Report •

June 18, 2010 at 05:26:39
Thanks, I gave that a try and I just got


Excel is not my strong suit so I'm probably missing something

Report •

June 18, 2010 at 06:45:14

Are you always entering today's date or do you have to enter different dates.

I ask this because there are two possible macros that could be used to put a date into the selected cell and format it as required.

A very short one adds today's date.
The other, longer one, uses Microsoft Office's calendar, to select the date and then it's entered in your format.

If you are interested, let me know.


Report •

Related Solutions

June 18, 2010 at 07:20:39
Since I can't see your spreadsheet from where I'm sitting, it's kind of hard to diagnose your problem.

DATEVALUE will return a #VALUE error if it can't convert the text into a date.

For example, applying DATEVALUE to this should work:

March 1, 2010

But this would return #VALUE because Excel wouldn't recognize it as a date since it's missing the comma:

March 1 2010

In general the text has to appear in the cell looking like one of Excel's date formats in order for DATEVALUE to work.

If you type in a date and it doesn't convert, check the formatting and tell us what it says.

Report •

June 18, 2010 at 07:44:23
You could also try this:


This will return a code that shows the formatting of the cell.
Any code with a leading letter D is a date code, so if it returns a D1 then the cell is formatted as "d-mmm-yy or dd-mmm-yy"

What code does it return?


Report •

June 18, 2010 at 11:51:56
Thanks everyone. As it turns out, my regional settings were
not set correctly. I set them to English (Canada) and
checked the format before going back to the spreadsheet. I
was able to type in new dates in this format: January 23, 2009
and have it automatically convert to 23-01-2009, which is what
I wanted. However, it still would not convert my existing data.
By chance, I removed the space after the month and before
the date (January_11), and the formatting changed to my
desired output. Saved me a tonne of time just doing a find
and replace on the month names.

I appreciate your feedback and next time will be sure to
include examples of my problems.

Report •

Ask Question