Please Help This Armature. Question Regarding Date Formatting.

[MilsAlex]

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.

 

[mole999]

where does the data come from, is it the same data source

 

[MilsAlex]

where does the data come from, is it the same data source

Thanks for reply.


Yes it's the same source.
I do not know why the system/software spits out the data like this, that part is baffling.

Just while I am fixing that particular column I think to myself there has to be an easier way, but only someone a lot smarter than me will know it if there is.

 

[mole999]

is it from a database

 

[MilsAlex]

Hi,

Sorry I have not replied in a while but I have been away.

I don't know the system exactly but I assume it has some form of database in the backgroung.

The only fix I have found to this issue is the following formula (but it's huge):



=IF(A2 ="","No Date",IF(ISNUMBER(A2),<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">DATEVALUE(TEXT(A2,"mm/dd/yy"))<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">,IF(LEFT(A2,1)>="A",DATEVALUE(<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">CONCATENATE(LEFT(RIGHT(A2,7),<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">2)," ",LEFT(A2,3)," ",RIGHT(A2,4))),DATEVALUE(<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">CONCATENATE(MID(A2,4,2), "/",LEFT(A2,2),"/",MID(A2,9,2)<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">)))))