Forum Discussion
Date formating
joeexception Since the date and time stamps are not recognised by your Excel, try to use Text-To-Columns from the Data ribbon. Save your file first, incase the following steps don't work for you.
Select the column with the "date/time". Choose "Delimited" in the first step. Next. Select space as the delimiter. Next. Now, in step 3 the preview window will display two columns. One with the dates and one with the times. With the first one selected, set the data type to Date and select MDY from the dropdown, since the date you are importing is in the Month-Day-Year. order. Now select the Time column and set it to Skip (Do not import).
Press Finish.
I think I'm really looking for a formula that can do this, rather than a one-off process. I had seen the text-to-columns trick mentioned elsewhere, but this requires new data to be 'processed' every time you import it.
I'm ideally wanting users to be able to simply paste a row of data into one sheet and then have a formula pull this data out of the cell and 'reformat' it for them in another table... hopefully that makes sense?
Thanks in advance! 😉
joeexception See attached for a working example of a formula based solution. Not very proud if it, though. When you break it into pieces, all it does is find the date part and "calculate" the year, month and day portions and throws them into the DATE function to create a real date that you can format as you like. There must be better ways to do this, though.