# Custom function to convert wk d m s format

#### Baelfur

##### New Member
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;

 weeks days hours minutes seconds 4 5 20 55 15 4 11 38 53 7 25 2 14 3 23 28 17 15 00

<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
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
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
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

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 14s 252: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 time Converted Ticket Time Ticket Minutes 7m 48 00:07 7 1d 2h 9m 56s 00:26 131 22m 58s 00:22 22 7h 9m 59s 07:09 429 2m 49s 00:02 2 12m 17s 00:12 12 10h 46m 20s 10:46 646 2h 19m 51s 02:19 139 1d 3h 4m 15s 27:04 1624 1d 12h 20m 23s 36:20 2180 1wk 3d 12h 6m 14s 252:06 15126

<tbody>
</tbody>

Last edited:

#### mjbeam

##### Well-known Member
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:
• Baelfur

#### mjbeam

##### Well-known Member

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

#### Baelfur

##### New Member
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
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
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 48s 0:07:48 0:07 1d 2h 9m 56s 26:09:56 26:09 22m 58s 0:22:58 0:22 7h 9m 59s 7:09:59 7:09 2m 49s 0:02:49 0:02 12m 17s 0:12:17 0:12 10h 46m 20s 10:46:20 10:46 2h 19m 51s 2:19:51 2:19 1d 3h 4m 15s 27:04:15 27:04 1d 12h 20m 23s 36:20:23 36:20 1wk 3d 12h 6m 14s 252:06:14 252:06

<tbody>
</tbody>

Last edited:

Replies
3
Views
141
Replies
5
Views
116
Replies
7
Views
122
Replies
15
Views
92
Replies
45
Views
727

### Forum statistics

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.

### Which adblocker are you using?    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

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