Subtracting Time from NOW() Value

  • Thread starter Trying to excel in life but need help
  • Start date
T

Trying to excel in life but need help

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
 
J

JE McGimpsey

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.




"Trying to excel in life but need help"
 
M

macropod

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"
 
Top