Excel/Access date format issues Nov01 '04

Tell me...

How can Excel and Access not recognize this:

Monday, November 1, 2004

... as being the same thing as this:

November 1, 2004

???

For example, in Excel – if you enter today’s full date into a cell, and try to format that cell to display the date in a different format, it doesn’t understand.

BUT... as soon as you take out the day of the week (Monday), the cell can then be formatted to display the date however you like.

My problem is... I have an old database of blog entries, with a terribly inefficient "date" column, that stores the full date, with the day of the week included – as in the first example above.

I’ve exported my SQL database, as an Excel file, and now I want to format the generic "date" column to display the date like this:

11/01/2004

The option to display the date like that is obviously available in Excel, except Excel doesn’t realize that the date I entered, is INDEED A DATE.

It treats it as plain text, and you can’t change that unless you manually remove each day of the week, from the date, so that it appears just like this:

November 1, 2004

THEN it understands you.

Aggravating.

Categories: Software

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info

How bout this Matt: Do you need the day in there? You say if you remove the day, then it works?? Could you write a function that removes either the ... Read more.

Yes, I would have to somehow use a function that removes the day - or, in this case, the first entire word after the first comma: Wedn ... Read more.

Oh wait... I just figured it out! Text to Columns! Under the Data menu, there is an option called "Text To Columns..." ... Read more.

DOH! I shoulda thought of that! Text to columns is sweet, ain't it?? ... Read more.

A1 = "Monday, January 1, 2004" Put this formula in anywhere: =VALUE(RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1)) The output will be the ... Read more.

matthom is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.

Contact Matt

Similar Entries

Stats

730 unique visits since August 2008

Syndicate

Advertisements