sort by dates with inconsistant format (Outlook 2000)

Topic

New Reply

I need to make an document listing items by date and I need to be able to sort by date. The problem I am running into is the dates of the items are inconsistent. One item may have a date such as 11/20/2004, the next only the year. Is there a way to enter items that don’t have the dd/mm/yy or dd/mm/yyyy format and have them all sort? Thanks

Reply | Quote

Replies

You can add a calculated column that contains real dates, and sort on that. For example, assuming all dates are in 1930 or later, enter this formula in C2:

=IF(A2>DATE(1930,1,1),A2,DATE(A2,1,1))

and fill down as far as necessary. Sort on column C. See attached version.

Reply | Quote

I see what you mean but that assigns a month and date and we can’t have that. These are publication dates. One might be an article published on a certain date, another could be an annual report that’s only date is the year and we need to show it that way. I tried replacing the 1s in the formulas with 0 but it didn’t work. Any other suggestions? Thanks

Reply | Quote

You could keep the original column for display purposes and use the calculated column only for sorting.

If that is not what you want, you will have to explain what you do want.

Reply | Quote

That could do the trick. Thank you your prompt responses. I appreciate all the help on get from folks here in the Lounge!

Reply | Quote

That could do the trick. Thank you your prompt responses. I appreciate all the help on get from folks here in the Lounge!

Reply | Quote

You could keep the original column for display purposes and use the calculated column only for sorting.

If that is not what you want, you will have to explain what you do want.

Reply | Quote

I see what you mean but that assigns a month and date and we can’t have that. These are publication dates. One might be an article published on a certain date, another could be an annual report that’s only date is the year and we need to show it that way. I tried replacing the 1s in the formulas with 0 but it didn’t work. Any other suggestions? Thanks

Reply | Quote

You can add a calculated column that contains real dates, and sort on that. For example, assuming all dates are in 1930 or later, enter this formula in C2:

=IF(A2>DATE(1930,1,1),A2,DATE(A2,1,1))

and fill down as far as necessary. Sort on column C. See attached version.

Reply | Quote