Problem with date formatting in Excel

  Mad Mick 21:23 12 Nov 2006

I am setting up an Excel spreadsheet to include the date of birth of past relatives. When I put a date of say 22/09/1938 it will convert to 22 September 1938. A problem arises when I insert a date from the 1800's. I tried to insert a date of 25/08/1886 and it stays as that instead of converting to 25 August 1886. Can anyone suggest a remedy?

  Peter 21:34 12 Nov 2006


I don't think the date function will work correctly with dates earlier than 01/01/1900. Perhaps someone with more knowledge of Excel may know a work around for this problem.


  VoG II 21:38 12 Nov 2006
  Mad Mick 21:38 12 Nov 2006

Thank you Peter. I am coming to that conclusion myself. If someone doesn't come up with a solution by tomorrow I will close this subject.

  Mad Mick 22:18 12 Nov 2006

Thank you VoG™ but that link is too far advanced for me to follow. Pleas accept my thanks for taking the trouble to post but I need step by step instructions. I don't know how to get Excel to accept pre 1900 years in the same way it will accept post 1900 years. Most of my past relatives lived in the 1800's so I need to see if I can reconcile this problem. I think the best way forward is for me to accept that all date prior to 1900 will have to be in the 25/08/1886 format. Your time and effort is much appreciated.

  Simsy 23:03 12 Nov 2006

with the formatting?

Do you need to do any arithmetic with the dates in question? I suspect you do, and if so the following may be of help, with the maths...

click here

Good luck,



  Terry Brown 09:19 13 Nov 2006

I was looking for a wage/time calculator. This is Ideal
Please do not add any comments to this as I do not intend to 'Hijack' the thread.

  Mad Mick 12:50 13 Nov 2006

Thank you all for your input. As I said above I will just leave the dates in the 25/08/1888 format. I have made note of all the suggestions and will peruse them at my leisure. Again thanks to one and all for your input.

  picklefactory 21:17 21 Nov 2006

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Honor 9 Lite review

18 Best Adult Colouring In Books 2018

HomePod review

Les meilleures séries Netflix (2018)