Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Can't Sort Based On Date/Time

Copper Contributor

Hoping someone can help.

 

I have a simple Excel data set where the first colum is the date (i.e. Dec 1 2023) and the second column is the time (i.e. 7:12 AM).

 

I have been unable to sort the data based on the Date/Time.

 

The formatting of the Date/Time are "General."

 

I thought that I could just change the formatting to one of the Date and Time formats, but it does not allow me to do that.

 

Any ideas on how to fix this?

 

Thanks.

2 Replies

@KM2_344 


@KM2_344 wrote:

I thought that I could just change the formatting to one of the Date and Time formats, but it does not allow me to do that.

Why not?

What happens?

 

 

@KM2_344 

Dates and times in Excel are numbers.  Specifically they are the number of days measured from January 1st 1900 with fractions of a day representing the time of day.  So, what looks like 'normal' dates

image.png

are actually

image.png

when formatted as general numbers.   If your 'General' formatted dates still look like US formatted dates then they are text rather than numbers, and Excel will not recognise them as dates and will instead simply sort the so-called dates into alphabetical order.

 

If you are lucky, you may find that the function DATEVALUE will convert the dates held as text to numbers

image.png