Sorting dates going back into the 18th century

[Glio]

I have a worksheet with a column for dates. The dates are entered in format dd/mm/yyyy. I would like to sort the dates in chronological order, however, the dates are all in the 18th century i.e. 01/01/1750. Excel does not want to sort these dates at all. I have also tried to format the column the dates are in so they show as 01-Jan-1750, but that won't work either. Is there a date limitation in Excel. I am using Excel for Mac 2011. TIA

[jason.b75]

Excel uses serial numbers to track dates, for windows versions, Day 1 is 01/01/1900. I believe that day 1 for the mac version falls somewhere in 1904.

You could reverse the format of the dates in another column so that excel thinks they are just numbers, which you could then use to sort.

Using the format YYYYMMDD (numbers only, no other characters) sorting in numeric order would be the same as chronological order, you could extract this from your original dates using formula or vba.

[Ace_XL]

Yes, Calendar dates in Excel date back to 1/1/1900 only

Excel will read the date in year 1750 as text and not a number thereby throwing your sorting results out of whack.

Input these dates as yyyymmdd i.e. 17500819 for Aug 19,1750. This way Excel will sort the 'text data' based on year, then month and then date.

[LJMetzger]

Hi Glio,
Excel (for Windows) dates start at Jan 1, 1900 with an option for starting at 1904 in Excel Options.

Apparently the MAC uses the 1904 date system: https://support.microsoft.com/en-us/kb/303216 which is also of no help to you.


There are at least two things you can do:
a. Store the dates in yyyymmdd format such as 17501222.
b. Use a custom VBA function that operates on dates prior to 1900. See https://support.microsoft.com/en-us/kb/245104
I have not tried this function.

Please be aware that the calendar has a discontinuity in 1752, when 11 days were removed in September. See https://en.wikipedia.org/wiki/1752

Lewis

[Glio]

Thanks to all for your comments. I will have to change my date input to yyyymmdd as suggested. I will use a simple formula to do the change. Thanks for your responses. I just hoped there was an easier way ;( Happy New Year