Sorting Time/Date When Derived From A Formula

[Woody Pecker]

I have some data which is in hh:mm ddd format, but is a result of a formula from some text based data, so the actual result (eg:- 03:21 Wed) might actually present as text.

How can I sort the column of data when it rolls through midnight into another day without the earlier times moving to the top

eg:-

15:32 Wed

20:00 Wed

21:30 Wed

22:00 Wed

23:30 Wed

23:51 Wed

00:01 Thu

00:30 Thu

00:30 Thu

01:00 Thu

 

[6StringJazzer]

The only way to sort this is to get it into standard date/time format (you can still display it as shown). What is the formula, and what is the original text that the formula refers to?

 

[Woody Pecker]

MASTER Warehouse Outbound Schedule NEWxlsx.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	WAREHOUSE OUTBOUND SCHEDULE -
2
3		LOAD LAST DROP ON THE HEADBOARD
4
5	Route	Delivery Name	Trip Pos	Pallets	Actual Pallets Loaded	Product	Load by Time -1hr	Actual Load Time	Carrier	Trailer Number	Delivery ref	Bay	Start time	Finish time	Loaded By	Comments		SUBBIE TIME ADJUSTMENT
6	21	SN HEMEL HEMPSTEAD	1	36		NTS	03:21 Wed		44T COVENTRY									03:21 Wed
Sheet1

Cell Formulas
Range	Formula
R6	R6	=IF(A6="","",IF(I6="",G6,IF(IF(IF($I6="44T COVENTRY",$G6,"")>"",IF($I6="44T COVENTRY",$G6,""),IF(IF($I6="DD COVENTRY",$G6,"")>"",IF($I6="DD COVENTRY",$G6,""),""))="",TEXT(IF(IF(IF(IF($I6="44T COVENTRY",$G6,"")="",IF(IF($I6="DD COVENTRY",$G6,"")="",$G6,FALSE()))=FALSE(),"",IF(IF($I6="44T COVENTRY",$G6,"")="",IF(IF($I6="DD COVENTRY",$G6,"")="",$G6,FALSE())))="",IF(IF($I6="44T COVENTRY",$G6,"")>"",IF($I6="44T COVENTRY",$G6,""),IF(IF($I6="DD COVENTRY",$G6,"")>"",IF($I6="DD COVENTRY",$G6,""),"")),LEFT(IF(IF(IF($I6="44T COVENTRY",$G6,"")="",IF(IF($I6="DD COVENTRY",$G6,"")="",$G6,FALSE()))=FALSE(),"",IF(IF($I6="44T COVENTRY",$G6,"")="",IF(IF($I6="DD COVENTRY",$G6,"")="",$G6,FALSE()))),5)-TIME(1,0,0)),"hh:mm")&" "&RIGHT($G6,3),IF(IF(IF(IF($I6="44T COVENTRY",$G6,"")="",IF(IF($I6="DD COVENTRY",$G6,"")="",$G6,FALSE()))=FALSE(),"",IF(IF($I6="44T COVENTRY",$G6,"")="",IF(IF($I6="DD COVENTRY",$G6,"")="",$G6,FALSE())))="",IF(IF($I6="44T COVENTRY",$G6,"")>"",IF($I6="44T COVENTRY",$G6,""),IF(IF($I6="DD COVENTRY",$G6,"")>"",IF($I6="DD COVENTRY",$G6,""),"")),LEFT(IF(IF(IF($I6="44T COVENTRY",$G6,"")="",IF(IF($I6="DD COVENTRY",$G6,"")="",$G6,FALSE()))=FALSE(),"",IF(IF($I6="44T COVENTRY",$G6,"")="",IF(IF($I6="DD COVENTRY",$G6,"")="",$G6,FALSE()))),5)-TIME(1,0,0)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R6	Expression	=R6=G6	text	NO
I2:I1048576	Cell Value	contains "COVENTRY DOUBLE MAN"	text	NO
I2:I1048576	Cell Value	contains "COVENTRY DOUBLEMAN"	text	NO
I2:I1048576	Cell Value	contains "COVENTRY DRIVER"	text	NO
G5:H5	Expression	=($A5=$A3)	text	YES
P5	Expression	=($A5=$A3)	text	YES
O5	Expression	=($A5=$A3)	text	YES
A5:F5,I5:N5,R5	Expression	=($A5=$A3)	text	YES
J5	Expression	=($A5=$A3)	text	YES
J5	Expression	=(#REF!=TRUE)	text	YES
A27:P38,A40:P1801,A5:P5,A7:P25,R5	Expression	=($A5=$A4)	text	YES
A6:P6	Expression	=($A6=#REF!)	text	YES

 

[Woody Pecker]

It's the data in Column R that need to be the basis of the sort

 

[6StringJazzer]

The formula in R refers back to G6. Is G6 typed in as text?

 

[Woody Pecker]

The formula in R refers back to G6. Is G6 typed in as text?

Apologies for the delay, yes G6 is Text format

 

[6StringJazzer]

How do you get that data into your sheet currently?

The best solution is to enter column G as date/time data instead of text. Such as

12/12/2021 03:21

and use a custom format

hh:mm Ddd

so that it displays the same way as now.

Otherwise if you want to sort column R you have to modify every reference to G6 in your formulas to this. The big problem here is with just a day of the week, we don't know what the actual date is. You need to have some baseline date that tells us what week we are talking about. Let's say we put Monday's date in A2, then this formula converts G6 to a sortable value:

=TIMEVALUE(LEFT(G6,5))+MATCH(RIGHT(G6,3),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0)+$A$2-1

Needless to say this will make your formula very long. Better to put in date/time data to start with.

$scratch.xlsm
	A	B	C	D	E	F	G	H
1
2	12/13/2021
3
4
5							Original	Date/Time Value
6							03:21 Wed	03:21 Wed
Sheet4

Cell Formulas
Range	Formula
H6	H6	=TIMEVALUE(LEFT(G6,5))+MATCH(RIGHT(G6,3),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0)+$A$2-1