Custom function to convert wk d m s format

Baelfur

New Member
Joined
Sep 3, 2014
Messages
3
Hello all. I have a report I am trying to do and the parsed data that I receive has times formatted in wk d h m s currently and I need to convert it to total hours:minutes:seconds in one column and total minutes:seconds in another column as shown below. I would prefer dropping the seconds all together if possible as well.

4wk 5d 20h 55m 15s

<tbody>
</tbody>

812:55:15
and
48720:15

Is there a function that will do this for me automatically? I have been trying to use text to columns to just split them all but then they are lined up goofy and I cannot figure out how to remove the lettering. Another thing that would work is if there is a way for excel to just go in and remove the wk d h m s lettering on the data and organize the output from right to left as shown below;

weeksdayshoursminutesseconds
45205515
4113853
725
21432328
171500

<tbody>
</tbody>


If I can strip the letters and text to column and have it output in right to left orientation I can just use =(((A2*7)+B2)*24)+C2 for the hours calculation and =((((A2*7)+B2)*24)+C2)*60 for the minutes respectively (I think).


Either solution would be awesome. I have about 40,000 + entries I have to run this on so doing it by hand is not going to happen :(

Thanks!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
Can you explain your dataset with cell references?

Is this one cell with four lines of data?

4wk 5d
20h
55m
15s
 

mjbeam

Well-known Member
Joined
Apr 6, 2002
Messages
756
Hours:

=CONCATENATE(MID(A1,1,FIND("wk",A1)-1)*168+MID(MID(A1,FIND("wk ",A1)+3,LEN(A1)),1,FIND("d ",MID(A1,FIND("wk ",A1)+3,LEN(A1)))-1)*24+MID(MID(A1,FIND("d ",A1)+2,LEN(A1)),1,FIND("h ",MID(A1,FIND("d ",A1)+2,LEN(A1)))-1),":",MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1))


Minutes:

=CONCATENATE(MID(A1,1,FIND("wk",A1)-1)*168*60+MID(MID(A1,FIND("wk ",A1)+3,LEN(A1)),1,FIND("d ",MID(A1,FIND("wk ",A1)+3,LEN(A1)))-1)*24*60+MID(MID(A1,FIND("d ",A1)+2,LEN(A1)),1,FIND("h ",MID(A1,FIND("d ",A1)+2,LEN(A1)))-1)*60+MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1))
 
Last edited:

DeezNuts

Board Regular
Joined
Aug 12, 2014
Messages
177
anychance you can post a sample of how the data is and a sample of how it should be so I practice on this? I would not be the one to provide a solution but I can try and learn something
 

Baelfur

New Member
Joined
Sep 3, 2014
Messages
3

ADVERTISEMENT

Hours:

=CONCATENATE(MID(A1,1,FIND("wk",A1)-1)*168+MID(MID(A1,FIND("wk ",A1)+3,LEN(A1)),1,FIND("d ",MID(A1,FIND("wk ",A1)+3,LEN(A1)))-1)*24+MID(MID(A1,FIND("d ",A1)+2,LEN(A1)),1,FIND("h ",MID(A1,FIND("d ",A1)+2,LEN(A1)))-1),":",MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1))


Minutes:

=CONCATENATE(MID(A1,1,FIND("wk",A1)-1)*168*60+MID(MID(A1,FIND("wk ",A1)+3,LEN(A1)),1,FIND("d ",MID(A1,FIND("wk ",A1)+3,LEN(A1)))-1)*24*60+MID(MID(A1,FIND("d ",A1)+2,LEN(A1)),1,FIND("h ",MID(A1,FIND("d ",A1)+2,LEN(A1)))-1)*60+MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1))


Here is a sample of the current excel output with Mjbeams solution. It looks like the function has trouble interpreting the data if it is missing some of the wk d h m s values and only calculates the ones with the full string. My apologies I should have clarified that.


7m 48s#VALUE!
1d 2h 9m 56s#VALUE!
22m 58s#VALUE!
7h 9m 59s#VALUE!
2m 49s#VALUE!
12m 17s#VALUE!
10h 46m 20s#VALUE!
2h 19m 51s#VALUE!
1d 3h 4m 15s#VALUE!
1d 12h 20m 23s#VALUE!
1wk 3d 12h 6m 14s252:6

<tbody>
</tbody>

Below is the requested sample what I have to work with and how I want the output displayed, with the extra column for minutes and in hh:mm converted format and mmmmm format;

Ticket timeConverted Ticket TimeTicket Minutes
7m 4800:077
1d 2h 9m 56s00:26131
22m 58s00:2222
7h 9m 59s07:09429
2m 49s00:022
12m 17s00:1212
10h 46m 20s10:46646
2h 19m 51s02:19139
1d 3h 4m 15s27:041624
1d 12h 20m 23s36:202180
1wk 3d 12h 6m 14s252:0615126

<tbody>
</tbody>
 
Last edited:

mjbeam

Well-known Member
Joined
Apr 6, 2002
Messages
756
Hours:

=CONCATENATE(IF(ISERR(FIND("wk",A1)),0,MID(A1,1,FIND("wk",A1)-1)*168)+IF(ISERR(FIND("wk",A1)),IF(ISERR(FIND("d ",A1)),0,MID(A1,1,FIND("d",A1)-1)*24),MID(MID(A1,FIND("wk ",A1)+3,LEN(A1)),1,FIND("d ",MID(A1,FIND("wk ",A1)+3,LEN(A1)))-1)*24)+IF(ISERR(FIND("d ",A1)),IF(ISERR(FIND("h ",A1)),0,MID(A1,1,FIND("h ",A1)-1)),MID(MID(A1,FIND("d ",A1)+2,LEN(A1)),1,FIND("h ",MID(A1,FIND("d ",A1)+2,LEN(A1)))-1)),":",IF(ISERR(FIND("h ",A1)),MID(A1,1,FIND("m ",A1)-1),MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1)))


Minutes:

=CONCATENATE(IF(ISERR(FIND("wk",A1)),0,MID(A1,1,FIND("wk",A1)-1)*168*60)+IF(ISERR(FIND("wk",A1)),IF(ISERR(FIND("d ",A1)),0,MID(A1,1,FIND("d",A1)-1)*24*60),MID(MID(A1,FIND("wk ",A1)+3,LEN(A1)),1,FIND("d ",MID(A1,FIND("wk ",A1)+3,LEN(A1)))-1)*24*60)+IF(ISERR(FIND("d ",A1)),IF(ISERR(FIND("h ",A1)),0,MID(A1,1,FIND("h ",A1)-1)*60),MID(MID(A1,FIND("d ",A1)+2,LEN(A1)),1,FIND("h ",MID(A1,FIND("d ",A1)+2,LEN(A1)))-1)*60)+IF(ISERR(FIND("h ",A1)),MID(A1,1,FIND("m ",A1)-1),MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1)))
 
Last edited:

mjbeam

Well-known Member
Joined
Apr 6, 2002
Messages
756

ADVERTISEMENT

Hours with leading zero in minutes:

=CONCATENATE(IF(ISERR(FIND("wk",A1)),0,MID(A1,1,FIND("wk",A1)-1)*168)+IF(ISERR(FIND("wk",A1)),IF(ISERR(FIND("d ",A1)),0,MID(A1,1,FIND("d",A1)-1)*24),MID(MID(A1,FIND("wk ",A1)+3,LEN(A1)),1,FIND("d ",MID(A1,FIND("wk ",A1)+3,LEN(A1)))-1)*24)+IF(ISERR(FIND("d ",A1)),IF(ISERR(FIND("h ",A1)),0,MID(A1,1,FIND("h ",A1)-1)),MID(MID(A1,FIND("d ",A1)+2,LEN(A1)),1,FIND("h ",MID(A1,FIND("d ",A1)+2,LEN(A1)))-1)),":",IF(LEN(IF(ISERR(FIND("h ",A1)),MID(A1,1,FIND("m ",A1)-1),MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1)))<2,CONCATENATE("0",IF(ISERR(FIND("h ",A1)),MID(A1,1,FIND("m ",A1)-1),MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1))),IF(ISERR(FIND("h ",A1)),MID(A1,1,FIND("m ",A1)-1),MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1))))
 

Baelfur

New Member
Joined
Sep 3, 2014
Messages
3
Hours with leading zero in minutes:

=CONCATENATE(IF(ISERR(FIND("wk",A1)),0,MID(A1,1,FIND("wk",A1)-1)*168)+IF(ISERR(FIND("wk",A1)),IF(ISERR(FIND("d ",A1)),0,MID(A1,1,FIND("d",A1)-1)*24),MID(MID(A1,FIND("wk ",A1)+3,LEN(A1)),1,FIND("d ",MID(A1,FIND("wk ",A1)+3,LEN(A1)))-1)*24)+IF(ISERR(FIND("d ",A1)),IF(ISERR(FIND("h ",A1)),0,MID(A1,1,FIND("h ",A1)-1)),MID(MID(A1,FIND("d ",A1)+2,LEN(A1)),1,FIND("h ",MID(A1,FIND("d ",A1)+2,LEN(A1)))-1)),":",IF(LEN(IF(ISERR(FIND("h ",A1)),MID(A1,1,FIND("m ",A1)-1),MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1)))<2,CONCATENATE("0",IF(ISERR(FIND("h ",A1)),MID(A1,1,FIND("m ",A1)-1),MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1))),IF(ISERR(FIND("h ",A1)),MID(A1,1,FIND("m ",A1)-1),MID(MID(A1,FIND("h ",A1)+2,LEN(A1)),1,FIND("m ",MID(A1,FIND("h ",A1)+2,LEN(A1)))-1))))


You just hit pay dirt sir, but its still procs a #VALUE! error exception if the seconds are the only field. From attempting to stumble through this amazing function, I'm guessing that I need to add the IF(ISERR(FIND("s",A1) in there somewhere? Or maybe a few somewheres?
 

mjbeam

Well-known Member
Joined
Apr 6, 2002
Messages
756
Yeah, that will make the function a lot longer though. Best bet if you are in a hurry would be to filter those entries and just paste a 0 where they are (rather than the formula). If I get some time I'll see if I can add that in but not sure that I will have time.
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
A short formula for your consideration:

in B2
=SUMPRODUCT(IFERROR(MID(" "&A2,FIND({"wk","d","h","m","s"}," "&A2)-2,2),0)*{604800,86400,3600,60,1})*(1/24/60/60)
Format the result as [h]:mm:ss or [h]:mm per your need.

Note:
It allows you to ignore the order of wk, d, h, m, s. e.g. it works for 1d 2m 10s; or 49s 9m 20h
However, it works for up to two digit numbers in the text string. i.e. in case you have 100wk or 100d, it doesn't work

[h]:mm:ss[h]:mm
7m 48s0:07:480:07
1d 2h 9m 56s26:09:5626:09
22m 58s0:22:580:22
7h 9m 59s7:09:597:09
2m 49s0:02:490:02
12m 17s0:12:170:12
10h 46m 20s10:46:2010:46
2h 19m 51s2:19:512:19
1d 3h 4m 15s27:04:1527:04
1d 12h 20m 23s36:20:2336:20
1wk 3d 12h 6m 14s252:06:14252:06

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

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
Top