Automatically place date and time in one cell when another cell has any data entered.

[sameredith]

Excel 2007
I want to enter any thing in column (B) and then have the date (mm/dd/yyyy) & military time (hh:mm:ss) automaticaly put in column (A) same row.
enter any thing in (B1), the date & time entered in (A1) automaticaly
enter any thing in (B2), the date & time entered in (A2) automaticaly
and so on and so forth...
Having the username also entered would just be a bonus.

[FDibbins]

in A1 enter this...

=if(B1="","",now())

format accordingly

[Special-K]

Just use a formula

in A1
=IF(B1<>"",NOW(),"")
and copy down the column

[sameredith]

I am afraid I was not clear in my question.
The entry needs both the Date(mm/dd/yyyy)and Military Time(hh:mm:ss)and it needs to be a static entry. Not dynamic.
Sorry for any confusion.

[FDibbins]

for a static time, you need some1 (other than me) to write some VBA code for you. with regards the time being military or not, that is simply how you format it

[J450n]

Check out this link:
http://www.cpearson.com/Excel/DateTimeEntry.htm
seems like you need to do a similar worksheet change event macro in the "sheet" code space, and have an "if intersect target and column B" and use range("a" & target.row) to write the date in the correct row of Column A. You can then overwrite the formula by suing the following code:

Please Login or Register  to view this content.

Hope this helps

[Jakobshavn]

There are two ways of doing this:

1. Using iterative calculations
2. Using an Event macro

The first method is the simpler. First in Excel Options, enable Iterative Calculations with a maximum of at least 1 (this allows a formula to reference the cell its in). Then in A1, enter:

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


To use the second method, enter the following macro in the worksheet code area:

Please Login or Register  to view this content.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!