Jan 29 2020 02:27 PM
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.
Jan 29 2020 05:05 PM
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.
Jan 29 2020 06:07 PM
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!
Jan 29 2020 06:10 PM
Apr 22 2021 10:15 AM
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)
Apr 22 2021 11:26 AM
Apr 23 2021 04:20 AM