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

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 TimeThere 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 SubWhen you enter anything in a cell in Column A, the date and time will appear in

Column B.

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

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.

DerbyDad03

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?

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.

Ask Your Question

Weekly Poll