MS Excel Help!

[Dusty Britches]

I've run out of options, and I know there are a few MS Excel experts here.

I got a file from a client who entered a date formula for a date, rather than formating the cell. The contents of the cell read =DATE,6,9,1 and the visual is 9/01/06. (I know - old school.)

I need the date format for some analysis, so I tried to reformat to a date. The visual changes to 9/01/1906.

Does anyone know how I can quickly convert to a date format and wind up with September 1, 2006?

PM are also welcome. Thanks!

[rberg]

Excel likes to use a number if possible.
Sept 1, 2006 has the number 38961
To start, you can go to the function DATE and put in year, month, day and it will convert to the number.
Then you can go to the format cell section and make the date read any format you want.

give me a call and I can walk you through it.
512 253-0053

[Dusty Britches]

I have over 480 lines with 2 sets of dates each. I've tried converting to a number and to text, both codes come back as 1906 for the year.

Text for Sept. 1, 1906 is 2436
Text for Sept. 1, 2006 is 38961

[rberg]

OK, the difference between the century is 36525.
insert another column, add 36525 to the date and it should give you the correct one. If that works correctly, copy the formula to all the other rows.
Then hide the old columns.

There is probably a better way, but this should work.

[Double L]

=Date(6,9,1)+36525

[Double L]

oops. Rberg was posting whilst I was meditating on it.

[Dusty Britches]

It worked, thanks.

Actually, I tried the answer you posted, Double L, but it did not work. So then I put 36525 in a seperate cell, added it to the original and summed to a third cell, converted the third cells to a date format. Then I copied the column and pasted "special" only values into the original cell.

[Double L]

Might be a difference in versions of Excel. I had to enclose the numbers in parentheses to get your original date formula to work. When I did that, I added the 36525 and it worked. Hey, at least you got it figured out. I always celebrate anytime I solve an issue in Excel...give yourself a high five Dusty!