Forum Discussion
Solved
Text to date
I have a number of Excel sheet with dates like this
August 1, 2021
July 25, 1995
How would I convert them in to a date value
e.g.
01/08/2021
25/07/1995
Thanks
Option 1:
- Select the dates.
- On the Data tab of the ribbon, in the Get & Transform Data group, click From Table/Range.
- Specify whether your data have a header row or not.
- Click Close & Load.
Option 2:
- Let's say your data begin in A2.
- In B2, enter the formula
=DATEVALUE(MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1)&" "&LEFT(A2,FIND(" ",A2)-1)&"-"&RIGHT(A2,4))- Format B2 as a date.
- Fill down.
4 Replies
Option 1:
- Select the dates.
- On the Data tab of the ribbon, in the Get & Transform Data group, click From Table/Range.
- Specify whether your data have a header row or not.
- Click Close & Load.
Option 2:
- Let's say your data begin in A2.
- In B2, enter the formula
=DATEVALUE(MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1)&" "&LEFT(A2,FIND(" ",A2)-1)&"-"&RIGHT(A2,4))- Format B2 as a date.
- Fill down.
- Perfect.
Thank you
Right mousekey Format cells Custom
Custom date format:
MM/DD/YYYY
Format a date the way you want
Format numbers as dates or times
Convert dates stored as text to dates
Hope this information could help you.
Wish you a nice day.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
- I have, of course, looked at all those before.
But.... non of them explain how to convert the date if the 1st 2 segments (Month and day) are nor comma separated.
