Date & time auto generated based on input date & time

Hi all,
I want a function that can automatically generate date & time based on our input date & time.
For Ex :
Date & Time Posted
07 September 2010 11:02 AM

Name
BlastRanger

I want the date & time to fill automatically when I key in the name.
Is that possible?

Ideally you would use VBA (Change Event) though pending answers to below you might find you can use Iteration if you really want to.

In short I think you need to provide a little more detail...

To which range which this logic apply ?

Where will the stamp be posted in relation to the input cell ?

Should entering a blank clear the stamp ?

Should the stamp update if the cell is altered to something other than a blank ?
(ie stamp to reflect last change datetime if changed more than once)

Erm, I would prefer a simple date & time tracking.
I want the date & time auto filled in cell B6 when cell D6 is entered (Regardless of any value)
Then in cell C6 I want the date & time auto filled IF there are changes made to D6.

Take it as the Column B is Original date, Column C is modified date

I just want simple tracking date & time a value is being entered. No stamp will be posted.

To clear the situation,
The date and time usually manually typed, example I type today date and what time is it now, after that I type the value in D6. It's simple but if there are another way can make things even simpler by automatically input date & time

So you're saying this will only apply to D6 and no other cells on the sheet ?

Entering a blank into D6 should not clear either/both of B6:C6 ?

Yeah just apply to D6.
Yeah a blank into D6 should not clear both.

OK then I think I should explain more.
for instant, I dont need to type date and time manually, but I just select B6 and press CTRL+; then SPACE then CTRL+SHIFT+;

I hope it can just like the formula =today() and =now() entered in the B6, but the date I want it static.

Oh I forgot, also apply to those cell below it, which is D7, D8, D9 . . . . .

This sample does not reflect the requirements you laid out earlier in the thread.

Previously you mentioned two stamp columns with entry values in D.

What works for the sample file you have provided will not work for the requirements previously outlined.

So we need to know: which is it to be ?

Originally Posted by blastranger

In order not to confuse then I think should use the sample instead.

I disagree entirely - if the actual requirements are as stated previously then providing code that does something altogether different is a waste of time for all concerned.

If you want two stamps in separate columns (first update, last update) then that's a little different to a standard stamp.

Running two stamps will require VBA.

If you want just the single stamp which is to hold the original date and which once created is never to change then you can use Formulae which I suspect you would prefer ?

To do this - using your simplified sample file...

First: via Tools -> Options -> Calculation tab enable Iteration and set Max Iteration to 1 -> click OK.

Second: for sake of demo. clear B2

Highlight A2 to A25 and type the following formula:

Please Login or Register  to view this content.

Apply a Custom Format to A2:A25 of: dd-mm-yyyy hh:mm:ss;;;@
(modify time format as desired)

It works perfectly.
via Tools -> Options -> Calculation tab enable Iteration and set Max Iteration to 1 -> click OK.
Will that change for permanent? why need to change that? if change permanent would it be affected to another excel file?
Also explain about =IF(A2,A2,NOW()*(B2<>""))
why B2<>""

Originally Posted by blastranger

It works perfectly.
via Tools -> Options -> Calculation tab enable Iteration and set Max Iteration to 1 -> click OK.
Will that change for permanent? why need to change that? if change permanent would it be affected to another excel file?
Also explain about =IF(A2,A2,NOW()*(B2<>""))
why B2<>""

The Iteration setting is an Application level setting and will be saved with the workbook.
In regard to how Excel will enforce calculation settings see: http://www.decisionmodels.com/calcsecretse.htm

Originally Posted by blastranger

why need to change that?

The formula used is circular by nature - that is to say A2 is a precedent of itself - enabling Iteration permits self referencing formulae.

Originally Posted by blastranger

Also explain about =IF(A2,A2,NOW()*(B2<>""))
why B2<>""

Break the formula out into parts.

Please Login or Register  to view this content.

DateTime values in Excel are numbers - Time being decimal - ie 24 hours = 1.
For ex. 7th Sep 10:00 AM equates to 40428.4166666667 on a 1900 date system.
It follows that NOW() is returning a Numeric value.

Please Login or Register  to view this content.

will return a Boolean of TRUE or FALSE which when coerced (by means of a suitable arithmetic operation) equate to 1/0 respectively, eg:

10* FALSE -> 10 * 0 -> 0
10 * TRUE -> 10 * 1 - > 10

It follows then that only if B2 is non-blank/non-null will our calculation generate anything other than 0.

With that in mind we can look at the formula as a whole:

Please Login or Register  to view this content.

We know that A2 will contain a numeric value - either 0 or a datetime value.

We've seen already how Booleans can be coerced to Integer.
When working in reverse (ie from Number to Boolean) it is important to note that only the value 0 equates to FALSE, eg:

=IF(0.01,TRUE,FALSE) -> TRUE
=IF(0,TRUE,FALSE) -> FALSE

Given this fact it follows that where viable we can use a number to generate a TRUE/FALSE test - in our case we use the value in A2.

So in essence what our formula says :

=IF(A2 not 0, A2 , current date time if B2 is not empty else 0)

So once A2 is populated with the a date time value (ie when content is first added to B2) the value in A2 will never change given the IF test will always return TRUE and thus the cell will refer to itself forever more.

Originally Posted by blastranger

the <> means "other than" as it is not "more than", and also not "less than".

No, "<>" means does not equal.

= equals
> greater than
< less than
>= greater than or equal to
<= less than or equal to
<> does not equal

Originally Posted by blastranger

I type 2 in B2 (which make the B2 not blank). Then the formula should multiply now() with 2 I guess. After multiply with 2 the result should be different with the date and time now, but why it will not?

As already outlined (B2<>"") returns only TRUE or FALSE - either B2 is non-blank/non-null (TRUE) or it is not (FALSE).

As a result NOW() is only ever multiplied by either 1 (TRUE) or 0 (FALSE)

I'm afraid I can't really explain it any other way.

Originally Posted by blastranger

What I want now is when the cell of value is deleted, the date go back to blank.

In which case:

=IF(B2="",0,IF(A2,A2,NOW()))