full date string to date

users
#1

I had a spreadsheet with dates that I think somehow got converted to text.
I tried pasting it as plain text with detecting special numbers and I tried setting the column to a date format but it still seems to come out as text.

'Wed, Jan 2, 2020'

#2

James,

The only way I could get the text sample you provided to be accepted as a
date was to leave the day name off. Typing Wed, Jan 2, 2020 into a cell
resulted in a cell that contained text. Typing Jan 2, 2020 into a cell
resulted in a cell that contained a date in the default date format,
mm/dd/yy. That could then be formatted to NN, MMM D, YYYY and display as
Wed, Jan 2, 2020.

The DATEVALUE() function will convert the string Jan 2, 2020 to a date. It
returns Err:502 when pointed at the string Wed, Jan 2, 2020.

My suggestion would be to input the substring Jan 2, 2020 into the
DATEVALUE function then format as desired to get your dates back.

e.g. =DATEVALUE(MID(A1,6,20))

#3

Dates are sensitive to language and locale, so no guarantees, but ...; you should be able to convert your data using the spreadsheet program's own facilities. Try this:

o Suppose your date data is in column A. Select the range (or column) and go to Data | Text to Columns... . Under Separator options, tick Comma, Space, and Merge delimiters. OK. You now have the four parts of your dates separately in columns A, B, C, and D.

o In the first row of your data in a new column, enter (for, say, row 1)
=DATEVALUE(C1&B1&D1)
- and fill down the column. Note the jumbled order of the parameters, so what is offered to the DATEVALUE() function is three parts of your date concatenated as "2Jan2020".

o Format the values in the new column as desired, perhaps as
NN, MMM D, YYYY

You can copy the resulting values back over the originals if you wish - or elsewhere, of course - using Paste Special with Formulae unticked.

Oh, and by the way, unless something very strange is about to happen, I'm expecting 2 January 2020 to be a Thursday, not a Wednesday!

I trust this helps.

Brian Barker

#4

James,

The only way I could get the text sample you provided to be accepted as a date was to leave the day name off. Typing Wed, Jan 2, 2020 into a cell resulted in a cell that contained text. Typing Jan 2, 2020 into a cell resulted in a cell that contained a date in the default date format, mm/dd/yy. That could then be formatted to NN, MMM D, YYYY and display as Wed, Jan 2, 2020.

The DATEVALUE() function will convert the string Jan 2, 2020 to a date. It returns Err:502 when pointed at the string Wed, Jan 2, 2020.

My suggestion would be to input the substring Jan 2, 2020 into the DATEVALUE function then format as desired to get your dates back.

e.g. =DATEVALUE(MID(A1,6,20))

That works, thanks.

I have a spreadsheet with 2018 dates and I did a replace of '2018' for '2019' to make a copy of the spreadsheet for 2019.
When I did the replace for 2020 dates it changed the cells to text.
I maybe had an option checked that I didn't before. :frowning: