Time in text string comparisons (Conditional Formatting)

S

Stelio

New Member
Joined
Oct 8, 2015
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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 ByRectification Missed ByHD DELAY
2 days 1 hrs 37 mins
1 days 18 hrs 56 mins0 days 0 hrs 18 mins
62 days 3 hrs 56 mins57 days 4 hrs 26 mins0 days 0 hrs 47 mins
1 days 17 hrs 11 mins
5 days 20 hrs 29 mins
5 days 0 hrs 48 mins0 days 1 hrs 57 mins0 days 0 hrs 1 mins
1 days 20 hrs 28 mins
46 days 19 hrs 0 mins41 days 19 hrs 45 mins1 days 22 hrs 45 mins
0 days 16 hrs 30 mins
0 days 1 hrs 2 mins
 
Sort by date Sort by votes
Stelio said:
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.
Click to expand...
The formula works for me as shown below. It would help if you could give the sample data with XL2BB instead of as you have done above. That way we get a better copy of what you actually have. At this stage my guess is stray spaces somewhere that are not easily visible and/or characters that look like normal space characters but are something different. Again XL2BB should help figure that out.

In your sample in post 9, what happens if you go back to the column R and S values and re-type them over the top. Also select the column Q cell and hit Delete to ensure nothing in there. Does the formula in column T then change?

23 03 02.xlsm
QRST
779 days 20 hrs 15 mins80 days 0 hrs 29 minsTRUE
CF Stelio
Cell Formulas
RangeFormula
T7T7=LET(sq,SEQUENCE(,3,,2),m,10^SEQUENCE(,3,4,-2),t,SUBSTITUTE(" "&TEXTJOIN(" ",,IF(Q7:S7="","0 d 0 h 0 m",Q7:S7))," ",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)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S2:S7Expression=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)))textNO
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Stelio said:
Thanks. I can put this into a macro so yes VBA can do too.
Click to expand...
VBA solution:
Double Click into any cell in column S, this code will loop through each row, get the total hour# of each cell in Q,R,S , if S> Q or R then hightlight with yellow and put the time consumtion in to column U.
How: Right click on tab'name, view Code, then paste below code into (worksheet module)
VBA Code: 
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lr&, c As Range, c3 As Range, s, h As Double, dur As Double
lr = Cells(Rows.Count, "S").End(xlUp).Row
If Intersect(Target, Range("S2:S" & lr)) Is Nothing Then Exit Sub
Range("S1:S10000").Interior.Color = xlNone
Range("U2:U" & lr).ClearContents  ' remove this line if do not want to paste value into column U
For Each c3 In Range("S2:S" & lr)
    If Not IsEmpty(c3) Then
        s = Split(c3)
        h = CDbl(s(0)) + CDbl(s(2)) / 24 + CDbl(s(4)) / 1440
        For Each c In c3.Offset(0, -2).Resize(1, 2)
            If Not IsEmpty(c) Then
                s = Split(c)
                dur = h - (CDbl(s(0)) + CDbl(s(2)) / 24 + CDbl(s(4)) / 1440)
                If dur > 0 Then
                    c3.Offset(0, 2).Value = dur ' remove this line if do not want to paste value into column U
                    c3.Interior.Color = vbYellow
                    Exit For
                End If
            End If
        Next
    End If
Next
Cancel = True
End Sub

Capture.JPG
 
Upvote 0
I have mapped the data and converted texts to dates, I have applied CF to the mapped data.

The mapped data is below your data in the below example:
Book1
QRS
1Response Missed ByRectification Missed ByHD DELAY
20 days 17 hrs 5 mins0 days 13 hrs 9 mins
33 days 18 hrs 34 mins0 days 2 hrs 0 mins
483 days 23 hrs 18 mins0 days 0 hrs 27 mins
50 days 0 hrs 25 mins0 days 0 hrs 26 mins0 days 0 hrs 27 mins
61 days 0 hrs 47 mins1 day 0 hrs 55 min
7
8
9
10Response Missed ByRectification Missed ByHD DELAY
1100/01/1900 17:0500/01/1900 13:09
1203/01/1900 18:3400/01/1900 02:00
1323/03/1900 23:1800/01/1900 00:27
1400/01/1900 00:2500/01/1900 00:2600/01/1900 00:27
1501/01/1900 00:4701/01/1900 00:55
Sheet1
Cell Formulas
RangeFormula
Q11:S15Q11=IFERROR(MAP(Q2:S6,LAMBDA(x,DATE(1900,1,TEXTBEFORE(x," "))+TIME(TEXTAFTER(TEXTBEFORE(x," ",3)," ",2),TEXTBEFORE(TEXTAFTER(x," ",4)," "),0))),"")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S11:S15Expression=IF($S11="",FALSE,IF(OR(AND($Q11>0,$S11>=$Q11),AND($R11>0,$S11>=$R11)),TRUE,FALSE))textNO


I did try to create a CF formula from the above but i have only just learned that LAMBDA is not supported in CF
 
Last edited:
Upvote 0
Peter_SSs said:
Thanks for updating your details. (y)

See how this goes.

23 03 02.xlsm
QRS
1Response Missed ByRectification Missed ByHD DELAY
20 days 17 hrs 5 mins0 days 13 hrs 9 mins
33 days 18 hrs 34 mins0 days 2 hrs 0 mins
483 days 23 hrs 18 mins0 days 0 hrs 27 mins
50 days 0 hrs 25 mins0 days 0 hrs 26 mins0 days 0 hrs 27 mins
61 days 0 hrs 47 mins1 day 0 hrs 55 min
CF Stelio
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S2:S6Expression=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)))textNO
Click to expand...
Hi Peter. I did a trim on all my cells and found that the formula started kinda working but still giving me incorrect TRUE and FALSE readings.

Mini Sheet below:

Book1.xlsx
ABCDEFGHIJKLMNOPQRS
1Response Missed ByRectification Missed ByHD DELAY
22 days 1 hrs 37 mins
379 days 20 hrs 15 mins0 days 0 hrs 29 mins
41 days 18 hrs 56 mins0 days 0 hrs 18 mins
562 days 3 hrs 56 mins57 days 4 hrs 26 mins0 days 0 hrs 47 mins
61 days 17 hrs 11 mins
75 days 20 hrs 29 mins
85 days 0 hrs 48 mins0 days 1 hrs 57 mins0 days 0 hrs 1 mins
91 days 20 hrs 28 mins
1046 days 19 hrs 0 mins41 days 19 hrs 45 mins1 days 22 hrs 45 mins
110 days 16 hrs 30 mins
120 days 1 hrs 2 mins
130 days 17 hrs 5 mins0 days 13 hrs 9 mins
141 days 0 hrs 12 mins0 days 21 hrs 52 mins1 days 2 hrs 49 mins
158 days 1 hrs 2 mins3 days 1 hrs 37 mins0 days 2 hrs 22 mins
160 days 0 hrs 33 mins
170 days 1 hrs 15 mins3 days 21 hrs 30 mins
181 days 8 hrs 42 mins0 days 0 hrs 7 mins
193 days 23 hrs 59 mins0 days 0 hrs 7 mins
203 days 21 hrs 40 mins
210 days 17 hrs 45 mins
224 days 21 hrs 32 mins0 days 1 hrs 13 mins
230 days 11 hrs 48 mins0 days 9 hrs 18 mins0 days 0 hrs 4 mins
240 days 15 hrs 10 mins
250 days 5 hrs 9 mins0 days 0 hrs 4 mins
263 days 18 hrs 34 mins0 days 2 hrs 0 mins
270 days 17 hrs 15 mins0 days 9 hrs 47 mins
285 days 23 hrs 24 mins3 days 0 hrs 24 mins0 days 0 hrs 1 mins
298 days 3 hrs 4 mins30 days 21 hrs 19 mins0 days 0 hrs 1 mins
3016 days 19 hrs 41 mins11 days 22 hrs 11 mins
311 days 0 hrs 52 mins0 days 0 hrs 2 mins
322 days 16 hrs 26 mins0 days 1 hrs 3 mins
333 days 16 hrs 0 mins
3411 days 15 hrs 25 mins
3519 days 20 hrs 45 mins0 days 0 hrs 3 mins
362 days 18 hrs 40 mins0 days 7 hrs 2 mins
373 days 5 hrs 30 mins0 days 6 hrs 10 mins0 days 0 hrs 10 mins
CompletedLate (Confirm)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S2:S1132Expression=LET(sq,SEQUENCE(,3,,2),m,10^SEQUENCE(,3,4,-2),t,SUBSTITUTE(" "&TEXTJOIN(" ",,IF(Q3:S3="","0 d 0 h 0 m",Q3:S3))," ",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)))textNO
 
Upvote 0
Stelio said:
Mini Sheet below:
Click to expand...
Thanks for that. Much easier. :)

Stelio said:
.. the formula started kinda working but still giving me incorrect TRUE and FALSE readings.
Click to expand...
That is because you have correctly started the conditional formatting from row 2, but the formula refers to row 3. ;)

1677754374648.png


Go back into the conditional formatting and change both Q3:S3 to Q2:S2 and Apply

Stelio.xlsm
QRS
1Response Missed ByRectification Missed ByHD DELAY
22 days 1 hrs 37 mins
379 days 20 hrs 15 mins0 days 0 hrs 29 mins
41 days 18 hrs 56 mins0 days 0 hrs 18 mins
562 days 3 hrs 56 mins57 days 4 hrs 26 mins0 days 0 hrs 47 mins
61 days 17 hrs 11 mins
75 days 20 hrs 29 mins
85 days 0 hrs 48 mins0 days 1 hrs 57 mins0 days 0 hrs 1 mins
91 days 20 hrs 28 mins
1046 days 19 hrs 0 mins41 days 19 hrs 45 mins1 days 22 hrs 45 mins
110 days 16 hrs 30 mins
120 days 1 hrs 2 mins
130 days 17 hrs 5 mins0 days 13 hrs 9 mins
141 days 0 hrs 12 mins0 days 21 hrs 52 mins1 days 2 hrs 49 mins
158 days 1 hrs 2 mins3 days 1 hrs 37 mins0 days 2 hrs 22 mins
160 days 0 hrs 33 mins
170 days 1 hrs 15 mins3 days 21 hrs 30 mins
181 days 8 hrs 42 mins0 days 0 hrs 7 mins
193 days 23 hrs 59 mins0 days 0 hrs 7 mins
203 days 21 hrs 40 mins
210 days 17 hrs 45 mins
224 days 21 hrs 32 mins0 days 1 hrs 13 mins
230 days 11 hrs 48 mins0 days 9 hrs 18 mins0 days 0 hrs 4 mins
240 days 15 hrs 10 mins
250 days 5 hrs 9 mins0 days 0 hrs 4 mins
263 days 18 hrs 34 mins0 days 2 hrs 0 mins
270 days 17 hrs 15 mins0 days 9 hrs 47 mins
285 days 23 hrs 24 mins3 days 0 hrs 24 mins0 days 0 hrs 1 mins
298 days 3 hrs 4 mins30 days 21 hrs 19 mins0 days 0 hrs 1 mins
3016 days 19 hrs 41 mins11 days 22 hrs 11 mins
311 days 0 hrs 52 mins0 days 0 hrs 2 mins
322 days 16 hrs 26 mins0 days 1 hrs 3 mins
333 days 16 hrs 0 mins
3411 days 15 hrs 25 mins
3519 days 20 hrs 45 mins0 days 0 hrs 3 mins
362 days 18 hrs 40 mins0 days 7 hrs 2 mins
373 days 5 hrs 30 mins0 days 6 hrs 10 mins0 days 0 hrs 10 mins
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S2:S37Expression=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)))textNO
 
Last edited:
Upvote 0
Peter_SSs said:
Thanks for that. Much easier. :)


That is because you have correctly started the conditional formatting from row 2, but the formula refers to row 3. ;)

View attachment 86591

Go back into the conditional formatting and change both Q3:S3 to Q2:S2 and Apply

Stelio.xlsm
QRS
1Response Missed ByRectification Missed ByHD DELAY
22 days 1 hrs 37 mins
379 days 20 hrs 15 mins0 days 0 hrs 29 mins
41 days 18 hrs 56 mins0 days 0 hrs 18 mins
562 days 3 hrs 56 mins57 days 4 hrs 26 mins0 days 0 hrs 47 mins
61 days 17 hrs 11 mins
75 days 20 hrs 29 mins
85 days 0 hrs 48 mins0 days 1 hrs 57 mins0 days 0 hrs 1 mins
91 days 20 hrs 28 mins
1046 days 19 hrs 0 mins41 days 19 hrs 45 mins1 days 22 hrs 45 mins
110 days 16 hrs 30 mins
120 days 1 hrs 2 mins
130 days 17 hrs 5 mins0 days 13 hrs 9 mins
141 days 0 hrs 12 mins0 days 21 hrs 52 mins1 days 2 hrs 49 mins
158 days 1 hrs 2 mins3 days 1 hrs 37 mins0 days 2 hrs 22 mins
160 days 0 hrs 33 mins
170 days 1 hrs 15 mins3 days 21 hrs 30 mins
181 days 8 hrs 42 mins0 days 0 hrs 7 mins
193 days 23 hrs 59 mins0 days 0 hrs 7 mins
203 days 21 hrs 40 mins
210 days 17 hrs 45 mins
224 days 21 hrs 32 mins0 days 1 hrs 13 mins
230 days 11 hrs 48 mins0 days 9 hrs 18 mins0 days 0 hrs 4 mins
240 days 15 hrs 10 mins
250 days 5 hrs 9 mins0 days 0 hrs 4 mins
263 days 18 hrs 34 mins0 days 2 hrs 0 mins
270 days 17 hrs 15 mins0 days 9 hrs 47 mins
285 days 23 hrs 24 mins3 days 0 hrs 24 mins0 days 0 hrs 1 mins
298 days 3 hrs 4 mins30 days 21 hrs 19 mins0 days 0 hrs 1 mins
3016 days 19 hrs 41 mins11 days 22 hrs 11 mins
311 days 0 hrs 52 mins0 days 0 hrs 2 mins
322 days 16 hrs 26 mins0 days 1 hrs 3 mins
333 days 16 hrs 0 mins
3411 days 15 hrs 25 mins
3519 days 20 hrs 45 mins0 days 0 hrs 3 mins
362 days 18 hrs 40 mins0 days 7 hrs 2 mins
373 days 5 hrs 30 mins0 days 6 hrs 10 mins0 days 0 hrs 10 mins
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S2:S37Expression=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)))textNO
Click to expand...
Thanks Peter! It works like a charm. Appreciate all the help. I had square eyes but finally got there.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
How do I store a battery's state of charge past midnight?
Replies
3
Views
247
NdNoviceHlp
NdNoviceHlp
john Smith72
  • Solved
count days between dates for each month by drop list
Replies
9
Views
375
Tetra201
Tetra201
C
  • Question Question
ODD DATE & TIME (FORMAT) CALCULATION
Replies
8
Views
1K
Tetra201
Tetra201
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
H
  • Solved
Time and If Functions / Conditional Formatting
Replies
4
Views
525
vladimiratanasiu
V

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top