Forum Discussion
Date formating
I'm copying and pasting data from an MS Form export into a new spreadsheet. The dates are captured as mm/dd/yy hh:mm:ss (sometimes shortened to m/d/yy h:mm:ss - for example, "2/14/22 13:40:12")
When I copy and paste the row these date cells come across as 'General' formatted cells.
I simply want a formula to automatically convert these to regular, but EU-formatted, dates on another sheet. I don't really need the time, so just want dd/mm/yy.
I've tried using the DATEVALUE formula but it fails (my system is set up for EU date formatting and it seems that might be a factor?).
I would assume this was a fairly simple request, but it's proven rather frustrating! Hopefully someone here can work some magic!
3 Replies
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.
- Thanks Riny,
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.
