How do I convert an excel text to date?

1
Anonymous
Sep 22, 2020 08:13 AM 2 Answers General
Member Since Jan 1970
Unsolved Solved Mark as Solved Mark as Unsolved
Subscribed Subscribe Not subscribe
Flag(0)

I have a cell in general format with dates looking like this: Tue, Oct 26, 2010 and want excel to recognize it as a date. How do I do that? Thanks!

Update:
I need to frequently update the original data and would therefore like a formula that translates the text into a date rather than simply changing format... Thanks again

Update 2:
Thanks for the inputs, C Masters. This is close to what I have tried and I know it's pretty much what I'm looking for. However, I always get a #Value error.

I assume this is because of the three letter month name (e.g. Oct ) instead of number.

Does somebody know how to fix this?

1 Subscribers
Submit Answer
2 Answers
Best Answer
0
CMasters
Sep 22, 2020
Flag(0)

Right click on the cell, go to “format cell”. Go to the “number” tab, and select the format of which you want to use.

Sign in to Reply
Replying as Submit
Best Answer
0
Anonymous
Edit Sep 22, 2020
Flag(0)

**EDIT** I test ran the samples below with the “Tue, Oct 26, 2010” you supplied above and got a good result. I did get #VALUE! once, but that was because I had nothing in cell A2 which is where the formula was looking for the original text string…

try diagnosing by seeing what gets returned from =(MID(A2,FIND(“,”,A2)+1,100))

maybe there is a double space or something that is throwing off the rest of the formula

compare the values from =LEN(A2) and =LEN(TRIM(A2)) ??

*********

if the text string was in A2

=DATEVALUE(MID(A2,5,100))

this assumes your data entry is constant with a 3 letter weekday abbreviation+comma+space before the month, and returns the date as the number that Excel uses to track dates off of 01/01/1900

=DATEVALUE(MID(A2, FIND(“,”,A2)+1,100))

is little more flexible because it looks for the first comma in the cell and takes the remainder of the cell contents to the right

you’ll still need to set the cell formatting to one of the Date options if you want it to display as something like 10/26/2010 instead of 40477

if you want it to display directly into a standard date format you could do something like

=TEXT(DATEVALUE(MID(A2, FIND(“,”,A2)+1,100)),”mm/dd/yyyy”)

hope that helps

Sign in to Reply
Replying as Submit