Sorting By Date - Not Time

[JayTheKaz]

I inserted a column into my spreadsheet and input two sequential time inot the 1st two cells (00:00, 00:15). When i format the cells for time (Time-13:40), it automatically inserts a default date as part of the cell data. When i sort the data, it sorts by the date. I want to sort only by the time. Is that possible?

 

[jasonb75]

If you're only inserting a time then that is all that will be in the cell. If you choose a format that shows date and time then it will show a default of 00/01/1900 which will have no value for the purpose of sorting.

 

[JayTheKaz]

But it does still post according to the date. it doesn't autofill the same date. the date is progressive. Any sort you do, it's sorted by the date.

 

[jasonb75]

Exactly how are you entering the first time before autofilling? I've tried it in the way that you describe in your first post and everything is working the way that I said it should, with no dates shown.

The exception being if you autofill more than 96 rows (using 15 minute intervals). When it goes from 23:45 to 00:00, that is actually 24:00 or 1 day, not 00:00. The increments continue, it does not reset and loop back to the beginning, meaning that it will go to 24:15, 24:30, etc. If this is what you are encountering then you need to stop filling at 23:45 then copy the fist set of times and paste down instead of filling.

Also note that if you are using an alternative application then it may work differently. What I have described is based on excel. Alternatives like google sheets, open office calc, or anything else may not be the same.

 

[JayTheKaz]

I start on the 1st cell with 00:00, the next 00:15. I highlight the two and autofill to 25,000 by grabbing the lower left hand corner and dragging. Cutting and pasting to 25,000 cells isn't something I want to spend the time doing

 

[jasonb75]

You'll need to use a formula then convert it to values.

Enter this into the first cell and fill down to 25000

=MOD((ROWS(A$1:A1)-1)*"00:15",1)

Then copy the entire filled range and paste special > values (ctrl alt v, v, enter).

 

[JayTheKaz]

thanks. I'll give it a try