transforming raw data to new time format

[gamuzadt]

This is the info i get from a report:

0d 5h 26m
0d 4h 57m
0d 4h 27m
0d 3h 57m

I need to generate a new collumn that shows 30 mins intervals (from 00:00 to 23:30) using cells that contain the infor as shown above. That is, Column C shows "Time" as shown above, and some how i want to have a column D showing to which interval does the column c cells belonfs to. Say, C1 shows 0d 5h 26m, then D1 should read 05:00 (because it belongs to 05:00-05:30 interval)

My initial attempts where to use Left/Right formula and using a different sheet where all intervals are located as reference. So i have one sheet named Data, where i paste the data i get from a report, and another one calls Reference where all intervals are shown.

I hope i was clear enough as to what im after, and i really appreciate any help you guys can provide.

best regards!

[darkyam]

There might be shorter ways to do this, but =TIME(MID(C1,FIND(" ",C1)+1,FIND("h",C1)-FIND(" ",C1)-1),INT(SUBSTITUTE(RIGHT(C1,3),"m","")/30)*30,0) works for me.

[NBVC]

Try:

=FLOOR(TIME(MID(A1,FIND(" ",A1)+1,FIND("h",A1)-FIND(" ",A1)-1)+0,MID(A1,FIND("h",A1)+2,FIND("m",A1)-FIND("h",A1)-2)+0,0)*24,0.5)/24

where A1 contains original info from report