Changing Dates

[celinedion]

Hi

I have some data that has dates the wrong way round

Date
10/16/2016

This is how I actually want it
16/10/2016

I have tried changing it through Format Cells but nothing happens

Was wondering if anyone has an idea...as it would take forever to do manually

Thanks in advance

Dave

[Roel Jongman]

Because there is no month 16 excel changes the values to text. changing the cellformt will not change that.

to fix you can use the text to column option under the DATA menu
1 .Select the column with the dates.
2. choose Text to Column unde the data menu.
a. pick fixed width and make sure there are no split columns made
b. Click next button two times
c. in the step 3 screen select the columnformat data and pick date format mdy (that is the current format)
d. press finish button.

All dates should now convert to your local date format.

[Sam Capricci]

maybe try this...
=DAY(A2)&"/"&MONTH(A2)&"/"&YEAR(A2)
assumes it is in A2

[celinedion]

Sorry neither of them seem to have worked...I am using Excel 2010

Many of the dates are in the correct format but for some reason some are in the M/D/Y rather than D/M/Y

There are 292,000 files and 161,000 of them are in the wrong date format

[Roel Jongman]

both solutions are Excel 2010 proof.. this is nothing new.. date issues are as old as Excel itself..

I can almost garantee that the other 131000 are wrong too but because the day was less then 12 it was interpreted as month.. so f.e. 05-04-2016 will be posted as 5 april while it was intended to be 4 may.

In the best case you manage this problem at the moment of import of the data. How did you receive this data?

[sandy666]

Attach a sample Excel workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[celinedion]

Yes the data was mixed when I got it.. I downloaded it from a site I am a member of

[celinedion]

As you will see from this sample first set of dates are what I want all the data to be the highlighted yellow date are the incorrect ones

[sandy666]

• copy whole column of source to another sheet (eg. column A)
• use: B2: =if(istext(a2),a2,"")
• copy whole column B and paste in place as values (B to B - text2columns doesn't work with formula)
• column B text to columns with MDY
• in C2: if(isnumber(a2),a2,b2)
• copy column C and paste to the original sheet as values
• change format to short date
• delete temporary sheet

[Roel Jongman]

I build a formula. Like I said before I think your dates are also wrong, ofcourse not 1-1-2016 but check the source.. Dates will have been downloaded consistently in the m/d/yyyy format but if excel is set to d/m/yyyy any date before day 13 will be recognized as normal date however the day is put in month and month in day so januari 12 will become 1 december because of automatic interpretation by excel.

So I build 2 formula's 1 keeping the dates as dates as they are (formula 1) which I suspect is wrong
and I build a formula that will switch back the day and month for cells already seen as dates.
Verify the dates with days under 13 in your source to see if they were downloaded correctly or were positions were flipped by excel.

if you want the adjusted dates in column A just copy the help column and paste it over column A as values
and format A as date.

Edit: But still all this is a "bushfix" for dates.. you should try to manage the correct conversion when you download or import the data.

[celinedion]

Thank you...but I don't understand what you are saying...I can see it worked in your file...but can't make it work in mine....it's the numpy in me.

I have just copied the whole on column A to a new sheet and tried doing what you said...but can't get your results..sorry

[sandy666]

If you answer to someone use @name

[celinedion]

Sorry sandy666 my reply was to you

[sandy666]

ok, watch video

i did mistake and on the end I pasted result into A1 but should be in A2

[sandy666]

I'm out so...

If that takes care of your original question, & to say Thanks and for better Motivation, please

1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
   then
2. select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.

If you did it already - ignore it.
Thank you.

[celinedion]

Thanks for the video sandy666 I followed it....but still did not get the right results....I think I am just going to have to spend day's doing it all manually....and then as I download the data each day make sure it is correct

[celinedion]

Thanks Roel.....However I was unable to make this work....I think it is most likely me who just has no idea what I'm doing...however your help was very much appreciated

[sandy666]

check your local settings

localsettings.jpg

or

formatcells.jpg

and one more thing: don't use CENTER because you'll never know what kind of data is in

center.jpg

TEXT is aligned to the left , NUMBER to the right by default

[celinedion]

sandy666 just checked all look as they should be

[sandy666]

I've the same and I did steps as above and it works

sorry but I've no idea what is wrong there


maybe try
select all in temporary sheet than use

clearall.jpg

then start steps

I don't remember if Clear All exist in 2010

[celinedion]

sandy666 I guess it's just me.....you know what your doing I really don't...Just trying to get the date and times in order from lowest to highest...but without the dates being correct it won't work......Started doing it manually only another 150,000 dates to change....might take a while

[sandy666]

Copy 150 000 dates and post here (in excel file of course )

[celinedion]

Thanks sandy666 just had one last try...but still nothing....so manual it is...

[sandy666]

I said copy whole column with dates to the new workbook and post it here. I'll try to do it for you. if file will be too big save workbook as xlsb

[celinedion]

Sorry sandy666 misunderstood

I have copied all 250,000 dates right and wrong for you to have a play with...had to zip it as to big

[sandy666]

here is............
(sorry for delay but I was thinking you give up )
copy last column and paste as values to your original workbook

[Rodguirre]

For small documents it is easy, but for huge it's complicated, only for me

[sandy666]

why? it's taking less than minute (stopwatch: 20 secs)

[celinedion]

Thanks sandy666

When I pasted it into my spreadsheet..it did not work...I got #REF! in all cells.....And in the sheet you sent me if I deleted the first 2 columns I got the same.....So what I have done is carried forward the rest of the data to your sheet...but have had to keep all three columns....to be able to maintain the correct dates...Not ideal but at least I have the dates in the correct format...plus I've had to delete data from after March as it had double data in it.

I'll work with this and see how it goes....Thanks for your efforts much appreciated

[sandy666]

you didn't read instruction: PASTE AS VALUES

pasteasvalues.jpg

[celinedion]

OK done that now....and all looks good apart from May having doubles runners...but I have deleted them and will download again...Thanks sandy666 for your persistence..much appreciated.....I will call it closed now.....Have a nice evening

[sandy666]

U2 .