I would like to be able to key in 02142019 and cell read 2019-02-14. I have been able to enter a custom format that will enter the dashes but cant figure out how to change the order of the entry.
I would like to be able to key in 02142019 and cell read 2019-02-14. I have been able to enter a custom format that will enter the dashes but cant figure out how to change the order of the entry.
What are you wanting to do with these dates? If you want Excel to recognize them as dates -- so you can use number formatting to format the dates in different ways and use Excel's date functions to manipulate the dates, then the easiest is to probably use text manipulation functions to extract the parts of the date and then convert to date serial number.
Read something like this to understand how Excel stores dates (and time) as serial numbers: http://www.cpearson.com/Excel/datetime.htm
I would probably:
1) preformat the data entry cell as text so that Excel will retain the leading 0 and store my date string as a text string and not a number.
2) Enter my dates 02142019 like you propose.
3) Have an adjacent helper column that will convert the text to an Excel date:
3a) Use a RIGHT() function to get the year RIGHT(A1,4)
3b) Use a LEFT() function to get the month LEFT(A1,4)
3c) Use a MID() function to get the day MID(A1,3,2)
3d) Finally use a DATE() function to combine those parts of the date into a single date serial number. =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))
4) Format the date in the desired number/date format -- "yyyy-mm-dd" custom number format help file: https://support.office.com/en-us/art...7-9c9354dd99f5
5) optional -- if you dislike having both cells, then copy the formula cell -> paste special -> as values over the data entry cell. You can then delete the formula cell. I would only do this if this is a one time or rare thing -- since the copy paste -- delete operation will delete the formula. If this is going to be a frequent thing, then I would keep the formula cell.
Help files for all functions can be found here, in their appropriate category: https://support.office.com/en-us/art...1-63f26a86c0eb
Is that an adequate solution?
Originally Posted by shg
That worked perfectly!! Thank you so much!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks