Compare now() against two times

[FtnHills]

Hi,

I have a need to fill in a spreadsheet as time advances during the day every 15 minutes. So in column A, I simply created those time values (with a formatting of "time 13:30"), namely:

8:00
8:15
8:30
8:45

In an adjacent column, I wish to take action if I'm between those time values. My formula isn't working, it is

=IF(AND(NOW()>=A1,NOW()<A2),"Do 1","Do 2")

Can you tell me how to fix this?

Thanks

[NBVC]

If you want just the time, add MOD()

e.g.

=IF(AND(MOD(NOW(),1)>=A1,MOD(NOW(),1)<A2),"Do 1","Do 2")

[6StringJazzer]

Welcome to the Forum!

NBVC has answered your question but I thought I would add some explanation around it.

NOW() returns date + time. So for example, as I am typing this it returns

9/30/2011 14:43

and the underlying numeric value is 40816.613232. When you put in a time, you only get the decimal portion. For example, 08:00 has a value of 0.3333 (repeating) because it represents the fraction of a 24-hour day.

So to compare NOW() to a time value you have to lop off the interest portion from NOW().

As NBVC so concisely has done.

[FtnHills]

Wonderful, thanks NBVC, it works great.

Thanks too 6StringJazzer, for explaining the underlying time format and why it works.

[NBVC]

apologies for the conciseness

[zbor]

You can use =NOW()-TODAY() or =NOW()-INT(NOW()) for example

[NBVC]

Those should both also work.