Formula runs based on time

Hi All,

Can anyone help me regarding the formula. Ive tried setting the column D with a if statement C6>=now() as not to show any values on time that hasn't occured yet but somehow the formula i set still shows all values.

This is the formula ive placed.
=IF(C6<=NOW(),VLOOKUP(B6,raw!A:D,4,0),"")

Please help me out.



Please see attached sample template.
formula test.xlsx

First thing I noticed:

Originally Posted by OP

...a if statement C6>=now() as not...
...6>=now()...

Originally Posted by also OP

This is the formula ive placed.
=IF(C6<=NOW(),VLOOKUP(B6,raw!A:D,4,0),"")
...C6<=NOW()...

you have swapped the "greater than", to "less than".

Second thing I've noticed:

NOW() delivers DATE + TIME.
Time is a serial number from 0 to 1.
The date value is an integer indexed from 0, at Jan 1, 1900. Today (June 21 2012) is 41081.

Column C, schedule, has the date value implicitly 0-- Jan 1, 1900.

So, your DATE values are completely washing out your time values.

Use TODAY() to strip out the date like this:

=IF(C6 >= (NOW() - TODAY()), VLOOKUP(B6,raw!A:D,4,0), "")

Third thing I noticed:

The value of "WO" is treated as greater than the time. I dunno if that's correct or not but it's happening.

Hi Ben_Hensel,

Thanks for the help, It really did the trick.

But i think it should be "less than" because if we are to use "greater than, The log in time would not appear.

as for the "WO", I just used this formula IF(C6="WO","WO",IF(C6<=(NOW()-TODAY()),VLOOKUP(B6,raw!A:D,4,0),""))



Thanks for the help!!!

Just a follow up question:

What if I were to change the timezone in "time and date" settings of the computer.
Is there a way to always stick with Pacific time format?