Need help with date and time

[Roemeo]

hello everyone i have a question what i need to do in excel is set up a cell to input info and then beside it on the next cell to display the date and time that it was inputed but from what i created the cel l(time and date) keeps updating everytime the sheet is open any ideas ?

this is what i used Example

Please Login or Register  to view this content.

[DonkeyOte]

For a static time stamp you're looking at either

a) VBA Change Event

b) Iterative Calculations

If the datetime stamp is to be updated whenever C1 is altered ie not just the very first time then you will need to use VBA.... if it's a one off then you can use b (ie avoid VBA).

If you want to go for VBA please specify the range against which this logic is to be applied ie values in C1:Cx... necessitate time stamps in A1:Bx)

[Roemeo]

im fairly new to this so B would be my best option. How about would i go writting that. im still learning and would appreciate a lesson. explained if possible plz and ty

[DonkeyOte]

If you go into the options via Tools menu and Calculation Tab you will find there is an Enable Iteration option, check this and set Max Iteration to 1.

Back to your sheet... ensure C1 is blank... clear A1 & B1 of any existing formulae then set formula such that

Please Login or Register  to view this content.

Now if you change C1 you should find that A1 populates with the time of the update and will not update thereafter unless you clear C1 content before re-entering new value ... eg:

C1: "a" -> "b" ... time stamp will only update when you first enter "a"

C1: "a" -> CLEAR -> "b" ... time stamp will update when you enter "a" and then subsequently when you enter "b" and be cleared when you first clear "a"

Enabling Iteration essentially permits the use of what are known as "circular references" - formulae in cells that refer to themselves - in our case the formula in A1 refers to itself and is thus circular by nature.