• 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.

Date Help

Doughnut01

New Member
Hi,

I have a large amount of data being received weekly. i need to pull the dates from it, which arrives in a column of information with some text.

It all has a standard layout of "YY.MM.DD Text"

I've been able to pull the dates from this, but the system keeps reading the info as "DD.MM.YY"

For example something from today which should be "19(YY).12(MM).03(DD)" is only ever being read as "19(DD).12(MM).03(YY)"

Any guidance on this would be greatly appreciated.
 

NARAYANK991

Excel Ninja
It all has a standard layout of "YY.MM.DD Text"

I've been able to pull the dates from this, but the system keeps reading the info as "DD.MM.YY"

For example something from today which should be "19(YY).12(MM).03(DD)" is only ever being read as "19(DD).12(MM).03(YY)"
Hi ,

Can you mention what is your Windows date format ? This is the default date format that Excel will use.

Any date formatting that you do within Excel is only if the original data was in your Windows date format so that Excel could recognize the data as valid dates.

If your Windows date format is mm-dd-yyyy , then you will have to convert your text data into this format first , and then within Excel apply date formatting to display these dates in the dd.mm.yy format.

Narayan
 

Doughnut01

New Member
Hi,

The date is dd-mm-yyyy as standard.

That's the area i'm struggling with at the moment.

I can use text to columns, but with thousands of lines of data being received weekly i'd rather avoid that.

Is the best method adding 20 to the start of the years, and using'=Left(A1,10) to extract the date and work from there?

Or is there an easier method?

Many Thanks.
 

NARAYANK991

Excel Ninja
Hi ,

This formula does the job. I do not know any easier way.

=("20" &LEFT(SUBSTITUTE(A1,".","-"),8)) + 0

Adding the 0 converts the text string to a numeric value , which is then interpreted by Excel as a valid date.

Once this happens , you can format the output cells in dd.mm.yyyy format.

Narayan
 
Top