Date and Time Sorts (Excel 2003 SP3)

Topic

New Reply

Trying to figure out a way to sort appointments by date then time (am first, then pm on same date). The time displays as military time in the column and am/pm time in the formula bar. Do you know of a way to sort by time in chronological order by date and display with am/pm time in the column? Sorting with am/pm doesn’t produce the right result. Carol

Reply | Quote

Replies

I would put the date and time together in 1 column. If they are both serial date/times you can just add the 2 together then delete the individual columns. The single column can be formatted (via format – cells) to display both date/time (custom format (no quotes): “mmm d, yyyy hh:mm am/pm”).

Then you can sort with this column.

Steve

Reply | Quote

That works great—thanks so much.

Reply | Quote

If “time displays as military time in the column and am/pm time in the formula bar”, then that indicates that those values are Excel Date Time values. However, you haven’t told us anything that we can use to determine if those values also contain the date. If those values do not contain the date, then they will sort in order without reguard to which date they fall on. Are the dates by chance in a different column. You could save us all a lot of time trying to figure out what you have if you would upload the workbook. You can delete everything but the column or columns that contain the dates and times.

Reply | Quote

I’m sorry about the lack of date information. The date is in one column and the time in another. We sort by date, then time.

Reply | Quote