Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Dates in Excel

Options
  • 07-10-2002 4:38am
    #1
    Dun
    Registered Users Posts: 1,967 ✭✭✭


    I have a column of dates in Excel that are all between 1852 and 1900. The problem is that they are all in dd/mm/yyyy format and I need to get them into dd mmm yyyy format. I know how to do it, but I've come across a snag. I have no idea how to get Excel to recognise them as dates.

    It'll recognise all the post-1900 dates but none of the pre-1900 ones. At the moment I'm hoping not to have to re-type them (well type them - I didn't have to do them in the first place) as there are almost 2000 of them.

    Anyone have any ideas about how to make Excel recognise pre-1900 dates as dates?


Comments

  • Options
    #2
    sceptre
    Registered Users Posts: 19,608 ✭✭✭✭sceptre


    Ah, now there's a dilly of a pickle.

    Excel can't handle any dates before Jan 1, 1900 (or 1904 in macland) - it regards that as day one.
    From MS Excel help:
    Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.


    Solution? Use the XDate addon

    Free and all that (174k download) - just be careful not to share files with a PC that hasn't had the addon installed.


  • Options
    #3
    Dun
    Registered Users Posts: 1,967 ✭✭✭Dun


    Thanks! I bet you can realise how glad I am at not having to retype those blooming dates :D


Advertisement