Formula Conformation

[Arts]

Hi all

I used the below formula (searching online I found that NOW gives you a time stamp as opposed to TODAY which updates the date regularly)

=IF(A3<>"",NOW(), "")

The reason behind the below is that when someone enters a value the timestamp is generated for audit purposes. I used the below formula yesterday to test and noticed that the date had updated to todays date.

Have I entered something wrong here?

As always any help is very much appreciated. Ps if I am delayed in responding currently at work but shall get back to any replies.



Thanks

 

[shinigamilight]

Now gives you a dynamic date, everytime you press F9 it updates automatically. I created a macro to add timestamp in order to overcome this and a UDF.

 

[etaf]

Now() gives the date & time and will update, everytime excel calculates
you can use something like
=IF(C3<>"",IF(B3="",NOW(),B3),"") in cell B3.
if some one enters a value in c3 , B3 will show the date and time
and will not change providing iterations are set up, otherwise a circular error will occur

what cell do you want the time stamp in and what cell is someone entering data into
But you also have to set iterations in the preferences
have a read here

How to insert timestamps in excel sheet using formulas

Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time). This is nothing but timestamping. Once the item is time stamped, it is much more easier to analyze it. Here is an excel formula trick to generate...

chandoo.org

 

[Arts]

Hi thanks for the replies guys,

@etaf My initial entry will go into Column A (user) with the time stamp expected to go into column C

My formula seems to be slightly off to the one you provided but will give that a whirl, I will go over the link you sent me, thanks for sending.

Does make me wonder that if NOW updates every time it calculates seems very similar to TODAY, I mean on most instances I assume spreadsheets would be updated therefore calculating...

 

[etaf]

Does make me wonder that if NOW updates every time it calculates seems very similar to TODAY, I mean on most instances I assume spreadsheets would be updated therefore calculating...

Yes, that the idea , people use today() a lot in conditional formatting for things like overdue based on todays date - and so it needs to update.
NOW() just gives the date and also the time
so again can be useful in functions to know the time now and use that to base other calculations on - again conditional formatting for example

SO in C2 you type and copy down - BUT iterations is the Key setting
=IF(A2<>"",IF(C2="",NOW(),C2),"")

 

[Arts]

Yes, that the idea , people use today() a lot in conditional formatting for things like overdue based on todays date - and so it needs to update.
NOW() just gives the date and also the time
so again can be useful in functions to know the time now and use that to base other calculations on - again conditional formatting for example

SO in C2 you type and copy down - BUT iterations is the Key setting
=IF(A2<>"",IF(C2="",NOW(),C2),"")

At first glance would this not give a circular reference? But will go through the link you sent to make any adjustments as need be, always gets me thinking how something so simple has been missed of excel (I mean I would classify having a time stamp as a simple thing to achieve compared to other things I have seen excel do)

 

[etaf]

yes , it will - hence why
iterations need to be set - I did say that , but - made it clearer during an edit - why iterations need to be set - which you may have missed

if some one enters a value in c3 , B3 will show the date and time
and will not change providing iterations are set up, otherwise a circular error will occur

 

[Arts]

yes , it will - hence why
iterations need to be set - I did say that , but - made it clearer during an edit - why iterations need to be set - which you may have missed

I have yet to go through the link (at work) just mentioned at first glance but shall go through the link and set iterations as need to.

Thanks