Usage of the NOW() function.

[Guest]

Hi,

I'm trying to fiigure out the possible causes for the following 2 problems.
Any help is highly appreciated.

#1.
I'm trying to use the NOW() function in a cell(say B1) with format m/d/yyyy.
This gives me 3/1/2005. I've another cell(say C1) where I've entered the date
2/27/2005. Now on D1(=b1-c1) with format numeric, I get a 3 as opposed to a 2
when I hardcode 3/1/2005 in B1 instead of the NOW() function.

#2. Using the value in D1 in further calculations is even more scary.
Multiplying D1(value 3) by a 100 gives me an output of 269 as opposed to 300.

 

[Rob Bovey]

Hi Manoj,

The reason you're having these problems is because the NOW() function
returns the current date and time, which is a decimal value when used in a
mathematical expression. To verify this, select the cell containing NOW()
and press Ctrl+Shift+~ (tilde) to remove the formatting. You will see the
underlying date serial value which will look something like 38412.79392
(depending on what the current date and time are). The decimal part is used
to represent the current time, and this is what's causing your problems.

If you want to do math on pure dates you should use the TODAY()
function, which returns only the current date (the integer part of the date
serial value). Using Ctrl+Shift+~ to reveal the underlying date serial value
for a cell containing TODAY() would show something like 38412. You can see
that this is the same integer value as returned by NOW(), but without the
decimal part. This should work as expected in your equations.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm