Need Help with Date/Time Function 3

[quicklearner]

For my workgroup I have created a log in excel. Column A is the "Person/Role", Column B is the "Date & Time" and Column C is for "Comments & Notations."

I would like for everytime a person enters their name in the cell under Column A, that the current date and time would be automatically stamped into the corresponding cell under column B.

Suggestions or links would be appreciated.

 

[sfvb]

If you format column B as Date/Time from either the Date or Time formats, you could use:

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

 

[quicklearner]

Could you please explain this formula alittle bit? Would I be adding another formula to this one based upon how I format the Date/Time? I need for the date and time to be stamped into the cell and not change once the event occurs. (I think I didn't clarify that in my earlier post)

 

[xlhelp]

The now formula will change the date and time every time the worksheet is calculated.

Your best bet is have Worksheet_Change event macro that will take an entry and embed a date stamp in the cell you require.

Here is a non-vb solution. Caution: This will throw off your any calculations you have in the worksheet

Credit:Tushar Mehta

For this, it would be a good idea to move the comment section to column B.

Then go to Tools, Options, Calculation tab, Check Iteration and set iteration to 2.

In cell C2, enter the formula
=IF(D2=1,B2,C2)
In cell D2, enter the formula
==IF(B2<>C2,1,0))
and in Cell E2, enter the formula
==IF(B2<>C2,NOW(),E2))

Now any time there is an entry in column B, there will be a time stamp in column E. You can always hide columns C&D

Even if you don't use this, it's fun to try

 

[xlhelp]

As you can see I am terrible at typing. Please ignore the double equal signs.

Now where is that Mavis Beacon package........

 

[rigel7]

Nice Trick. Thanks.

 

[quicklearner]

I tried your formulas, and the last column enters the date and time as a decimal.

I am not at all familiar with macros. Isn't there an easier solution? All I need is when an entry is made in column A, a non-changing date and time stamp occurs in column B. A solution can't be that hard?

 

[Zathras]

This is an easy way to get your feet wet in the world of macros.

1. With your spreadsheet open and active, key Alt-F11 to bring up the VBA editor.

2. If you don't see the Project Explorer on the left-hand side of the screen, type Ctrl-R to show it.

3. Expand the VBAProject entry if necessary.

4. Double click on the Sheet1 (xxxx) line.

5. Paste the following into the macro editor space (everything between the two lines of equal signs):

=========================================================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Left(Target.Address, 2) = &quot;$A&quot; Then
Target.Offset(0, 1).NumberFormat = &quot;m/d/yy h:mm AM/PM&quot;
Target.Offset(0, 1).Value = Now()
End If

End Sub
=========================================================

6. Go to the worksheet and type something in any cell in column &quot;A&quot; and you should see the date/time appear in column &quot;B&quot;

7. If you don't like the format, play with the date format mask in the macro code.

Be aware that this is slightly different from &quot;normal&quot; macro code in that you are assigning actions to worksheet events. &quot;Normal&quot; macro code would have you first insert a Module and then do your coding there (by double-clicking on the Module1 line in the project explorer window.

 

[xlhelp]

All you have to do is format the cells as date.

 

[quicklearner]

Thank you to zathras for a perfect answer. That macro does exactly what I needed!