Forum Discussion
extreme noob converting text to date
i am trying to setup a simple spreadsheet. When I enter the date, i prefer to type it in as follows using todays date 08182020. i would like to change that to show as 08/18/2020. I have tried usi...
I believe DATE is the function that takes year, month, day. DATEVALUE will convert text to a date value, however, 08182020 won't be properly converted.
You can use the LEFT/RIGHT/MID functions to separate the respective parts (I'm assuming the dates are numeric, but formatted to show a leading zero and that the days are all two digits (so days less than 10 will have a leading zero)).
=DATE(RIGHT(H2,4),LEFT(H2,LEN(H2)-6),MID(H2,LEN(H2)-5,2))
I suspect the problem with text to columns may be that your data is not actually text, but numeric (with a custom number format to display a leading zero)? Perhaps format a cell as text and enter 08182020, then try text to columns and see if that works for you.