Format Date

[Ille]

Dear All,
working with Excel 2007, I've received a huge workbook with Dates (e.g. Jan,
12, 2009). The owner of the book has chosen the Dates-Format 1904. When I
import the data, all dates change to four years earlier.
Do you know any possibility for me to work with this worksheets with the
1900-format and still have the correct dates?
Thank you very much in advance

 

[Luke M]

Why not change the format back to working in 1900 system? (if import adds 4
years, changing back to 1900 should subtract 4 years, thus giving correct
dates).

 

[JoeU2004]

Why not change the format back to working in 1900 system?
(if import adds 4 years, changing back to 1900 should subtract
4 years, thus giving correct dates).

Because the worksheet designer might have been following the dubious advice
of some people in this forum and used the 1904-date-system option to permit
them to display negative elapsed time :-(. Deselecting the 1904 option
might cause other parts of the worksheet to seem to fail (display "###").

IMHO, we should not be advising people to use the 1904-date-system option,
except perhaps when sharing worksheets with Macs. (I don't know if the
1904-date-system option is needed in that case. I can only imagine that it
might be.)


----- original messages -----

 

[Gord Dibben]

Type 1462 in an unused cell formatted to General.

Copy it and select the range of date.

Edit>Paste Special>Add>OK>Esc.


Gord Dibben MS Excel MVP