Please Help With Sorting by text: 7 days 6 hours 42 minutes 57 seconds

[miva0601]

Hello,

I'm creating a pivot table and would like to sort by the below text under Column Time In Queue.

What I've been able to do so far is convert the days, hours, minutes, seconds into a "d.h:mm:ss" format but it looks messy and I'm not sure it's correct.


[TABLE="width: 483"]
<tbody>[TR]
[TD]Time In Queue[/TD]
[TD]Invoice Amount[/TD]
[TD]Vendor ID[/TD]
[/TR]
[TR]
[TD]22 days 6 hours 45 minutes 51 seconds[/TD]
[TD="align: right"]$20.26[/TD]
[TD="align: right"]326553[/TD]
[/TR]
[TR]
[TD]21 days 21 hours 3 minutes 38 seconds[/TD]
[TD="align: right"]$244.12[/TD]
[TD="align: right"]4142[/TD]
[/TR]
[TR]
[TD]21 days 20 hours 30 minutes 55 seconds[/TD]
[TD="align: right"]$3,271.25[/TD]
[TD="align: right"]9373[/TD]
[/TR]
[TR]
[TD]16 days 2 hours 44 minutes 9 seconds[/TD]
[TD="align: right"]$2,108.49[/TD]
[TD="align: right"]340233[/TD]
[/TR]
[TR]
[TD]14 days 11 hours 22 minutes 6 seconds[/TD]
[TD="align: right"]$90.00[/TD]
[TD="align: right"]342924[/TD]
[/TR]
[TR]
[TD]14 days 9 hours 20 minutes 28 seconds[/TD]
[TD="align: right"]$86.48[/TD]
[TD="align: right"]331774[/TD]
[/TR]
[TR]
[TD]13 days 17 hours 59 minutes 5 seconds[/TD]
[TD="align: right"]$10,590.01[/TD]
[TD="align: right"]29130[/TD]
[/TR]
[TR]
[TD]13 days 17 hours 58 minutes 15 seconds[/TD]
[TD="align: right"]$10,451.08[/TD]
[TD="align: right"]29130[/TD]
[/TR]
[TR]
[TD]8 days 20 hours 20 minutes 26 seconds[/TD]
[TD="align: right"]$1,221.96[/TD]
[TD="align: right"]6156[/TD]
[/TR]
[TR]
[TD]8 days 20 hours 18 minutes 10 seconds[/TD]
[TD="align: right"]$249.97[/TD]
[TD="align: right"]6156[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Time in Queue[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Count of Days[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Hour[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] Minute[/TD]
[TD]Second[/TD]
[TD]Time Value[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]Time Value Result[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]d.h:mm:ss[/TD]
[TD][TABLE="width: 191"]
<tbody>[TR]
[TD="class: xl65, width: 191"]d.h:mm:ss Result[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]22 days 6 hours 45 minutes 51 seconds[/TD]
[TD]22[/TD]
[TD]6[/TD]
[TD]45[/TD]
[TD]51[/TD]
[TD]'=TIME(E2,F2,G2)[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125, align: right"]6:45:51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 283"]
<tbody>[TR]
[TD="class: xl65, width: 283"]=D2&"."&TEXT([@[Time Value]],"h:mm:ss")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 191"]
<tbody>[TR]
[TD="class: xl65, width: 191"]22.6:45:51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Assuming all your data will always have the format in your OP, here's a UDF (User-Defined Function) you can use like a worksheet function after you install it. Use it as in the example below and you can then sort on Time (Days) column.

Excel Workbook
	A	B	C	D
1	Time In Queue	Invoice Amount	Vendor ID	Time (Days)
2	22 days 6 hours 45 minutes 51 seconds	$20.26	326553	22.2818403
3	21 days 21 hours 3 minutes 38 seconds	$244.12	4142	21.8775231
4	21 days 20 hours 30 minutes 55 seconds	$3,271.25	9373	21.8548032
5	16 days 2 hours 44 minutes 9 seconds	$2,108.49	340233	16.1139931
6	14 days 11 hours 22 minutes 6 seconds	$90.00	342924	14.4736806
7	14 days 9 hours 20 minutes 28 seconds	$86.48	331774	14.389213
8	13 days 17 hours 59 minutes 5 seconds	$10,590.01	29130	13.7493634
9	13 days 17 hours 58 minutes 15 seconds	$10,451.08	29130	13.7487847
10	8 days 20 hours 20 minutes 26 seconds	$1,221.96	6156	8.84752315
11	8 days 20 hours 18 minutes 10 seconds	$249.97	6156	8.84594907
Sheet1

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Function DurationInDays(S As String) As Double
Dim V As Variant, i As Long, Days As Long, Hours As Double, Minutes As Double, Seconds As Double
V = Split(S, " ")
Days = V(0)
Hours = V(2) / 24
Minutes = V(4) / 60 / 24
Seconds = V(6) / 60 / 60 / 24
DurationInDays = Days + Hours + Minutes + Seconds
End Function