Another Unique Time Conversion Question

[Lilpanik]

I have data that I would like to see in seconds or minutes but the data I receive can come out many ways. For Example in Column D Row 2 it could read (5 hr 3 min) but row 3 could read (6 day 17 hr) and row 4 could read (21 min 58 sec). I would prefer to see this data in second so if it read (5 hr 3 min) the conversion formula would read 18180.

Any help would be greatly appreciated.

 

[mole999]

what are ALL the variations of times

my first thought would be VBA

check if first word in cell is hr do one thing, if day, do another, if min do another, eliminating text could be done, but establishing the value would be interesting

 

[Lilpanik]

The Variation of times will always return 2 parts and the second part will always be the next time set down. For Example, If the first says hr, the second will always be min. If the first part is day, the second will be hr. And if the first part is min, the second part will be sec. Those are actually the three variations.

 

[Lilpanik]

I have actually had someone right a similar formula for me in the past, however it doesn't account for seconds and it is very hard for me to read. The formula is below.

=IFERROR(IF(LEN(D3)<=6,LEFT(D3,FIND(" ",D3)-1)*IF(TRIM(RIGHT(D3,3))="Day",24*60,IF(TRIM(RIGHT(D3,3))="Hr",60,1)),IF(LEN(D3)<=15,LEFT(D3,FIND(" ",D3)-1)*IF(MID(MID(MID(SUBSTITUTE(D3," ","^",1),1,256),FIND("^",SUBSTITUTE(D3," ","^",1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(D3," ","^",1),1,256),FIND("^",SUBSTITUTE(D3," ","^",1)),256))-2)="Day",24*60,IF(MID(MID(MID(SUBSTITUTE(D3," ","^",1),1,256),FIND("^",SUBSTITUTE(D3," ","^",1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(D3," ","^",1),1,256),FIND("^",SUBSTITUTE(D3," ","^",1)),256))-2)="Hr",60,1))+MID(MID(MID(SUBSTITUTE(D3," ","^",2),1,256),FIND("^",SUBSTITUTE(D3," ","^",2)),256),2,FIND(" ",MID(MID(SUBSTITUTE(D3," ","^",2),1,256),FIND("^",SUBSTITUTE(D3," ","^",2)),256))-2)*IF(TRIM(RIGHT(D3,3))="Day",24*60,IF(TRIM(RIGHT(D3,3))="Hr",60,1)),(LEFT(D3,FIND(" ",D3)-1)*24*60+MID(MID(MID(SUBSTITUTE(D3," ","^",2),1,256),FIND("^",SUBSTITUTE(D3," ","^",2)),256),2,FIND(" ",MID(MID(SUBSTITUTE(D3," ","^",2),1,256),FIND("^",SUBSTITUTE(D3," ","^",2)),256))-2)*60+MID(MID(MID(SUBSTITUTE(D3," ","^",4),1,256),FIND("^",SUBSTITUTE(D3," ","^",4)),256),2,FIND(" ",MID(MID(SUBSTITUTE(D3," ","^",4),1,256),FIND("^",SUBSTITUTE(D3," ","^",4)),256))-2)))),0)