Time Stamp in Excel

Thank you, that is very useful but I was looking for some way to have it automated for the guys on the floor, most of those guys can't tell a mouse from a keyboard, so I have to do things that make it easy for them.

Here's what I was looking for, if I go to C3 and make that a pull down menu where they can select "Start" or "Stop" then in D3, the cell will automatically populate the date and time.

The "NOW()" would work fine, if it didn't update every time the spreadsheet updates.

Wonder if there is someway to put those key strokes in a macro???? ummmm

This is a good answer (ie: To insert a fixed date into a cell--a date that you don't want to change--hold down Ctrl while you press the semicolon ( ;) key. To insert the current time (without a date), press Ctrl-Shift-Semicolon.)

provided you turn this into a macro with a button or by assigning a shortcut key to keep it simple for the non-computing types :)

GA to sbruel@yahoo.com. It solves the problem exactly and with minimal fuss.

QL

As I'm sure you now know through experimentation, the 'now' command wont meet your needs. Having a novice computer user select a drop-down would be no more difficult than setting a 24-hour format clock next to the computer and having them enter the date and time directly.

If you wanted to get fancy, on your end… set up a protected spreadsheet with the first tab simply a macro button that invokes the 'now' command and copies the data and pastes special 'value only'. When they are done, they click the macro button again and it repeats.

Another option, if the machine they are setting up has a PLC, just add a push button on the machine somewhere that on first press starts a timer in the PLC. When they are done, they press the button again and it stops the timer and writes the data to a log file. You can then query the log once a week and dump the data to a spreadsheet yourself.

But, in my opinion, setting a three-column spreadsheet with 'date,' 'start,' and 'stop' columns that they type the data in directly is not that complicated and even the most novice of users would be able to do with very little training.

But, if you're worried about data manipulation… I'd say a modified version of the PLC version above. Tie in a timer to the first process they do at the beginning of the setup (operator sets machine to stand-by) that stops once they begin the first post-setup process (operator sets machine to ready).

If you do a complete lock-out of the machine, PLC and all, then have the PLC log the time as the last controlled process is implemented prior to throwing the main breaker. Then, have the PLC log the time as its first process upon start-up.

Regardless, there are quite a few different ways to track time… it's just that the 'now' command probably isn't one of the best options.

JavaHead

You can set up a function in vba that writes to a cell on workbook startup and shutdown.

As an example the code in the ThisWorksheet module could read. This assumes cell A1 on sheet1 holds the number of times the workbook has been opened.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim numTimesStarted as int = Sheet1.Cells(1,1).value
Sheet1.Cells(numTimesStarted, 2).Value = Now()
End Sub

Private Sub Workbook_Open()

Dim numTimesStarted as int = Sheet1.Cells(1,1).value
Sheet1.Cells(numTimesStarted, 1).Value = Now()

Sheet1.Cells(1,1).value = numTimesStarted + 1
End Sub

Search the web for excel and vba for more information.

Don't know that much about VBA but why not use the NOW() command as part of a macro that then converts the value to text. Then it won't be updated.