Hi All,
Apologies if this question is about I did search but could not find anything that could help me.
First off I am new to the forums and after taking a brief look, it seems to be a great place to get excel knowledge/tips.
Explanation/Question:
I have a fairly large data extract I do daily and form pivot tables from it. One column of dates however seems to be all messed up and inconsistent in it's format, this posses a problem for the pivots.
The dates come out in 2 different types of format and 4 different types of writing (I don't know why), I need to know how to turn all of these into a short date in date format.
They extract as follows:
Jan 14,2016 = general format
10/22/2015 07:24:53 AM = general format
11/04/2015 22:30:50 = custom format
="08/07/2015 10:39:42 AM" = general format
At the minute I am having to highlight the column and do find and replace to remove the =" and then run that again to remove the " and then run it again to remove a space that is at the beginning of some dates.
I then have to highlight all and format to short date, problem is sometimes the time stays in there as 00:00 and sometimes the top one comes out without the year.
If anyone knows an easier way it would be a great help and I would really appreciate any advice that could make it easier.
Thanks.
Apologies if this question is about I did search but could not find anything that could help me.
First off I am new to the forums and after taking a brief look, it seems to be a great place to get excel knowledge/tips.
Explanation/Question:
I have a fairly large data extract I do daily and form pivot tables from it. One column of dates however seems to be all messed up and inconsistent in it's format, this posses a problem for the pivots.
The dates come out in 2 different types of format and 4 different types of writing (I don't know why), I need to know how to turn all of these into a short date in date format.
They extract as follows:
Jan 14,2016 = general format
10/22/2015 07:24:53 AM = general format
11/04/2015 22:30:50 = custom format
="08/07/2015 10:39:42 AM" = general format
At the minute I am having to highlight the column and do find and replace to remove the =" and then run that again to remove the " and then run it again to remove a space that is at the beginning of some dates.
I then have to highlight all and format to short date, problem is sometimes the time stays in there as 00:00 and sometimes the top one comes out without the year.
If anyone knows an easier way it would be a great help and I would really appreciate any advice that could make it easier.
Thanks.