Excel/Access date format issues Nov01 '04
Feedback
# (2 of 5): Matthom
2 days, 6 hours after the fact. (Wed 03 Nov 2004, 6:15 PM CST)
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:
Wednesday, November 3, 2004
I would need to remove everything from AFTER the FIRST COMMA - backward. I don't want to have to go in and manually remove each one.
I looked for a TRIM function in Excel, but there's nothing obvious right now. It will come to me...
# (3 of 5): Matthom
2 days, 6 hours after the fact. (Wed 03 Nov 2004, 6:18 PM CST)
Oh wait... I just figured it out!
Text to Columns!
Under the Data menu, there is an option called "Text To Columns..."
I could then break up each cell, using the comma as a column delimeter.
Bingo!
# (4 of 5): Chad » greasyvalley.com
3 days, 2 hours after the fact. (Thu 04 Nov 2004, 2:31 PM CST)
DOH! I shoulda thought of that! Text to columns is sweet, ain't it??
# (5 of 5): Peter
2 months, 2 weeks after the fact. (Thu 20 Jan 2005, 3:56 PM CST)
A1 = "Monday, January 1, 2004"
Put this formula in anywhere:
=VALUE(RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1))
The output will be the date in numerical format. Reformat the number so that it shows as a date.
RSS feed for comments on this post
Leave feedback
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.
Excel and Access don’t recognize a full date, with the day of the week included. What gives?
You are at the feedback permalink page for: Excel/Access date format issues
# (1 of 5): Chad » greasyvalley.com
1 day, 23 hours after the fact. (Wed 03 Nov 2004, 10:46 AM CST)
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 day (what you don't want) and gives you the date (what you do want) or maybe just remove the date and put that into a new field? Do this using LEFT, RIGHT, or MID...is there a TRIM function in Excel?? You will have to use some logic since the days are all different character lengths, but nothing too bad....maybe worth a shot...