Converting Getting Mixed Date Format

[Firasath]

Hi,

In the below link file contains column "LastStaffUpdateTime" which has the extracted Date and it is in Text format and needs to be converted in correct date format. It has been extracted from "Update Action" column. The Date should be from Oct 2012 till yesterday i.e. 18/01/2014, but when I'm converting it through Text To Column, I am getting wrong date range which is showing date till end of 2014.

http://www.4shared.com/file/AFB68TwEba/Sample.html

Kindly suggest.

 

[oldchippy]

Hi Firasath,

Welcome to the forum, I can't seem to download your file from 4shared, please can you attach it in your post?

See how to here

http://chandoo.org/forum/threads/posting-a-sample-workbook.451/#post-73705

Thanks

 

[NARAYANK991]

Hi Naqvi ,

Please avoid a site such as 4Shared , which requires you to register in order to download a shared file.

2Shared is a similar file sharing website , but it does not require you to register in order to download a shared file.

I have already downloaded the file.

@oldchippy : the file size is 2.5 MB , and hence it cannot be uploaded to this forum.

Narayan

 

[NARAYANK991]

Hi Naqvi ,

The problem is that the date+time combination in column B has the data in the wrong format every now and then ; for the most part , the date is in mm/dd/yy format , but once in a while , it is in dd/mm/yy format.

Can you go through the data ectraction process once more , and specify the format for the column as either dd/mm/yy or mm/dd/yy so that all of the data is in the same format ?

Otherwise , please upload the text file , so that we can open it in Excel and do the Text to Columns or date separation ourselves.

Narayan

 

[Hui]

The first 1000 records is attached below
If Narrayan's comments don't help simply use a Formula to extract teh dates into a new Column
in C2:
=DATE(MID(A2,7,2),LEFT(A2,2),MID(A2,4,2))
or
=DATE(MID(B2,7,2),LEFT(B2,2),MID(B2,4,2))

Copy down

 

[SirJB7]

Please avoid a site such as 4Shared , which requires you to register in order to download a shared file.

Hi, NARAYANK991!
I wasn't asked for registration, just had a 20 seconds of waiting/delay time before download link appeared.
Regards!

 

[NARAYANK991]

Hi Pablo ,

When I click on the link , it displays a popup which requires you to :

1. Sign in using an account with Facebook , Twitter , Google+ , Instagram

or

2. Sign in using your account with 4Shared.

2Shared does not require this.

Narayan

 

[SirJB7]

Hi, NARAYANK991!
Strange... give a look at the uploaded files, those are the 2 screens I get displayed after clicking on the posted link. In the 1st one at top right you'll notice that I'm not logged in, but in the 2nd one which I assume that is the one which you're not getting unless you register, the button for the normal download never starts the countdown from 20.
Regards!

 

[NARAYANK991]

Hi Pablo ,

The issue is very simple ; let us not complicate it with uploads ; can you download the file by clicking on the download link , and have you done so ? I could not do it , period. I could do it using 2shared.

Narayan