Clean and format dates

[JojoMaque]

Good morning,

I have a column in which I need to find the average duration however as you can see below the data needs cleaning. I would like to convert each cell to seconds if possible. which is the simplest way to tackle this?
The data is very messy with lots of mixed date formats both text and numerical.

Thank you

Duration
4 minutes
3 minutes

3 minutes
5 minutes
Driving
Seconds
10 seconds
<1 minute
3 minutes
3 minutes
3 minutes
5 min
5 minutes
3 minutes
3 minutes
3 minutes
30 seconds
30 seconds
1 minute
2 minutes
2 minutes
1 minute
2 minutes
20 minutes

15 seconds

80 sec
3 minutes
10 mins
2 min
2 minutes
30 seconds
5-8 min
4 hrs
4 hrs
10 seconds
5 seconds
3 min
50min
3-4 minutes
3-4 minutes
Approximately 5 minutes
1hour
1 min
1 minute
1 minute
10 Minutes
10 Minutes
10 Minutes
1 minute
30 mins
30 mins
30 mins
3-4 minutes
4
10 seconds
15 seconds
1 minute
Unknown
10 minutes
10 seconds
6h 30 mins
10 seconds
10 seconds
1 minute

Seconds
20 seconds
12 minutes

5 seconds
20 seconds
8-10 seconds
1 minute
~2 seconds
1 minute

~2 minutes
10 minutes

1 minute
10 seconds
5 hours
2 hours
Ongoing

28 seconds
20 minutes
2 seconds
6-8 minutes
60 seconds
30 seconds
10 minutes
20 minutes
1 minute
~4 seconds
2 minutes
20 mins
1 minute
10 minutes
1.5 hours
30 minutes
3 seconds
1 hour
20 minutes
5 minutes
1-2 minutes
3-5 seconds
30 seconds
4 seconds
5 seconds
unknown
2 minutes
0.208333333
10 minutes
3.5 minutes
20 minutes
2 minutes
3
5 minutes
45 minutes
2 minutes
5 minutes
Hour
30 seconds
2 seconds
3 minutes
5 minutes
4 minutes
2 minutes
3 minutes
10 minutes
Don't know
1 minute
Half an hour
6 minutes
5 minutes
5 minutes

5-10 minutes
1 minute?
3 minutes
a few hours
20 minutes
20 seconds
2 seconds
3 seconds
1 hour 20 minutes
2 minutes
5 minutes
10 minutes
2 minutes
5 minutes
1 minute
2 hours
5 minutes
5 hours
30 minutes
4 minutes
5 Minutes
20 minutes
5 minutes
5-10 minutes
5 minutes
0.104166667
20 seconds

15 seconds
4 seconds
3 minutes
30 seconds
5-6 minute
~45 minutes
10 minutes
1 second
10 minutes
5 minutes
40 seconds
2 minutes
2 seconds
20 minutes
1-2 minutes
3 minutes
10 seconds
1 minute
8 minutes
20
10 minutes
10 minutes
5 minutes
5 minutes
15 minutes
25 seconds
2 minutes
5 minutes


20 minutes
5 minutes
:15
Approx. 1 minute
50 MINUTES
20 minutes
Seconds
8 min
Few seconds each
7 minutes
5 minutes
5 minutes
0.010416667
5 Minutes
2 minutes
3:00 Minutes
2 minutes
5 to 10 minutes
5 min maybe
5 minutes
5 minutes
1 minute
20 seconds
10
Less than a minute
0.125
5 minutes
12 minutes
5 minutes
Varied
5 minutes
5 min
5 Minutes
0.208333333
3 minutes
3 minutes
30 minutes
10 minutes
30 minutes
5 Minutes
30 seconds
10 minutes
5 minutes
4 minutes
45 seconds
3 seconds
11 minutes
15 minutes

3 minutes
20 minutes

20 seconds



2-3 minutes
10 minutes
6 seconds
30 secconds
1 hour
12 minutes
1.5 minutes
>2 minutes
30 seconds
5 seconds
~8 seconds
2 seconds
10 seconds
10 seconds
5 seconds
5 seconds
9:00-2:00
30 seconds
20
1 minute
3 seconds
5 seconds

10 minutes
25 seconds
7
25 minutes
20 minutes
30
1 hour

>30 minutes
2 hours
10 minutes
1 hour
15 minutes
5 seconds
15 seconds
3-4 seconds
5 seconds
15 seconds
4 minutes
60 seconds
Hours
20 minutes
Still occuring
3 hours
4 seconds
0.840277778
15 minutes
Ongoing
10 seconds
10 seconds
1 minute
15-20 minutes
5 minutes
1 hour
15 minutes
3 hours

10 minutes
4-5 seconds

5 minutes
4 minutes

3 hours
Seconds
10 minutes
10 minutes
3 seconds


~90 seconds
2 seconds MAX
2 minutes
1 hour +
5 minutes
5 minutes
5 minutes
15 seconds
3-4 minutes
2 minutes
40 minutes


30 seconds
ongoing


15 minutes
0.06875

5 minutes
1 second
10 minutes
10 seconds

15 minutes


4 minutes


1 minute
1 minute
3 minutes
1 minute
5 minutes
10 minutes
15 minutes
3 minutes
5 minutes
15-20 minutes
1 minute
Few minutes
20 minutes
15 minutes
30-60 seconds
5 minutes
15
10 minutes
10 seconds

5 minutes
5 minutes
1 minute

7 minutes
45 seconds
3 seconds
10-15 minutes
3
15 seconds
4 minutes
60 minutes +
1.25

5 minutes
0.125

 

[Alex Blakenburg]

Your data is that messy that I don't know how you are going to get a meaningful average out of it.

You are basically going to have to scan / filter the entire list and make judgement calls.

You could try something like this as a template. Where you only enter data into the 2 manual columns.
A number in the first and and S, M or H in the second.
I have not handled the "5-8 min" situation so these need to one of the things review and put details in the manual column.
XL2BB has a limit so I could only copy in a sample.

20210423 Convert Hrs Mins Secs.xlsx
	A	B	C	D	E	F	G	H	I
1
2	Original	Extract Before Space	Value	Measure	Manual Value	Manual Measure	Final Value	Final Measure	Convert to Secs
3	4 minutes	4	4	M			4	M	240
4	3 minutes	3	3	M			3	M	180
5		0	0				0		0
6	3 minutes	3	3	M			3	M	180
7	5 minutes	5	5	M			5	M	300
8	Driving	Driving							0
9	Seconds	Seconds							0
10	10 seconds	10	10	S			10	S	10
11	<1 minute	<1		M				M	0
12	3 minutes	3	3	M			3	M	180
13	3 minutes	3	3	M			3	M	180
14	3 minutes	3	3	M			3	M	180
15	5 min	5	5	M			5	M	300
16	5 minutes	5	5	M			5	M	300
17	3 minutes	3	3	M			3	M	180
18	3 minutes	3	3	M			3	M	180
19	3 minutes	3	3	M			3	M	180
20	30 seconds	30	30	S			30	S	30
21	30 seconds	30	30	S			30	S	30
22	1 minute	1	1	M			1	M	60
23	2 minutes	2	2	M			2	M	120
24	2 minutes	2	2	M			2	M	120
25	1 minute	1	1	M			1	M	60
26	2 minutes	2	2	M			2	M	120
27	20 minutes	20	20	M			20	M	1200
28		0	0				0		0
29	15 seconds	15	15	S			15	S	15
30		0	0				0		0
Data

Cell Formulas
Range	Formula
B3:B30	B3	=IFERROR(LEFT($A3,FIND(" ",$A3)),$A3)
C3:C30	C3	=IFERROR(VALUE(B3),"")
D3:D30	D3	=T(IFERROR(UPPER(MID($A3,FIND(" ",$A3)+1,1)),""))
G3:G30	G3	=IF(ISBLANK(E3),C3,E3)
H3:H30	H3	=UPPER(IF(ISBLANK(F3),D3,F3))
I3:I30	I3	=IFERROR(G3*IF(H3="H",60*60,IF(H3="M",60,IF(H3="S",1,0))),0)

Cells with Data Validation
Cell	Allow	Criteria
F3:F30	List	S,M,H,s,m,h

 

[JojoMaque]

Thank you so much, I will give this a try and let you know how I get on.

 

[JojoMaque]

Your data is that messy that I don't know how you are going to get a meaningful average out of it.

You are basically going to have to scan / filter the entire list and make judgement calls.

You could try something like this as a template. Where you only enter data into the 2 manual columns.
A number in the first and and S, M or H in the second.
I have not handled the "5-8 min" situation so these need to one of the things review and put details in the manual column.
XL2BB has a limit so I could only copy in a sample.

Your data is that messy that I don't know how you are going to get a meaningful average out of it.

You are basically going to have to scan / filter the entire list and make judgement calls.

You could try something like this as a template. Where you only enter data into the 2 manual columns.
A number in the first and and S, M or H in the second.
I have not handled the "5-8 min" situation so these need to one of the things review and put details in the manual column.
XL2BB has a limit so I could only copy in a sample.

20210423 Convert Hrs Mins Secs.xlsx
	A	B	C	D	E	F	G	H	I
1
2	Original	Extract Before Space	Value	Measure	Manual Value	Manual Measure	Final Value	Final Measure	Convert to Secs
3	4 minutes	4	4	M			4	M	240
4	3 minutes	3	3	M			3	M	180
5		0	0				0		0
6	3 minutes	3	3	M			3	M	180
7	5 minutes	5	5	M			5	M	300
8	Driving	Driving							0
9	Seconds	Seconds							0
10	10 seconds	10	10	S			10	S	10
11	<1 minute	<1		M				M	0
12	3 minutes	3	3	M			3	M	180
13	3 minutes	3	3	M			3	M	180
14	3 minutes	3	3	M			3	M	180
15	5 min	5	5	M			5	M	300
16	5 minutes	5	5	M			5	M	300
17	3 minutes	3	3	M			3	M	180
18	3 minutes	3	3	M			3	M	180
19	3 minutes	3	3	M			3	M	180
20	30 seconds	30	30	S			30	S	30
21	30 seconds	30	30	S			30	S	30
22	1 minute	1	1	M			1	M	60
23	2 minutes	2	2	M			2	M	120
24	2 minutes	2	2	M			2	M	120
25	1 minute	1	1	M			1	M	60
26	2 minutes	2	2	M			2	M	120
27	20 minutes	20	20	M			20	M	1200
28		0	0				0		0
29	15 seconds	15	15	S			15	S	15
30		0	0				0		0
Data

Cell Formulas
Range	Formula
B3:B30	B3	=IFERROR(LEFT($A3,FIND(" ",$A3)),$A3)
C3:C30	C3	=IFERROR(VALUE(B3),"")
D3:D30	D3	=T(IFERROR(UPPER(MID($A3,FIND(" ",$A3)+1,1)),""))
G3:G30	G3	=IF(ISBLANK(E3),C3,E3)
H3:H30	H3	=UPPER(IF(ISBLANK(F3),D3,F3))
I3:I30	I3	=IFERROR(G3*IF(H3="H",60*60,IF(H3="M",60,IF(H3="S",1,0))),0)

Cells with Data Validation
Cell	Allow	Criteria
F3:F30	List	S,M,H,s,m,h

Hi Alex, can you explain this formula =T(IFERROR(UPPER(MID($A3,FIND(" ",$A3)+1,1)),"")) how did you extrapolate the M,S,H?

thanks
Jo

 

[petertenthije]

This formula should get you on your way. It won't find every one, but only those that ONLY include HRS, HOU, MIN or SEC.

If none of the above, or more then one of the above, are found, then the formula will return an error.

This should cover you for around 75% of the list you gave.

=IFERROR(IF(SEARCH(""min"",RC[-1])>0,LEFT(RC[-1],SEARCH(""min"",RC[-1])-2)/(24*60),1),IFERROR(IF(SEARCH(""hrs"",RC[-1])>0,LEFT(RC[-1],SEARCH(""hrs"",RC[-1])-2)/(24),1),IFERROR(IF(SEARCH(""hou"",RC[-1])>0,LEFT(RC[-1],SEARCH(""hou"",RC[-1])-2)/(24),1),IF(SEARCH(""sec"",RC[-1])>0,LEFT(RC[-1],SEARCH(""Sec"",RC[-1])-2)/(24*60*60),1))))

 

[Alex Blakenburg]

thanks
Jo

You're Welcome.