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

 

[Marc L]

Hi,​

​

maybe 'cause that's just text rather than date ! Check with ISNUMBER worksheet function : TRUE means date but FALSE is text …​

 

[Toadstool]

Hi Markster,

=SUBSTITUTE(B2,".","/")+0
Will return 44326.042 which represents 10 May 2021 at 1am and 29 seconds which you can then sort.

 

[markster]

Ok great thanks both

 

[Dave Patton]

The sort should work OK.
to secure some help, you need to provide some information

- is the data text or numbers
- is the information real data or a formula

Please post an extract of your data with the forum's XL2BB.

 

[markster]

Hey just as a follow up to this I want to categorise the dates and times into 2 time periods as follows:

Any transactions falling into time periods

14:30 to 21:15
21:16 to 14:29

My time data is in column B time period in column C so I need a formula in Column C that would return the relevant time period eg.

14:45 would be in 14:30 to 21:15
21:20 would be in 21:16 to 14:29

Thanks again in advance for your help.

Cheers Mark

 

[Toadstool]

Like this?

Markster.xlsx
	A	B	C
1	Date/Time	As number for sorting	Category
2	2021.05.10 01:00:29	44326.042	2
3	2021.05.10 14:00:29	44326.58367	2
4	2021.05.10 14:00:30	44326.58368	2
5	2021.05.10 14:30:00	44326.60417	1
6	2021.05.10 21:00:29	44326.87534	1
7	2021.05.10 14:00:29	44326.58367	2
8	2021.05.10 19:00:29	44326.792	1
9	2021.05.10 11:00:29	44326.45867	2
10	2021.05.10 05:00:29	44326.20867	2
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=SUBSTITUTE(A2,".","/")+0
C2:C10	C2	=IF(AND(TIMEVALUE(RIGHT(A2,8))>=TIME(14,30,0),TIMEVALUE(RIGHT(A2,8))<=TIME(21,15,0)),1,2)

 

[markster]

Actually that should work mate - thanks. M

 

[markster]

Like this?

Markster.xlsx
	A	B	C
1	Date/Time	As number for sorting	Category
2	2021.05.10 01:00:29	44326.042	2
3	2021.05.10 14:00:29	44326.58367	2
4	2021.05.10 14:00:30	44326.58368	2
5	2021.05.10 14:30:00	44326.60417	1
6	2021.05.10 21:00:29	44326.87534	1
7	2021.05.10 14:00:29	44326.58367	2
8	2021.05.10 19:00:29	44326.792	1
9	2021.05.10 11:00:29	44326.45867	2
10	2021.05.10 05:00:29	44326.20867	2
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=SUBSTITUTE(A2,".","/")+0
C2:C10	C2	=IF(AND(TIMEVALUE(RIGHT(A2,8))>=TIME(14,30,0),TIMEVALUE(RIGHT(A2,8))<=TIME(21,15,0)),1,2)

So - I have changed a little bit so have DATE ONLY in Cell A2 TIME ONLY in B2. So when use the formula referencing B2 it just returns and error. Think it's my fault for splitting it out - can the formula be adapted? Thanks Mark

Like this?

Markster.xlsx
	A	B	C
1	Date/Time	As number for sorting	Category
2	2021.05.10 01:00:29	44326.042	2
3	2021.05.10 14:00:29	44326.58367	2
4	2021.05.10 14:00:30	44326.58368	2
5	2021.05.10 14:30:00	44326.60417	1
6	2021.05.10 21:00:29	44326.87534	1
7	2021.05.10 14:00:29	44326.58367	2
8	2021.05.10 19:00:29	44326.792	1
9	2021.05.10 11:00:29	44326.45867	2
10	2021.05.10 05:00:29	44326.20867	2
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=SUBSTITUTE(A2,".","/")+0
C2:C10	C2	=IF(AND(TIMEVALUE(RIGHT(A2,8))>=TIME(14,30,0),TIMEVALUE(RIGHT(A2,8))<=TIME(21,15,0)),1,2)

 

[markster]

Hey really sorry but I've split out the date and time so Date only is in A2 and Time Only in B2. So when I used the formula and relate to. B2 it doesn't work - can it be adapted? Sorry again. thanks Mark