Hi Excel Guru,
I have issue in taking results from following data.
I have planned headway of 00:03:30.
While time 1 in cell "A1" is 06:15:00 and time 2 in cell "A2" 06:18:30 and its changing in next cells. I am taking results in "B1" by formula "A2-A1".
I want conditional 3 formatings for the answer in "B1". I.e
1. If answer in "B1" is > 00:03:30 = format red
2. If answer in "B1" is < 00:03:30 = format Green
3. If answer in "B1" is = 00:03:30 = format yellow.
Please help me how is possible to format with accurate results.
Thanks in advance.
Note:I try i.e B1>Time(0,3,30) but not works fine
Nice question! Working with time brings always an extra thing with it, doesn't it?
Thought I figured it out, but something went wrong (something with the right " " in the right places must be the answer I think, or convert time first to value with a function). Sorry don't have time now, I'll come back later on this, but maybe somebody else can help you sooner....?
So I am obviously not the Guru.... 😉
Hi mey
Try comparing the difference in time (B1) with the headway (0:03:30) stored in a cell (e.g. C7 in my example attached).
Hope this helps.
Sunny
Thanks,Sunny
but condition equal to 0:03:30 not works
It works for me, Mey. If you change cell A2 to 6:18:00 the result in B1 is 0:03:30 and the conditional format is yellow fill.
Mynda
Hi Mynda and Sunny,
I Dont want to put 0:03:30 in Cell C7,i want to place it in conditional formatting rule,how i put to make it work,I try Time(0,3,30) but not work,any suggestion?
thanks
Try this.
You can even use (ignoring the TIME() function)
=TEXT($B$1,"hh:mm:ss")>"00:03:30"
=TEXT($B$1,"hh:mm:ss")="00:03:30"
=TEXT($B$1,"hh:mm:ss")<"00:03:30"
Sunny
Thanks,Sunny perfectly work
