Sorting Date and Time

[markster]

Hello there

A column in my spreadsheet contains the date and time of transaction in the following format:

2021.05.10 01:00:29

There's 20,000 lines and i need to sort by date and time but i can't get it to work using the sort function - does anyone have any ideas?

Thanks
Mark

2021.05.10 01:00:29

 

[markster]

Oh I've just had feedback from my boss and now he's asked that each transactionbe put into an hour time slot based on time. So for instance:

9am is in the 9:00 to 09:59 time frame
12noon is in the 12:00 to 12:59 time frame
16:30 is in the 16:00 to 16:59 time frame

The time only is in Column B2 downwards in the following format 01:00:29

Maybe VBA code would be better so don't have to have a super long formula.

This is the last change in specification - sorry again - I'm up against it.

Thanks
Mark

 

[Toadstool]

Like this?

Markster.xlsx
	A	B	C
1	Date/Time	As number for sorting	Category
2	2021.05.10 01:00:29	0.042002315	1
3	2021.05.10 14:00:29	0.583668981	14
4	2021.05.10 14:00:30	0.583680556	14
5	2021.05.10 14:30:00	0.604166667	14
6	2021.05.10 21:00:29	0.875335648	21
7	2021.05.10 14:00:29	0.583668981	14
8	2021.05.10 19:00:29	0.792002315	19
9	2021.05.10 11:00:29	0.458668981	11
10	2021.05.10 05:00:29	0.208668981	5
Sheet1 (2)

Cell Formulas
Range	Formula
B2:B10	B2	=TIMEVALUE(RIGHT(SUBSTITUTE(A2,".","/"),8))
C2:C10	C2	=HOUR(B2)

 

[markster]

Perfect thanks - works a treat. Have a good evening.
M

 

[Toadstool]

Perfect thanks - works a treat. Have a good evening.
M

You're welcome!