• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

changing date types [SOLVED]

P

Pofski

Member
Quick question,


i am doing an extraction of date's out of a txt file.

now, after the extraction is done, i have a list of dates, but i notice that some of them are in european format, and some are in american format.


So that makes it that it shows some dates as completed on 4th december 2013, while they are actually 12th april 2013.


I was thinking about putting the extraction date in a cell, and let excel test to see if the date is later then the extraction date, and if this would be the case to change it from 4/12/2013 to 12/4/2013.


Does anybody have a suggestion about how i could get this done without resorting to macro's?


Thank you in advance
 
https://docs.google.com/file/d/0B0naOkYo4pCmMlVKdEhidVdCTnc/edit?usp=sharing


The column date contains the date as it is extracted. The column corr date is the date how it should become (automatically).
 
Please try this: in L2


=IF(ISERROR(VALUE(G2))=TRUE,DATE(RIGHT(G2,4),MID(G2,FIND("-",G2)+1,FIND("-",G2,FIND("-",G2)+1)-FIND("-",G2)-1),LEFT(G2,2)),TEXT(G2,"DD-MM-YY"))


Regards,
 
Hi, Pofski!

If you have mixed date formats dd/mm/yyyy and mm/dd/yyyy there is absolutely no way to extract the actual data, neither with formulas nor UDF nor VBA code. You could only detect and extract data for wrong date values (i.e., dd/mm formatted with values in mm/dd and dd>12, and the reciprocal case) but you wouldn't be sure of all extracted data for apparently correct date values (i.e., dd/mm formatted with values either in dd/mm or in mm/dd and dd<=12) since you won't be able to detect their correctness or not, just that they appear as valid dates.

So Faseeh's formula would work for the detected wrong date values but you'd still be missing the certainty about those that Excel accept as valid dates.

Regards!
 
Hi Pofski ,


In addition to the format ( mm dd yyyy or dd mm yyyy ) , can you clarify what the delimiter is for the two formats ?


Is it that the American format will use the "/" character , while the European format will use the "-" ?


If this is so , then when you are bringing the data into Excel , specify the column as Text , so that the delimiters are preserved , and can be used. Excel will retain all the dates , even the valid ones , in their original format.


Narayan
 
Hi everybody,


after going over the date manually, we have come to the conclusion that the remark that SirJB7 made, is in fact correct.


There is no certainty that those date's that are automatically registered as "correct" dates, are in fact correct.


Although the end of year number would be correct, this would be useless, seeing as we are doing a week to week verification of an enormous amount of data.


It's back to the drawing bord for us on the part of data extraction.


Thank you all for your help


sincerely
 
Quick question: is the source data in European format, while your Excel uses US format? (Or vice versa). If so, what if you temporarily change the format on your PC to European, and then import the dates, then change it back to US.


Just thinking aloud...not sure if this will solve your issue or not.
 
@Pofski

Hi!

I learned this in the hard way as I work with Spanish Excel versions with date format in dd/mm/yyyy and I have to take care when importing from US sources, if not... should do a rollback and restart.

Regards!
 
You must log in or register to reply here.
Back
Top