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 using text to columns but I am quite sure that I am entering something wrong. =DATEVALUE does not seem to work as the format it uses is year, day, month. I want day, month, year
6 Replies
In addition, dates in Excel are actually sequential integer numbers starting from 1, which is represented as Jan 01, 1900, to the end of 9999 year. For example, Aug 20, 2020 if you enter this date and apply General format to it will be shown as 44063.
Thus formatting won't help, you shall use formula as JMB17 suggested or like to convert your texts into the dates.
Although Mr. JMB17b and Mr. Mosomiv were very detailed in their assistance, I am sending you this link from microsoft, maybe it will help you to make this more understandable.
DATE function
Thank you,
Nikolino
I know that I don't know (Socrates)i just reread my question. the format i want is month, day, year. OOOOPS
It appears my previous response was eaten.
I suspect your issue with text to columns is that the date is not actually text, but numeric (custom formatted to show a leading zero).
Try this (change cell reference as needed):
=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))
If you right-click on the cell and select "Format Cells" from the drop down menu, then, in the new window that appears, select Date from the Category, you will be able to format the date any way you want. Highlight all the cells/columns that you are entering the date in and format them, then it will change your entries to your chosen date format.
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.
