function that enters the corrent date automatically

**xena08** · 09-02-2008, 06:42 AM

Hi all,

I want to know if there is a function that enters the corrent date automatically. E.g., if I enter "3000" in B1, the result will be "2/9/2008" in, say, B2.

Can it be done?

xena

**Jokacave** · 09-02-2008, 07:29 AM

Hi Xena,
"3000" in B1 - Why 3000?
I don.t understand... Can you explain better what you want.

**xena08** · 09-02-2008, 07:41 AM

I want to make an expenses list so when I`ll write something today, never mind what, it will enter auto the date I entered this value.

E.g., if i`ll write 3000$ in B2, B1 - that will be the date entered column - will be updtaed with the current date.

Is it clear now?

**Jokacave** · 09-02-2008, 07:53 AM

You have any file?

**mudraker** · 09-02-2008, 08:14 AM

have a look at

http://www.mrexcel.com/archive2/27600/31486.htm

**martindwilson** · 09-02-2008, 08:30 AM

heres a little snippet i saved from somwhere
You can use a circular reference to enter the time when a change is made in
another cell, then maintain that time. Choose Tools/Options/Calculation
and check the Iteration checkbox. Then, if
your target cell is A1 and you want the date/time to appear in B1, enter this in
B1:

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

Format B1 as you wish to display date, time, or both. If A1 is initially blank,
B1 will return a null string (""). When a value is entered into A1, B1 will
evaluate as "", therefore NOW() will be returned. After that (as long as A1
remains populated), B1 will evaluate to a date/time and therefore will return
the value in B1 - i.e., the date/time.

**sglife** · 09-03-2008, 02:22 AM

there are 2 suggestions:

1. Remember this shortcut
CTRL+; (semicolon)
2. Attach your file and ask someone write a VBA code for you

**xena08** · 09-05-2008, 10:59 AM

Hi All,

This one did the trick for me (From Paddy):
1) Hit alt + F11 to get to the vb editor
2) go insert | module
3) paste in the following:

Function DateAndTime()
DateAndTime = Now
End Function

4) use it in a formula of the sort:

=if(a1=100%,dateandtime(),"")

One last thing: I`m a little newbie in advanced excel, so can anyone point me to some guide on this ctrl+F11 app? Didn`t know that.

xena

**Cheeky Charlie** · 09-05-2008, 11:11 AM

That's quite a roundabout way; why not just
now()
or
today()

**Cheeky Charlie** · 09-05-2008, 11:12 AM

i.e.
=if(a1=1,today(),"")

**martindwilson** · 09-05-2008, 11:55 AM

i suspect its becase the udf is not volatile (until you delete the contents ofreference cell)

**xena08** · 09-06-2008, 09:23 AM

I was wrong. I t`s not working, the paddy`s way. When I restart the excel file, it gives me "#name?" instead of date when a new number entered.

Any suggestions?

**Richard Schollar** · 09-06-2008, 10:41 AM

Do you want the date to remain static once entered (ie doesn't change)? If so, you really don't want to be entering this date with a formula. If you are happy using VBA then you could use a worksheet event to insert the date (as a static value rather than as a formula). Post back and I'm sure one of us can help you.

Richard

**davesexcel** · 09-06-2008, 10:52 AM

so can anyone point me to some guide on this Alt+F11 app? Didn`t know that.

Try to google " excel VBA for begginers "

here's one site

http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

If you are serious about learning VBA, than a book would work as well

**davesexcel** · 09-06-2008, 11:04 AM

Originally Posted by xena08

I was wrong. I t`s not working, the paddy`s way. When I restart the excel file, it gives me "#name?" instead of date when a new number entered.

Any suggestions?

The function

Function DateAndTime()
DateAndTime = Now
End Function

...works well when you want to time stamp as the date will not change automatically as =now() or =today() does, it will only change when you enter the formula again.
If you have the code saved in one workbook, and tried to use the function in another workbook, the function will not be recognized and the name error will show itself is this the case??

Maybe creating an "addin" would work, then it would be available for all workbooks on your computer.