Excel 2010 autofill dates not correct progression

[Carni]

I have entered a date into a cell, formatted the cell to the matching
date format eg:
1869/01/22
meaning 22nd January 1869.
When I drag the selection to autofill the next cells, the day of the
month does not progress to 02 when the day passes 31, so it ends up
with 1869/01/32, 1869/01/33 etc
I have tried using several available date formats and I always have
the same problem.

 

[lifescholar]

I have entered a date into a cell, formatted the cell to the matching
date format eg:
1869/01/22
meaning 22nd January 1869.
When I drag the selection to autofill the next cells, the day of the
month does not progress to 02 when the day passes 31, so it ends up
with 1869/01/32, 1869/01/33 etc
I have tried using several available date formats and I always have
the same problem.

Excel can only store dates later than 1 January 1900, so in your
example Excel assumes that the entered value is text. It therefore
increments the last numeric portion of the value, disregarding
everything before the second / character.

 

[Gord]

If you want to work with dates prior of 1900 you can benefit from an
add-in by John Walkenbach.

http://spreadsheetpage.com/index.php/tip/extended_date_functions/


Gord Dibben Microsoft Excel MVP

 

[Carni]

If you want to work with dates prior of 1900 you can benefit from an
add-in by John Walkenbach.

http://spreadsheetpage.com/index.php/tip/extended_date_functions/

Gord Dibben    Microsoft Excel MVP

Thank you lifescholar and Gord for your answers.