Convert irregular text into hours

[thairicci]

Hi experts

I've got the following data output to work with.

---------------------

30 days 12 hours 18 minutes
1 day 1 hour 1 minute
45 minutes
6 hours 1 minute

---------------------

My goal is to convert this data into decimal hours.

As you can see, the data is irregular, sometimes there's only 1 number (i.e. NOT 06 hours).
Also the letters have different lenghth (day, days etc.)

I've tried to split the data up into cells for further processing but I'm getting the following problem

30
1
45
6

If there are no days, the hours jump into collum A and if there are no days and hours, the minutes jump in collum A.

I've tried to read out the cell with

=LEFT([@[*Time in Online Sessions]];2)*24

That works well, but how to handle the middle and the right part, if the length of numbers and text are varying?



Sorry for my bad english and thanks fpr your help.

[Phuocam]

Try this ...

=IFERROR(LEFT(A1,SEARCH("day",A1)-1),0)*24+IFERROR(LOOKUP(24,--MID(A1,SEARCH("hour",A1)-{1,2,3},{1,2,3})),0)+IFERROR(LOOKUP(60,--MID(A1,SEARCH("minute",A1)-{1,2,3},{1,2,3})),0)/60

[JBeaucaire]

I know one big monster formula can be constructed from all this, but this is easier to understand/analyze.

Assuming the first value is in A2:

B2: =IFERROR(LEFT(" "&$A2, SEARCH(B$1, " "&$A2)-1)+0, "")
C2: =IFERROR(MID(" "&$A2, SEARCH(C$1, " "&$A2)-3, 2)+0, "")
D2: =IFERROR(MID($A2, SEARCH(D$1, $A2)-3, 2)+0, "")

F2: =(N(B2)*24)
G2: =N(C2)
H2: =N(D2)/60
I2: =SUM(F2:H2)

Data Range

	A	B	C	D	E	F	G	H	I
1	Text	Day	Hour	Minute		DayHrs	Hrs	MinHrs	Dec.Hrs
2	30 days 12 hours 18 minutes	30	12	18		720	12	0.30	732.30
3	1 day 1 hour 1 minute	1	1	1		24	1	0.02	25.02
4	45 minutes			45		0	0	0.75	0.75
5	6 hours 1 minute		6	1		0	6	0.02	6.02
6	16 hours		16			0	16	0.00	16.00
7	1 hour 12 minutes		1	12		0	1	0.20	1.20
8	1 day 22 minutes	1		22		24	0	0.37	24.37

[thairicci]

you're awesome!

Thanks a lot to both of you!!!

[samba_ravi]

Please Login or Register  to view this content.

try this and copy towards down