Home |
Search |
Today's Posts |
#1
|
|||
|
|||
excel help
I am in need of a formula to develope a timestamp of sorts. I want to
check cell A1 for any data and then automatically input the date and time the data was entered. I have tried the formula =IF(A1<"",NOW(),"") The problem with the now command is that it updates with each calulation. I need the date and time to be fixed. Rick |
#2
|
|||
|
|||
Hi
you need to either enter the date & time manually control & ; (semi-colon) will give you the date and control & shift & ; will give you the time you can put these two in the same cell by pressing spacebar between them ..... or you can use worksheet_change code which runs whenever the value in A1 is changed (in the following code the date & time will be put into cell B1) ----- Private Sub Worksheet_Change(ByVal Target As Range) If Target.address = "$A$1" And Not IsNull(Target.Value) Then Target.Offset(0, 1).Value = Now End If End Sub --- to use this code, right mouse click on the sheet tab of the sheet you're dealing with and choose view code, copy and paste the code on the right hand side screen then use ALT & F11 to return to your workbook. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway wrote in message oups.com... I am in need of a formula to develope a timestamp of sorts. I want to check cell A1 for any data and then automatically input the date and time the data was entered. I have tried the formula =IF(A1<"",NOW(),"") The problem with the now command is that it updates with each calulation. I need the date and time to be fixed. Rick |
#3
|
|||
|
|||
Julie, I know the manual keystrokes will work, but I am trying to make
this so any non-computer user will be able to work it. What I am doing is reading a bar code into cell A and need it timestamped. Is there a way to generate the manual keystrokes within a formula. Know matter how I use the NOW command it updates to the new time. Thanks!!! Rick |
#4
|
|||
|
|||
Julie's response also had some VBA code to address the situation, and I think
that's about the only way to accomplish your goal. her code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.address = "$A$1" And Not IsNull(Target.Value) Then Target.Offset(0, 1).Value = Now End If End Sub Julie's code works only for cell A1. You may want to change the If statement to If Target.column = 1 And Not IsNull(Target.Value) Then Change the "column = 1" to reflect your column of interest, and this will then work for any cell in that column " wrote: Julie, I know the manual keystrokes will work, but I am trying to make this so any non-computer user will be able to work it. What I am doing is reading a bar code into cell A and need it timestamped. Is there a way to generate the manual keystrokes within a formula. Know matter how I use the NOW command it updates to the new time. Thanks!!! Rick |
#5
|
|||
|
|||
Duke and Julie, I tried the VBA code and have it working. The only
problem is I do not have to enter any data in column A to get the date in column B. All I need to do is position the curser on a cell in column A and the date is input. What the problem is, if I move the curser up to previously input cells, it changes the date. Is there a way to have this work where you have to have data in each of the respective column A cells? Rick |
#6
|
|||
|
|||
Make sure you put the code in the right procedure. It sounds as if your
procedure is named Private Sub Worksheet_SelectionChange(ByVal Target As Range) and not Private Sub Worksheet_Change(ByVal Target As Range) The former fires every time you select a new cell, the latter fires only when a cell is changed " wrote: Duke and Julie, I tried the VBA code and have it working. The only problem is I do not have to enter any data in column A to get the date in column B. All I need to do is position the curser on a cell in column A and the date is input. What the problem is, if I move the curser up to previously input cells, it changes the date. Is there a way to have this work where you have to have data in each of the respective column A cells? Rick |
#7
|
|||
|
|||
Duke, thanks!! That's got it.
Rick |
#8
|
|||
|
|||
Rick
Try this version Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col A On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("B" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub Or this version to keep previously stamped dates if editing column A Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Col B time will not change if data in Col A is edited On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" _ And Excel.Range("B" & n).Value = "" Then Excel.Range("B" & n).Value = Format(Now, "hh:mm:ss") End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On 5 Apr 2005 11:17:11 -0700, wrote: Duke and Julie, I tried the VBA code and have it working. The only problem is I do not have to enter any data in column A to get the date in column B. All I need to do is position the curser on a cell in column A and the date is input. What the problem is, if I move the curser up to previously input cells, it changes the date. Is there a way to have this work where you have to have data in each of the respective column A cells? Rick |
#9
|
|||
|
|||
Duke, good catch, i wouldn't have had an idea :)
-- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Duke Carey" wrote in message ... Make sure you put the code in the right procedure. It sounds as if your procedure is named Private Sub Worksheet_SelectionChange(ByVal Target As Range) and not Private Sub Worksheet_Change(ByVal Target As Range) The former fires every time you select a new cell, the latter fires only when a cell is changed " wrote: Duke and Julie, I tried the VBA code and have it working. The only problem is I do not have to enter any data in column A to get the date in column B. All I need to do is position the curser on a cell in column A and the date is input. What the problem is, if I move the curser up to previously input cells, it changes the date. Is there a way to have this work where you have to have data in each of the respective column A cells? Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
html to excel | Excel Discussion (Misc queries) | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Merge from Excel to Excel | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) | |||
Shortcut file fails to open | Excel Discussion (Misc queries) |