Convert written date

[JamesBowdidge]

Hi,

I have an extract that spits out a date in this format...

Nov 6, 2018 9:59:35 AM

And I need it to be recognied as a "proper" date in excel..

Can anyone help?

thanks

J

 

[Joe4]

Welcome to the Board!

That actually is/can be a proper date in Excel.
The question is, is that entry entered as Text or Date?
An easy way to tell is to try changing the format of a cell to a different date format. If the appearance changes, it is entered as a date, and all you have to do is choose the date format that you want.
Otherwise, you can convert it to a date using the DATEVALUE function, i.e. in the entry is in cell A1:

=DATEVALUE(A1)

or if you do not want to use an additional column, use Text to Columns (under the Data menu) to convert it to a valid date entry.
Then, just choose the cell date format you desire.

 

[JamesBowdidge]

Hi Joe,

thanks for replyng..

Changing the formattin makes no differnce the cell contents.. an the datevaue function gives a #value error. so Iam assuming that even the syntax of the text is not recognised by excel. So I need another way of extracting the value from the text.

any ideas?

thanks

J

 

[Scott Huish]

That should work because that format is acceptable by Excel as you had given it. Is there perhaps no space before your AM/PM or something?

 

[mikeross]

Sometimes if Excel doesn't recognize my data I do "text to columns" (alt A E), click delimited and then uncheck every box and hit finish. Not sure why it works but it does!

 

[JamesBowdidge]

Thanks Mike, but has no effect im afraid..

I havn seen a date field extract quite like this before and nothing I do seems to make it recogise this.. I need an easy solution (preferably formular) as this is from an auto payment report that comes every day.. Im tottally stumped!!

thanks

 

[JamesBowdidge]

Hi Scott,

there is a space before the "am" or "pm" and the originaly quoted format of the date is exactly as shown in the original post? I just dont get it!! LOL

 

[MARK858]

James, what country are you in?

 

[JamesBowdidge]

uk

 

[MARK858]

If you put

6 Nov, 2018 9:59:35 AM

in a cell does it convert to a date?