Please Help This Armature. Question Regarding Date Formatting.

MilsAlex

New Member
Joined
Jan 17, 2016
Messages
3
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
where does the data come from, is it the same data source
 
Upvote 0
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.
 
Upvote 0
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;">)))))
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top