If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
possible to have =now() at data entry and not update after that?
How can I set a cell to contain the =NOW() date only once when data is
entered in the adjacent cells and not have it update after that? Right now I copy from a cell that contains =now() and paste value only into the cell. Is there an cleaner way? |
#2
|
|||
|
|||
Hi
From Excel Help (Keyboard shortcuts) CTRL+; (semicolon) Enter the date CTRL+SHIFT+: (colon) Enter the time Arvi Laanemets ".:mmac:." lost@sea wrote in message ... How can I set a cell to contain the =NOW() date only once when data is entered in the adjacent cells and not have it update after that? Right now I copy from a cell that contains =now() and paste value only into the cell. Is there an cleaner way? |
#3
|
|||
|
|||
Thanks for the reply but thats not what I meant.
I meant that the date would pop in automatically when data was entered in an adjacent cell. something like this would be in A1: = if ((A2 0)now(), "") I'm sure the syntax is wrong but it conveys the idea. "Arvi Laanemets" wrote in message ... Hi From Excel Help (Keyboard shortcuts) CTRL+; (semicolon) Enter the date CTRL+SHIFT+: (colon) Enter the time Arvi Laanemets ".:mmac:." lost@sea wrote in message ... How can I set a cell to contain the =NOW() date only once when data is entered in the adjacent cells and not have it update after that? Right now I copy from a cell that contains =now() and paste value only into the cell. Is there an cleaner way? |
#4
|
|||
|
|||
See
http://www.mcgimpsey.com/excel/timestamp.html for both a function and a VBA solution -- Regards, Peo Sjoblom ".:mmac:." lost@sea wrote in message ... Thanks for the reply but thats not what I meant. I meant that the date would pop in automatically when data was entered in an adjacent cell. something like this would be in A1: = if ((A2 0)now(), "") I'm sure the syntax is wrong but it conveys the idea. "Arvi Laanemets" wrote in message ... Hi From Excel Help (Keyboard shortcuts) CTRL+; (semicolon) Enter the date CTRL+SHIFT+: (colon) Enter the time Arvi Laanemets ".:mmac:." lost@sea wrote in message ... How can I set a cell to contain the =NOW() date only once when data is entered in the adjacent cells and not have it update after that? Right now I copy from a cell that contains =now() and paste value only into the cell. Is there an cleaner way? |
#5
|
|||
|
|||
this is the example given, modified for my application; I need the time cell
to be to the left of the entry cell and more cells to return the value. otherwise it's the same. But it does nothing and it doesn't show up in the macros list but the instructions state to put in the "This Workbook" module. perhaps I did that wrong? I cant just enter it as a macro because then it puts a wrapper around the code and says that I need another "End Sub" Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("b3:b1000"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -1).ClearContents Else With .Offset(0, -1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub "Peo Sjoblom" wrote in message ... See http://www.mcgimpsey.com/excel/timestamp.html for both a function and a VBA solution Regards, Peo Sjoblom Thanks for the reply but thats not what I meant. I meant that the date would pop in automatically when data was entered in an adjacent cell. something like this would be in A1: = if ((A2 0)now(), "") I'm sure the syntax is wrong but it conveys the idea. "Arvi Laanemets" wrote in message ... Hi From Excel Help (Keyboard shortcuts) CTRL+; (semicolon) Enter the date CTRL+SHIFT+: (colon) Enter the time Arvi Laanemets ".:mmac:." lost@sea wrote in message ... How can I set a cell to contain the =NOW() date only once when data is entered in the adjacent cells and not have it update after that? Right now I copy from a cell that contains =now() and paste value only into the cell. Is there an cleaner way? |
#6
|
|||
|
|||
Right click the sheet tab (the sheet where you want this to happen) and
select view code, paste it into there, press Alt + Q to close the VBE -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) ".:mmac:." lost@sea wrote in message ... this is the example given, modified for my application; I need the time cell to be to the left of the entry cell and more cells to return the value. otherwise it's the same. But it does nothing and it doesn't show up in the macros list but the instructions state to put in the "This Workbook" module. perhaps I did that wrong? I cant just enter it as a macro because then it puts a wrapper around the code and says that I need another "End Sub" Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("b3:b1000"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, -1).ClearContents Else With .Offset(0, -1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub "Peo Sjoblom" wrote in message ... See http://www.mcgimpsey.com/excel/timestamp.html for both a function and a VBA solution Regards, Peo Sjoblom Thanks for the reply but thats not what I meant. I meant that the date would pop in automatically when data was entered in an adjacent cell. something like this would be in A1: = if ((A2 0)now(), "") I'm sure the syntax is wrong but it conveys the idea. "Arvi Laanemets" wrote in message ... Hi From Excel Help (Keyboard shortcuts) CTRL+; (semicolon) Enter the date CTRL+SHIFT+: (colon) Enter the time Arvi Laanemets ".:mmac:." lost@sea wrote in message ... How can I set a cell to contain the =NOW() date only once when data is entered in the adjacent cells and not have it update after that? Right now I copy from a cell that contains =now() and paste value only into the cell. Is there an cleaner way? |
Thread Tools | |
Display Modes | |
|
|