Time in text string comparisons (Conditional Formatting)

[Stelio]

Hi Excel'ers,

A bit of a challenging one here which I can't seem to get my head around.
I have three columns with timeframes stored as text (columns Q, R and S).

I need to have conditional formatting in column S to satisfy the following conditions:
(IF Q is not blank AND timeframe in S>=Q) OR (IF R is not blank AND S>=R) then highlight S.

The issue I have been having is I had the below formula but this doesn't seem to work on the text string and throws up highlights where there shouldn't be.
I'm not a pro by the way so please enlighten me.

Q​	R​	S​
Response Missed By	Rectification Missed By	HD DELAY
2 days 1 hrs 37 mins
1 days 18 hrs 56 mins		0 days 0 hrs 18 mins
62 days 3 hrs 56 mins	57 days 4 hrs 26 mins	0 days 0 hrs 47 mins
1 days 17 hrs 11 mins
	5 days 20 hrs 29 mins
5 days 0 hrs 48 mins	0 days 1 hrs 57 mins	0 days 0 hrs 1 mins
1 days 20 hrs 28 mins
46 days 19 hrs 0 mins	41 days 19 hrs 45 mins	1 days 22 hrs 45 mins
0 days 16 hrs 30 mins
0 days 1 hrs 2 mins

 

[Stelio]

I forgot to paste the formula I was using in conditional formatting.
=OR(AND(LEN(Q2)>1,S2>=Q2),(AND(LEN(R2)>1,S2>=R2)))

 

[bebo021999]

With this sample, no cell in S expected to be hightlighted, right?
Could you show us 1 case of Q,R,S that S is highlighted?

 

[Stelio]

Hi bebo021999,

I've pasted below with comments in columns T and W. Hope this helps?

All the cells in column S are highlighted - some in error due to the formula I have pasted in my previous post not working correctly.
However, the last two rows are correct, and S should be highlighted.

​

​

Q​	R​	S​	T​	W​
Response Missed By	Rectification Missed By	HD DELAY
0 days 17 hrs 5 mins	0 days 13 hrs 9 mins		Error	S is not greater than either Q or R.
3 days 18 hrs 34 mins		0 days 2 hrs 0 mins	Error	S is not greater than or equal to Q.
	83 days 23 hrs 18 mins	0 days 0 hrs 27 mins	Error	S in not greater than or equal to R.
0 days 0 hrs 25 mins	0 days 0 hrs 26 mins	0 days 0 hrs 27 mins	Correct	S is greater than or equal to Q or R
	1 days 0 hrs 47 mins	1 day 0 hrs 55 min	Correct	S is greater than or equal to R

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Stelio]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

All sorted Peter. Thanks for the heads up.

 

[Peter_SSs]

Thanks for updating your details.

See how this goes.

23 03 02.xlsm
	Q	R	S
1	Response Missed By	Rectification Missed By	HD DELAY
2	0 days 17 hrs 5 mins	0 days 13 hrs 9 mins
3	3 days 18 hrs 34 mins		0 days 2 hrs 0 mins
4		83 days 23 hrs 18 mins	0 days 0 hrs 27 mins
5	0 days 0 hrs 25 mins	0 days 0 hrs 26 mins	0 days 0 hrs 27 mins
6		1 days 0 hrs 47 mins	1 day 0 hrs 55 min
CF Stelio

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S2:S6	Expression	=LET(sq,SEQUENCE(,3,,2),m,10^SEQUENCE(,3,4,-2),t,SUBSTITUTE(" "&TEXTJOIN(" ",,IF(Q2:S2="","0 d 0 h 0 m",Q2:S2))," ",REPT(" ",50)),Q,SUM(MID(t,sq*50,50)*m),R,SUM(MID(t,(sq+6)*50,50)*m),S,SUM(MID(t,(sq+12)*50,50)*m),OR(AND(Q>0,S>=Q),AND(R>0,S>=R)))	text	NO

 

[bebo021999]

This case is too complicate for formula solution.
If you need an UDF (formula via VBA code), call me back.

 

[Stelio]

Thanks for updating your details.

See how this goes.

23 03 02.xlsm
	Q	R	S
1	Response Missed By	Rectification Missed By	HD DELAY
2	0 days 17 hrs 5 mins	0 days 13 hrs 9 mins
3	3 days 18 hrs 34 mins		0 days 2 hrs 0 mins
4		83 days 23 hrs 18 mins	0 days 0 hrs 27 mins
5	0 days 0 hrs 25 mins	0 days 0 hrs 26 mins	0 days 0 hrs 27 mins
6		1 days 0 hrs 47 mins	1 day 0 hrs 55 min
CF Stelio

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S2:S6	Expression	=LET(sq,SEQUENCE(,3,,2),m,10^SEQUENCE(,3,4,-2),t,SUBSTITUTE(" "&TEXTJOIN(" ",,IF(Q2:S2="","0 d 0 h 0 m",Q2:S2))," ",REPT(" ",50)),Q,SUM(MID(t,sq*50,50)*m),R,SUM(MID(t,(sq+6)*50,50)*m),S,SUM(MID(t,(sq+12)*50,50)*m),OR(AND(Q>0,S>=Q),AND(R>0,S>=R)))	text	NO

Thanks Peter.

However, doesn't seem to highlight cell S for me. If I copy the formula and put it in an adjacent cell (T) instead of conditional formatting I get below.
Not sure what I'm doing wrong.

Q​	R​	S​	T​
	79 days 20 hrs 15 mins	80 days 0 hrs 29 mins	#VALUE!​

 

[Stelio]

This case is too complicate for formula solution.
If you need an UDF (formula via VBA code), call me back.

Thanks. I can put this into a macro so yes VBA can do too.