How to Sort DateTime by Date Only

[Ronald Wilson]

Is there a way to sort a Date and Time column by the Date value only? Or do I need to add a new column with a formula to get the Date only? Changing the format of the column to a date only does not eliminate the time value from the cell contents. Data below is sorted by CREATION_DATE then NAME.

NAMECREATED_BYCREATION_DATE
SMITH12873/21/17 4:01:01 PM
SMITH12873/21/17 4:01:18 PM
SMITH12873/21/17 4:01:27 PM
THOMPSON12873/21/17 4:01:37 PM
JONES12873/21/17 4:01:45 PM
SMITH12873/22/17 4:01:52 PM
SMITH12873/22/17 4:02:10 PM
SMITH12873/22/17 4:02:18 PM
JONES12873/22/17 4:02:31 PM

 

[Chihiro]

Helper is needed.

You can do it with VBA without helper... but isn't recommended. Much easier to add helper for set up and maintenance.

 

[NARAYANK991]

Hi ,

Why do you want to sort it only on the date ?

A date is an integer , and today's date would be an integer 42865.

Compared to this , a time is a decimal value between 0 and 1 ; thus 6 PM today would be a Date + Time value of 42865.75

Thus sorting by Date + Time will ensure that all entries for a particular date are together , which is what you would get by sorting date-wise.

Narayan

 

[Khalid NGO]

Hi,

Other than what posted above, you can remove the times by using Find & Replace option, then apply sort, see the following steps:

1) Select your range
2) Go to Find & Replace (Ctrl+H)
3) Find what: single space and *
4) Leave blank the Replace with filed
5) Replace all
6) Change the format to dd-mm-yyyy (or as desire)

Regards,

 

[Ronald Wilson]

Hi ,

Why do you want to sort it only on the date ?

A date is an integer , and today's date would be an integer 42865.

Compared to this , a time is a decimal value between 0 and 1 ; thus 6 PM today would be a Date + Time value of 42865.75

Thus sorting by Date + Time will ensure that all entries for a particular date are together , which is what you would get by sorting date-wise.

Narayan

Narayan,
I want to see the NAME field in order by Date and not DateTime. With DateTime, every second creates a separation, so in a larger data sample, you would see SMITH separated several times for the same Date because the Time changes.

Thanks,
Ronnie

 

[NARAYANK991]

Narayan,
I want to see the NAME field in order by Date and not DateTime. With DateTime, every second creates a separation, so in a larger data sample, you would see SMITH separated several times for the same Date because the Time changes.

Thanks,
Ronnie

Hi ,

In that case , I think the only way may be to use a helper column to isolate the Date portion , and then sort on that column.

Or if you do not need the time portion for any further processing , strip it off manually , as suggested by Khalid.

Narayan