Subtracting Time from NOW() Value
 

Hello,

Thanks for all the assistance so far. You have all been very helpful and it
is greatly appreciated.

I am trying to have a cell return a "" value when the clock reached a
certain time.
I have the formula working but it does not work past midnight.

$B$8 formula is: NOW()
=IF($B$8=TODAY()+$B11,"",'M-F'!L9)

This works only up to 23:59 and then returns the value in 'M-F'!L9. I want
to return the "" value.

Can anyone help?

Thanks
Martin

What is the value in B11? And what exactly are you trying to do?

XL stores dates as integer offsets from a base date, and times as
fractional days.

At midnight, TODAY() and NOW() are equal - they're the integer that
represents the number of days since the system base date.

For instance, on 10 April 2005, TODAY() returns 38452. At 00:00 on 10
April 2005, NOW() also returned 38452 (using the 1900 date system). At
3:00 am, NOW returns 38452 plus the fraction of the day that has passed,
e.g, 38452.125. At noon, NOW() returns 38452.5.

So if B11 contains a value 0, then at midnight of any day,

NOW()=TODAY()+$B11

will return FALSE, and the IF() statement will return 'M-F'!L9. That
conditional will continue to return FALSE until the fraction of the day
represented by $B11 has passed. If $B11 is = 1, then the conditional
will never be TRUE.




In article ,
"Trying to excel in life but need help"
oft.com wrote:

Thanks for all the assistance so far. You have all been very helpful and it
is greatly appreciated.

I am trying to have a cell return a "" value when the clock reached a
certain time.
I have the formula working but it does not work past midnight.

$B$8 formula is: NOW()
=IF($B$8=TODAY()+$B11,"",'M-F'!L9)

This works only up to 23:59 and then returns the value in 'M-F'!L9. I want
to return the "" value.

Hi Martin,

Obviously, once you pass midnight, both the NOW() and TODAY() functions
refer to a new day. So NOW() resets to 0 and TODAY() increments by one.
Thus, your test can never return a time value greater than 23:59:59.

Cheers


"Trying to excel in life but need help"
oft.com wrote in message
...
Hello,

Thanks for all the assistance so far. You have all been very helpful and
it
is greatly appreciated.

I am trying to have a cell return a "" value when the clock reached a
certain time.
I have the formula working but it does not work past midnight.

$B$8 formula is: NOW()
=IF($B$8=TODAY()+$B11,"",'M-F'!L9)

This works only up to 23:59 and then returns the value in 'M-F'!L9. I want
to return the "" value.

Can anyone help?

Thanks
Martin