Need formula to add time stamp to update time

Microsoft Office 2007 professional (aca...
October 6, 2010 at 06:02:32
Specs: Windows XP
I am not good at all with formulas can someone post a formula to copy to make it so when I enter data in Excel say column A Column B will put the Date and Time in automatically?
I would need a copy paste formula as I am no good with formulas at all.

Thank You

See More: Need formula to add time stamp to update time

October 6, 2010 at 06:41:37
The only Excel functions that will automatically put a date and/or time in a cell are, unfortunately, known as Volatile functions, which means they will change to show the current data and/or time every time the sheet does a calculation.

=TODAY() for the Date
=NOW() for the Date and Time

There is no way to use a "formula" to automatically put a date and/or time in cell and have it remain static.

There are 2 options:

Option 1:

These keystrokes will "hardcode" the date or the time in a cell:

Stolen without permission from here:

  Insert Time/Date in Excel 
    Try these keyboard shortcuts to insert the
    time/date in an Excel spreadsheet:
        - Current date: Press CTRL+SEMICOLON 
        - Current time: Press CTRL+SHIFT+ SEMICOLON 
        - Current date and time: Press CTRL+ SEMICOLON 
          then SPACE then CTRL+SHIFT+ SEMICOLON

Option 2:

Use a Worksheet_Change macro:

Right click the sheet tab where you want to insert the Date and Time and Choose View code

Paste this code into the pane that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 1 Then _
  Target.Offset(0, 1) = Now
End Sub

When you enter anything in a cell in Column A, the date and time will appear in
Column B.

Report •

October 6, 2010 at 07:47:17
I appreciate the reply I think I finally figured it out.

=IF(B2="","", NOW())
I put this formula in A2 and it populates the Date and Time in B2.
It is working so I am happy.

Thanks Again

Report •

October 6, 2010 at 08:39:17
Yes, that formula will indeed put a date and time in A2, but every time the sheet calculates the time is going to change. That's going to include every time the sheet is opened or closed.

If that's what you want - an ever changing time stamp - then you're all set. If not, then please reread my earlier post.

Report •

Related Solutions

October 6, 2010 at 10:19:06
Is there something special I have to do to have it show me View Code it says it is un-viewable. Like I said before this is new territory for me. Is option two you talk about VBS?

Report •

October 6, 2010 at 10:26:00
Actually in this case it's VBA, not VBS. Visual Basic for Applications.

I don't have 2007 on this machine, so the View Code procedure might be different.

Look under the Office Button or however you get to the VBA editior in 2007. Try Alt-F11

I can let you know when I get home tonight since I have 2010 on my home machine.

Report •

Ask Question