Convert text of 0h 0m 0s to actual time data....

[coolkev99]

Hi all, I have a two fold challenge I'm trying to solve, and I would greatly prefer not to have to use VBA script/macro...

Challenge #1) I get time data sent daily (example: 1h 8m 3s). Yes, the time data has text with the "h" "m" and "s", but only if applicable. The goal is to try to get this data down to minutes ONLY. The problem is, if the time is less that an hour it will not have the "h".(Example 8m 3s), and again, if the time data is less than a minute the time data will only have the time in seconds only. So for example it would NOT be sent like "0h 0m 8s", it would just be "8s". I originally attempted to use the following formula "=ROUNDDOWN(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"m",":"),"s",""))*1440,0)" But like I mentioned, this only works if all elements of "h" "m" and "s" are there. Just to make it even more weird, if the time data is like 1 hour flat, it WILL have the "m" and the "s" appearing. (example 1h 0m 0s)

Challenge #2) The time data I get is every half-hour, but I would like to have excel (if possible) find the highest time reported within that hour period (or pair).

So for example if:
1pm-1:30pm = 1h 10m 55s
1:30pm-2pm = 35m 0s

Then excel would compare these and report in another cell in rounded down minutes:
1pm-2pm = 70

PLEASE see image as it provides a better example of what I'm trying to build/how I'd like it to look.

 

[BSALV]

Roykana.xlsb
	A	B
1	1h 6m 20s	01:06:20
Blad1

Cell Formulas
Range	Formula
B1	B1	=TIME(IFERROR(MID("00"&A1,SEARCH("h","00"&A1)-2,2),0),IFERROR(MID("00"&A1,SEARCH("m","00"&A1)-2,2),0),IFERROR(MID("00"&A1,SEARCH("s","00"&A1)-2,2),0))

 

[BSALV]

the 2nd table is 50 rows !

Roykana.xlsb
	A	B	C	D	E	F	G	H
1	time	time2	time reported		start	highest	hour max
2	1h 6m 20s	01:06:20	00:05:00			00:07:00	00:07:00
3	6m 20s	00:06:20	00:06:00		00:30		-
4	20s	00:00:20	00:07:00		01:00	00:08:00	00:08:00
5	1h	01:00:00	00:08:00		01:30		-
6					02:00
7					02:30		-
8					03:00
Blad1

Cell Formulas
Range	Formula
E2:E8	E2	=TIME(0,(ROW()-ROW($E$1)-1)*30,0)
F2:F8	F2	=MAXIFS(Tabel3[time reported],Tabel3[time2],">="&[@start],Tabel3[time2],"<"&E3)
G2:G8	G2	=IF(MINUTE([@start])=0,MAX(F2:F3),"-")
B2:B7	B2	=TIME(IFERROR(MID("00"&A2,SEARCH("h","00"&A2)-2,2),0),IFERROR(MID("00"&A2,SEARCH("m","00"&A2)-2,2),0),IFERROR(MID("00"&A2,SEARCH("s","00"&A2)-2,2),0))

 

[coolkev99]

THANKS! The formula for the first challenge works perfect. I think maybe I didn't explain my second challenge well as I didn't understand the solution to that one. But I figured it out using MAX function. I also added a check so it would report "no data" if both cells were empty.

Formula below would compare two time entries in row (A1 and A2) time and provide higher result rounded down to nearest minute:

=IF(AND(A1="",A2=""),"no data",MAX(ROUNDDOWN(TIME(IFERROR(MID("00"&A1,SEARCH("h","00"&A1)-2,2),0),IFERROR(MID("00"&A1,SEARCH("m","00"&A1)-2,2),0),IFERROR(MID("00"&A1,SEARCH("s","00"&A1)-2,2),0))*60*24,0),(ROUNDDOWN(TIME(IFERROR(MID("00"&A2,SEARCH("h","00"&A2)-2,2),0),IFERROR(MID("00"&A2,SEARCH("m","00"&A2)-2,2),0),IFERROR(MID("00"&A2,SEARCH("s","00"&A2)-2,2),0))*60*24,0))))