Forum Discussion

Copper Contributor

Jun 18, 2024

Solved

conditional formating with time (hours)

I have three columns, first one with break time out and second with break time in. The third have to show the difference and if it is grater than one hour it has to show in red text. times are entere...

Excel for web

Riny_van_Eekelen
Jun 18, 2024
Denjg I'd recommend you to enter the times with a colon rather than a decimal point. That turns them into real time values without the need to convert them first. Then it will look like this:
with conditional formatting in Excel for the Web:
Note that 1 hour is represented by the numerical value of 1 / 24 = 0.04166667

Riny_van_Eekelen

Platinum Contributor

Jun 18, 2024

Denjg I'd recommend you to enter the times with a colon rather than a decimal point. That turns them into real time values without the need to convert them first. Then it will look like this:

with conditional formatting in Excel for the Web:

Note that 1 hour is represented by the numerical value of 1 / 24 = 0.04166667

Denjg
Copper Contributor
Jun 19, 2024
Riny_van_Eekelentrying to attach image again.
- SergeiBaklan
  Diamond Contributor
  Jun 19, 2024
  Denjg
  
  Any text is "greater than" any number. You need to ignore cells with empty strings.
  - Denjg
    Copper Contributor
    Jun 20, 2024
    Thank you very much.
Denjg
Copper Contributor
Jun 19, 2024
Thank you very much.
Actually I had a further requirement, I was checking either 'Out' time or 'In' time is entered in column A and B respectively and shows the 'C' column as blank. For this I am using the formula like this:
=IF(OR(A2="",B2=""),"",B2-A2)
And formatting the 'C' column with red if the value is greater than 1:00 hour. The problem I am facing is that the blank column is also getting formatted. Can you please help me to tweak what I am doing to get it correct (avoid formatting on blank values in 'C' column)
I am attaching an image to show what I am talking about.

Sorry I am not able to insert Image.