Date formats in excel

Hey All,

I have dates that I am having trouble formatting. The dates are year, month, and date (751205). I can't seem to get excel to format them in mm/dd/yyyy. Excel 2007 is adding 42 in front of the year (4275). I've tried converting using the =DateValue function, but it is not working. Any suggestions.

Thanks.

 

I just ran into a very similar issue after "upgrading" from Office 2k to Office 2010. Was about to post a new HELP thread when I saw this one.

This has trashed a business spreadsheet that I must restore. The date still displays correctly in the formula bar, but not in the cell. I have tried all the formatting options with no success. Here's an example:

4/29/10 appears as 40297. 5/4/10 appears as 40302.

Reopening with Excel 2k does NOT solve the problem. The problem (including any new entries is now identical in both versions of Excel. Files that have not been saved with 2010 do not have this problem.

Any assistance would be greatly appreciated!

 

I found a solution. I have no idea why this worked, but it did.

1. Create a new book.

2. Pre-format the date columns.

3. Cut & paste 1 entire column at a time

Problem solved. it will save including the new formatting as office 2010 .xlsx or as Office 2k .xls.

I have one other issue, but as I've been up 22 hours will post about it later.

TJV, I hope this also solves your problem.

 

Hi Geff,

Thanks for the help, but it is not working. Excel 2007 is taking the birth date 841215 and converting it to 3/01/4203.