time value format

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
444
Office Version
  1. 2019
hi all, i would like to ask how can i amend my issue.

A1 is used to enter a time to color B1:B100 by conditional formatting which is greater than A1
i've manual changed the format both are "hh:mm", while i always get stuck in column B with format "date time" like "8/10/2019 20:15", thus i cant get the answer.

thanks all
 
Still lost ...!
If A1 = 20:31 all cells in column B are colored correctly ...!
Please give detailed explanations
You don't answer to previous question:
What is wrong about B7 and B12
 
Last edited:
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Still lost ...!
If A1 = 20:31 all cells in column B are colored correctly ...!
Please give detailed explanations
You don't answer to previous question:
What is wrong about B7 and B12

maybe this can help?
i use your formula to try with "20:29, 20:30, 20:31", 20:31 get colored on 20:30 like H7, H12

7f542661cc
 
Upvote 0
Your display is now clear.
Finally, I got the same issue but refreshing the copy / paste of the format it clean up all issues: No explanation.
The only way to get it robust is to prepare manually the conditional format for each cell in column B and apply the format only for the cell concerned: Long and tedious
So the problem is not the formula but the way to apply it to the full column ...!
 
Upvote 0
Your display is now clear.
Finally, I got the same issue but refreshing the copy / paste of the format it clean up all issues: No explanation.
The only way to get it robust is to prepare manually the conditional format for each cell in column B and apply the format only for the cell concerned: Long and tedious
So the problem is not the formula but the way to apply it to the full column ...!

You could have a look on next link to see how to apply Conditional Format but you certainly already did it right
https://www.dummies.com/software/mi...cel-cells-based-on-the-value-of-another-cell/

thanks for the useful link and i really appreciate you helped a lot.
 
Upvote 0
In case you did not find a clear answer, here a macro to prepare a conditional format for each cell of the selection.
The cell reference is A1 and the fill color is colorIndex = 3

Code:
Sub Treat()
Dim Rg  As Range
Dim Add As String
Const RefAdd = "$A$1"           '  Here the cell in reference


    For Each Rg In Selection
        Add = Rg.Address
        Rg.FormatConditions.Delete
        Rg.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=IF((" & Add & "-INT(" & Add & "))>=(" & RefAdd & "-INT($A$1)),TRUE,FALSE)"
            
        Rg.FormatConditions(1).Interior.ColorIndex = 3          '  Here the color
    Next Rg
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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