Converting a text and number time into a numerical value and converting to minutes

Hi,

I am a very novice excel user and I am trying to solve I think a fairly complex formula.

I am creating a spread sheet from data that is extracted from another program. That program creates a cell for total usage time in the following format: eg. "2d 8h 35m" some will not have the days or hours depending on the amount of time in use "20h 6m" or "45m".

I have approx. 67 columns with the usage times. I would like to get a total at the end.

I have been trying to create a formula that will remove the letter and then convert the "days, hours and min" to straight minutes or one that will add all the cells and move excess min to hours and hours to days.....

I am not even sure where to start. Best I have managed so far is to remove one letter using the substitute formula. I have tried searching the web for help but that seems futile.....

Any suggestions or help would be appreciated.

Please read the yellow banner at the top of this page on how to attach a file.

Once we get a sample file, we can show you the solution.

	A	B	C	D	E	F	G	H
1		as text			as numbers
2		days	hours	minutes	days	hours	minutes	IN MINUTES
3	2d 18h 45m	2d	18h	45m	2	18	45	66:45
4	20h 35m	0	20h	35m	0	20	35	20:35
5	17m	0	0	17m	0	0	17	0:17
6	17d 23h 59m	17d	23h	59m	17	23	59	431:59
7	3h 55m	0	3h	55m	0	3	55	3:55

It is 1 AM, so forgive me some monster formula:

B3:

Please Login or Register  to view this content.

C3:

Please Login or Register  to view this content.

D3:

Please Login or Register  to view this content.

and the rest

	E	F	G	H
1	as numbers
2	days	hours	minutes	IN MINUTES
3	=IFERROR(LEFT(B3,FIND("d",B3)-1),0)	=IFERROR(LEFT(C3,FIND("h",C3)-1),0)	=IFERROR(LEFT(D3,FIND("m",D3)-1),0)	=E2+F2/24+G2/1440

I am sure that some magician make it much, much simpler.

With text in A2
First, try to combine text with "d,h,m" to ensure all d,h,m exist
"000"&A2&"000d000h000m"
then for "d" calculation

AGGREGATE(14,6,RIGHT(LEFT("000"&A2&"000d000h000m",SEARCH("d","000"&A2&"000d000h000m")-1),{1,2,3})+0,1)

similar to "h" and "m", then sum them up:

Please Login or Register  to view this content.

Please try

=SUMPRODUCT((TEXT(MID(0&A2,FIND({"d","h","m"},0&A2&"dhm")-2,2),"0;;;\0")/{1,24,1440}))