Clean and format dates

J

JojoMaque

New Member
Joined
Apr 23, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
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
ABCDEFGHI
1
2OriginalExtract Before SpaceValueMeasureManual ValueManual MeasureFinal ValueFinal MeasureConvert to Secs
34 minutes44M4M240
43 minutes33M3M180
500 0 0
63 minutes33M3M180
75 minutes55M5M300
8DrivingDriving    0
9SecondsSeconds    0
1010 seconds1010S10S10
11<1 minute<1  M M0
123 minutes33M3M180
133 minutes33M3M180
143 minutes33M3M180
155 min55M5M300
165 minutes55M5M300
173 minutes33M3M180
183 minutes33M3M180
193 minutes33M3M180
2030 seconds3030S30S30
2130 seconds3030S30S30
221 minute11M1M60
232 minutes22M2M120
242 minutes22M2M120
251 minute11M1M60
262 minutes22M2M120
2720 minutes2020M20M1200
2800 0 0
2915 seconds1515S15S15
3000 0 0
Data
Cell Formulas
RangeFormula
B3:B30B3=IFERROR(LEFT($A3,FIND(" ",$A3)),$A3)
C3:C30C3=IFERROR(VALUE(B3),"")
D3:D30D3=T(IFERROR(UPPER(MID($A3,FIND(" ",$A3)+1,1)),""))
G3:G30G3=IF(ISBLANK(E3),C3,E3)
H3:H30H3=UPPER(IF(ISBLANK(F3),D3,F3))
I3:I30I3=IFERROR(G3*IF(H3="H",60*60,IF(H3="M",60,IF(H3="S",1,0))),0)
Cells with Data Validation
CellAllowCriteria
F3:F30ListS,M,H,s,m,h
 
Upvote 0
Solution
Thank you so much, I will give this a try and let you know how I get on.
 
Upvote 0
Alex Blakenburg said:
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.

Alex Blakenburg said:
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
ABCDEFGHI
1
2OriginalExtract Before SpaceValueMeasureManual ValueManual MeasureFinal ValueFinal MeasureConvert to Secs
34 minutes44M4M240
43 minutes33M3M180
500 0 0
63 minutes33M3M180
75 minutes55M5M300
8DrivingDriving    0
9SecondsSeconds    0
1010 seconds1010S10S10
11<1 minute<1  M M0
123 minutes33M3M180
133 minutes33M3M180
143 minutes33M3M180
155 min55M5M300
165 minutes55M5M300
173 minutes33M3M180
183 minutes33M3M180
193 minutes33M3M180
2030 seconds3030S30S30
2130 seconds3030S30S30
221 minute11M1M60
232 minutes22M2M120
242 minutes22M2M120
251 minute11M1M60
262 minutes22M2M120
2720 minutes2020M20M1200
2800 0 0
2915 seconds1515S15S15
3000 0 0
Data
Cell Formulas
RangeFormula
B3:B30B3=IFERROR(LEFT($A3,FIND(" ",$A3)),$A3)
C3:C30C3=IFERROR(VALUE(B3),"")
D3:D30D3=T(IFERROR(UPPER(MID($A3,FIND(" ",$A3)+1,1)),""))
G3:G30G3=IF(ISBLANK(E3),C3,E3)
H3:H30H3=UPPER(IF(ISBLANK(F3),D3,F3))
I3:I30I3=IFERROR(G3*IF(H3="H",60*60,IF(H3="M",60,IF(H3="S",1,0))),0)
Cells with Data Validation
CellAllowCriteria
F3:F30ListS,M,H,s,m,h
Click to expand...
Hi Alex, can you explain this formula =T(IFERROR(UPPER(MID($A3,FIND(" ",$A3)+1,1)),"")) how did you extrapolate the M,S,H?
Click to expand...
thanks
Jo
 
Upvote 0
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))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Elapsed day, hours, minutes minus weekends
Replies
6
Views
383
Scott Huish
Scott Huish
D
  • Solved
Highlighting cells with date/time greater than 29 minutes past the hour
Replies
6
Views
168
Peter_SSs
Peter_SSs
M
  • Question
conditional formatting a cell when it is within a certain time bracket
Replies
5
Views
119
Mr Marvin
M
M
  • Question
calculate minutes worked in the hour, by the hour
Replies
4
Views
350
bebo021999
B
J
  • Question
How To Calculate Hours Minutes Seconds
Replies
8
Views
254
jamiguel77
J

Forum statistics

Threads
1,214,559
Messages
6,120,208
Members
448,951
Latest member
jennlynn

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top