Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Subtracting Time Formula-Horse Racing | Excel Discussion (Misc queries) | |||
Subtracting time | Excel Worksheet Functions | |||
Subtracting time in date format | Excel Worksheet Functions | |||
Subtracting time in date format | Excel Worksheet Functions | |||
Subtracting time in date format | Excel Worksheet Functions |