Data set needs date conversion

[wowzers]

Hi, I have two stock CSV files that need the date coverted to match a third date format for the purposes of being able to feed them into a software. I am looking for a way to automatically change the date formats of the CSV files I have to the specified date format required.

The first column is the only one that needs changing as it is the one with the date

The desired date format is YYYY.MM.DD
(Year.Month.Day)
eg) 2008.03.18
2008.03.19
2008.03.20
and so on

--------------------------------------------------------------------


One one of the spreadsheets that need changing my current date format is
01/02/1897
01/04/1897
01/05/1897
MM/DD/YYYY
(Month/Day/Year)

ex) this would need to be changed to 1897.01.02 etc
--------------------------------------------------------------------

The other spreedsheet's current date format is
2/7/1885
2/8/1885
2/9/1885
MM/DD/YYYY
(Moth/Day/Year)

ex)this would need to be changed to 1885.02.07

*note* In this data, when months or days are single digits there is no zero currently placed before them. This would need to be corrected as I have done with the above example.

[:) Sixthsense :)]

Change the control panel Date setting to your desired format and try...

[akhileshgs]

Will this work?

=TEXT(A1,"YYYY-MM-DD")  

[wowzers]

Change the control panel Date setting to your desired format and try...

Hi, this is a good idea but my rquired date is not in the list of excels formats to pick from

[wowzers]

Will this work?

=TEXT(A1,"YYYY-MM-DD")  

Hi, I get an error message when attempting this

[:) Sixthsense :)]

In short date format of your control panel Regional Date and Time Setting just enter the format you would like (Ex. DD-MM-YY or MMM-DDD-YYY)

[wowzers]

In short date format of your control panel Regional Date and Time Setting just enter the format you would like (Ex. DD-MM-YY or MMM-DDD-YYY)

how do i do that?

[wowzers]

In short date format of your control panel Regional Date and Time Setting just enter the format you would like (Ex. DD-MM-YY or MMM-DDD-YYY)

I figured out how to do this but it does not work.

[wowzers]

I have attached copies of the files here so that it may be easier to solve this dilemna.
Please note the actual files are way to big to upload so I have attached a sample.

[wowzers]

Bumping this so that someone can help me

[:) Sixthsense :)]

In A1 Cell

01/02/1897

Please Login or Register  to view this content.

Copy and paste this formula in H1 Cell and drag it down...

Use paste special to convert the formula to values.

[wowzers]

In A1 Cell

01/02/1897

Please Login or Register  to view this content.

Copy and paste this formula in H1 Cell and drag it down...

Use paste special to convert the formula to values.

Thank you sixthsense.
This formula works on only one of my spreadsheets and its not giving me the date I am looking for. (its givig me YYYY.DD.MM when I am actually looking for YYYY.MM.DD)

Nontheless its a step in the right direction

[:) Sixthsense :)]

its givig me YYYY.DD.MM when I am actually looking for YYYY.MM.DD)

Just interchange it...

Please Login or Register  to view this content.

[wowzers]

Just interchange it...

Please Login or Register  to view this content.

Thankyou kinldly, that worked beautifully

I must ask you, is there a similar formula that can be made for the second spreadsheet?

The other spreedsheet's current date format is
2/7/1885
2/8/1885
2/9/1885
MM/DD/YYYY
(Month/Day/Year)

ex)this would need to be changed to 1885.02.07

*note* In this data, when months or days are single digits there is no zero currently placed before them. This would need to be corrected as I have done with the above example.

[:) Sixthsense :)]

In A1 Cell
2/7/1885

In H1 Cell

Please Login or Register  to view this content.

[wowzers]

In A1 Cell
2/7/1885

In H1 Cell

Please Login or Register  to view this content.

Thankyou sixthsense
This does exactly what I need except there is one problem: the year needs to be in YYYY format (four digits)

[:) Sixthsense :)]

Can you please show an example in which the suggested formula fails for better understanding

[wowzers]

Can you please show an example in which the suggested formula fails for better understanding

Sixthsense,

when using the formula my date is converted from

2/17/1885
to
85 .02.17

The converted date returned from that formula is 85 with two spaces after it then .02.17

the correct date I require would need to have the full four dijits of the year

correct date is 1885.02.17

Thank you kindly

[wowzers]

Please Login or Register  to view this content.

Can anyone please help correct this so the years returned show up as four digits?

[wowzers]

Bump post so this can be completed hopefully

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[wowzers]

Try this...

Please Login or Register  to view this content.

Thank you sixthsense. This worked!

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback