Forum Discussion
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 entered in this format 13.30 (for 1:30 PM)
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
7 Replies
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_Eekelentrying to attach image again.
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.
- =ABS(A2-B2)*24 > 1
- Thank you for replying.
But, what I want is the third column shows the time taken (difference in hour, minute format (eg: 01:20) and if it is greater than one hour then it itself have to be shown in red color )
