Change date text formatted as dd/mm/yyyy hh:mm:ss AM/PM into date serial no.

[dblunden]

I have raw cvs file dump with text strings I need to return in a new column with the date/excel "serial number":

the cell contains for example: "23/07/2019 12:12:11 PM"

I need to return a serial date so that I can overlay data with discreet times into a pivot table.

[#2]

@dblunden 

That's a tricky one, because the dates are displayed in a European manner.

If it were "7/23/2019 12:12:11 PM"

then this formula works easily

=DATEVALUE(LEFT(A1,FIND(" ",A1)))

But with "23/07/2019 12:12:11 PM" it returns an error message.

It certainly is possible to parse the date and convert it to separate numbers for month, day and year....but I'm hoping there's somebody else here who can do something more directly.

[#3]

@dblunden 

OK, once the bug bites it's hard to let it go.

Here's a parsing formula that returns the datevalue, assuming your string is in cell A1

=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&MID(A1,7,4))

will return the date value.

Enjoy!

[#4]

Wa…..Fantastic. You have no idea how helpful that was.

[#5]

Hi,

Having real issues in converting a date in excel. It was so easy with google sheets.

Can anyone lend me a hand? (check attachment)

[#6]

Your formula doesn't even refer to the column in which the date appears. That could be the cause of it not working.
If you still have difficulties, though, please attach a copy of the spreadsheet, or a portion of it. Images aren't all that helpful when you're seeking help.

[#7]

Thanks for your reply. I managed to solve it differently