Date keeps changing. How to keep it static?

When I populate B7, B8, B9, etc... I ask cell F7, F8, F9, etc. to put in today's date.
That works fine.

When I open THAT SAME excel worksheet next day, F7, F8, F9, etc. gives me TODAY'S date. Every different day that I open that same excel worksheet, it updates the date without me asking it to.

How do I tell F7... If B7 is populated, put in today's date, and leave it like that; don't refresh to a new date.

This is the current formula I am using on F7:

=IF(LEN(B7)>0,TODAY(),"")

Is TODAY the wrong function to use?

Originally Posted by JPWRana

When I populate B7, B8, B9, etc... I ask cell F7, F8, F9, etc. to put in today's date.
That works fine.

When I open THAT SAME excel worksheet next day, F7, F8, F9, etc. gives me TODAY'S date. Every different day that I open that same excel worksheet, it updates the date without me asking it to.

How do I tell F7... If B7 is populated, put in today's date, and leave it like that; don't refresh to a new date.

This is the current formula I am using on F7:

=IF(LEN(B7)>0,TODAY(),"")

Is TODAY the wrong function to use?

Not necessarily. TODAY() is a volatile function. It updates upon calculation of your spreadsheet and when you open it.

To get around this, and keep your dates static, whenever you use TODAY() (or NOW()) copy and paste back to the same cell with the paste values option.
As an alternative, if formulas aren't required, the Keyboard shortcut for entering the date in Excel is Ctrl + ;
Try it.

There are other things you can do if you require the date in a function and that would be to use a "hard copied" date as above in a "helper" cell. Just refer to that in your formula.

Hope this helps.

Originally Posted by FlameRetired

Not necessarily. TODAY() is a volatile function. It updates upon calculation of your spreadsheet and when you open it.

I see that now... lol.

Originally Posted by FlameRetired

To get around this, and keep your dates static, whenever you use TODAY() (or NOW()) copy and paste back to the same cell with the paste values option.

I don't get understand.

Originally Posted by FlameRetired

As an alternative, if formulas aren't required, the Keyboard shortcut for entering the date in Excel is Ctrl + ;
Try it.

Flame, I am aware of the Ctrl + ; option, but I want to avoid any EXTRA key strokes. Every key stroke that I DON'T HAVE TO DO is time saved.

Originally Posted by FlameRetired

There are other things you can do if you require the date in a function and that would be to use a "hard copied" date as above in a "helper" cell. Just refer to that in your formula.

Hope this helps.

Like what?

Originally Posted by JPWRana

I see that now... lol.


I don't get understand.



Flame, I am aware of the Ctrl + ; option, but I want to avoid any EXTRA key strokes. Every key stroke that I DON'T HAVE TO DO is time saved.



Like what?

OK. The extra key strokes thing I can't help you with. Everything I've suggested does that. The only thing left that I can think of is VBA...macros....
and I am VBA anemic. Maybe someone who isn't will step in.
Sorry I couldn't help.

Originally Posted by SCLai

Hi,

From what you have described, you may want to include circular reference to keep your date static.

In my opinion, you can do so by:
1.) Enable iteration (something like 3)
2.) use: =if(F7<>"",F7,IF(LEN(B7)>0,TODAY(),""))
*I do not have excel installed in my home (current) computer so I cannot test out the formula, but I believe what I have stated should work...
**Assuming B7 to be input, F7 to be time stamp

Please feel free to tell me if it is not what you wanted!


SC

SC... I tried what the formula you gave me, but it doesn't work.

SCLai... I piloted the formula last week and I think it's ok. After December ends, if it still doesn't give me hiccups, I will close the thread SOLVED.

By the way, just so you know, its the first formula on Post #7 the one that IS GIVING ME what I'm looking for.

If I only understood why it works , but other than that