REPOST: Date format pre-1900.
Hi,
I have a table of immigration ships that have arrived in Australian shores. Some....okay most.....of the ships arrived before 1900. I was trying to sort by the arrival date, which is in the format of DD MMM YYYY, but that didn't work, as there were some 1900 dates. Tried to re-style the column to DD/MM/YYYY, but the pre-1900 dates don't change to the new style. Any suggestions on how to utilise the pre-1900 dates, so that Excel can recognise them. Cheers in advance. Craig. P.S. Originally sent on the 15/02/2005 - not sure if someone knowledgable missed it in the other 1000s of messages sent every day. Cheers. |
Maybe you could enter all your dates as text:
'1882/11/23 Including dates since 1900. You could even convert your real dates to Text with a formula like: =TEXT(A1,"yyyy/mm/dd") Anyway you do it, you'll want to be sorting text (not real dates) so that they sort correctly. "Craig & Co." wrote: Hi, I have a table of immigration ships that have arrived in Australian shores. Some....okay most.....of the ships arrived before 1900. I was trying to sort by the arrival date, which is in the format of DD MMM YYYY, but that didn't work, as there were some 1900 dates. Tried to re-style the column to DD/MM/YYYY, but the pre-1900 dates don't change to the new style. Any suggestions on how to utilise the pre-1900 dates, so that Excel can recognise them. Cheers in advance. Craig. P.S. Originally sent on the 15/02/2005 - not sure if someone knowledgable missed it in the other 1000s of messages sent every day. Cheers. -- Dave Peterson |
Hi Craig,
Excel does not recognize any date before 1900, but VBA does. Keep those dates as text. You can use John Walkenbach's Extended Dates routines to work with them arithmetically, but be aware that there are problems as calendar changes -- I believe that is at least mentioned on his site. Extended Date Functions Add-In http://www.j-walk.com/ss/excel/files/xdate.htm More information on Date and Time http://www.mvps.org/dmcritchie/excel/datetime.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Craig & Co." wrote in message news:42151ca3$0$57503 I have a table of immigration ships that have arrived in Australian shores. Some....okay most.....of the ships arrived before 1900. |
All times are GMT +1. The time now is 05:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com