today

[Chuck Fluri]

Hi I was wondering if there's a way to add today's date statically (so it
won't auto update) to a cell inside a formula. I would like the cell to
enter today's date automatically when a value is placed in cell A:12,

something like this;

=IF(NOT(A12=0),TODAY()," ")

This works except this will update the date each time I open the workbook
and I don't want that.

Thanks.

 

[Pete_UK]

You could change the formula to something like:

=IF(A12=0,"",DATE(2007,11,15))

for today's date. I've made a few other changes - got rid of NOT and
reversed the order, and changed " " to "".

Hope this helps.

Pete

 

[MartinW]

Hi Chuck,

Take a look here,
http://www.mcgimpsey.com/excel/timestamp.html

HTH
Martin

 

[Chuck Fluri]

Hi Chuck,

Take a look here,
http://www.mcgimpsey.com/excel/timestamp.html

HTH
Martin

I like this one from the above link:

=IF(A1="","",IF(B1="",NOW(),B1))

but why is there not a formula field in excel for this one? I'm sure I'm not
the first person who could use this.

 

[Peo Sjoblom]

What do you mean by this?

"but why is there not a formula field in excel for this one? I'm sure I'm
not
the first person who could use this."



--


Regards,


Peo Sjoblom

 

[Chuck Fluri]

I mean why have so many fields that update every time that you open excel.
Now(), date(), today(), all do pretty much the same thing and they all
update each time you open excel.
Lets go back to my original example which "Pete_UK" commented on and left
this:
=IF(A12=0,"",DATE(2007,11,15))
That's OK but why should you have to enter the 2007,11,15? The computer
knows what today is. Lets say excel had a function called NOWS (for now
static) that would only update when the referenced cell is modified. The
formula would now be:
=IF(A12=0,"",NOWS())
and would fill in the current date into the cell when A:12 is no longer zero
and would only change if and when A:12 changes.

 

[Peo Sjoblom]

There are only 2 that will do this, today() and now() and the difference is
that now includes the time as well. The date function does not update unless
any of the previous function are part of it.
You can also use Ctrl + ; to get the computer date.
The computer doesn't not know what today is, anyone can change the date on a
computer, you are asking the function to get the time and date from the
clock in the computer. What if the clock in wrong, how are you going to
update that if you have several hundreds of linked formulas dependant on
this. This is not doable. There are no functions that work this way, all
functions update if the source changes. If the contents of a cell changes
from 0 to 100 a formula dependant on that cell will change as well but you
are asking that a function will only update once.
What if you mistakenly cleared what was in A12, 3 weeks 25 minutes and 14
seconds after the formula was originally created, that would mean your
function would stop working since it would return a blank, now you would put
back the non zero value but the formula would update with the current date
and time?
That is not possible unless you use circular referencing like in the example
or the better by using an event macro as in the example as well.



--


Regards,


Peo Sjoblom