Date Conversion (19XX to 20XX)

[Bluendedmoon]

I was converting a Word document to Excel and the dates (MM/DD/YY) were transferred as text and had the error notification. I tried to remedy this by accepting error help and modifying the dates to the respective 4 year format (19XX, 20XX). We are talking about a lot of data here (about 650 pages). I messed up somewhere because now a lot of my dates that should have been 20XX are now 19XX. The error is no longer being recognized but I need to change most of the 19XX dates to 20XX dates. Is there a macro I can run to do this all in one shot? I need all years between 1900 and 1913 to be changed to 2000-2013. Any help would be greatly appreciated!

[ragulduy]

I guess you could use this formula:
=IF(AND(A1<=DATE(1913,12,31),A1>=DATE(1900,1,1)),A1+36525,A1)

Can put a macro together put I'd need an example workbook showing what everything looks like

[wenqq3]

Hi Bluendedmoon,
Since you saying 650 worksheet, can you upload 1 worksheet with date only.
So i can write a macro for you to change the date.

[Bluendedmoon]

Here is a sample of the document I'm working with. Thank you so much!

[ChemistB]

To continue with yudlugar's excellent suggestion, you could then Copy the results of your formula (i.e. select column B> Copy) and Paste Special> Values back on top of column A. Then clear column B.

[ragulduy]

As a macro:

Please Login or Register  to view this content.

[wenqq3]

Please Login or Register  to view this content.

Please set the range, and you have many worksheets in one workbook,so it take long time.

[Bluendedmoon]

This formula worked like a charm and was a lifesaver! Thank you everyone.

[Jean Neige]

Hi guys,
I am facing the same problem with dates.
For example, I would like to change 08-Jan-17 into 1/8/2017. I tried recording a macro to see how I could select the option "convert XX to 20XX" offered in the box (see attached), but nothing came out of it.

Screen Shot 2017-05-16 at 9.56.05 AM.png.
I have to do this modification in Column C on over 10 thousands lines.
Would someone have a code for this?
Thanks!

[ChemistB]

What is the default format for dates in your region, m d yy or d m yy?
Is 08-Jan-17 text or a number? (To find out, in another cell, enter this formula =ISNUMBER(A1) where A1 is the cell your date is in. It should give a "TRUE" answer.

If the answer is true, simply select the cell and Format Cells> Number> Date and find the format you want.

[Jean Neige]

Hi ChemistB,

The default format is dd-mmm-yy.
08-Jan-17 is not a number. The only thing making things complicated is that warning sign that appears when I try to convert all the date in the following format: mm/dd/yyyy. I would need to convert XX to 20XX as default for the rest of the dates I want to modify.
I cannot find a macro to do that anywhere.

Thanks!

[ChemistB]

Can you upload an example spreadsheet

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

[Jean Neige]

See example attached.

The beginning contains the dates in the right format. Format to modify comes in at row 159.

Thanks!

[ChemistB]

Try something like
For month day year
=(MID(B160,FIND("-",B160)+1,3)&" "&LEFT(B160,2)&", 2017")+0
For day month year
=(Left(B160,2) &"-" & MID(B160, FIND("-", B160)+1, 3) & "-2017")+0
Do either of those work?

[Jean Neige]

That first formula works well. I am curious about what +0 does?

I am wondering how I could integrate this into a macro with a loop without using any .select.

Thank you for your help so far!

[ChemistB]

The LEFT and MID functions are made to manipulate text strings so Excel sees any result from that as a string. The +0 helps Excel see it as a number.

I would start a new post, probably in the "Excel Programming/VBA forum asking for a macro to do what you want to do. Include your workbook so they know what they are working with.