Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
xecel 2007 on WindowsXP
I need to time stamp entries in a spreadsheet. I thought the NOW() function would do teh trick, but alas, I was mistaken. In column "B" I used the formula =if(A#0, NOW(), "") which worked great, until I moved to the next line and made an entry into column A, that is when the both entries in column B were updated to the current time. Is there a way to record the time a cell changes from empty to non empty status, without updating every other cell in the column? We are simply attempting to automate a call log-- shouldn't be this difficult. Anyone have a solution for my little mind twister today Thanks in advance for the fantatsic help this forum provides |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
To get a static time right click your worksheet, view code and paste this code in. Any entry in column A makes column B populate with a static NOW Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False With Target.Offset(, 1) .Value = Now .NumberFormat = ("dd/mm/yyyy hh:mm") End With Application.EnableEvents = True End If End Sub Mike "JR Hester" wrote: xecel 2007 on WindowsXP I need to time stamp entries in a spreadsheet. I thought the NOW() function would do teh trick, but alas, I was mistaken. In column "B" I used the formula =if(A#0, NOW(), "") which worked great, until I moved to the next line and made an entry into column A, that is when the both entries in column B were updated to the current time. Is there a way to record the time a cell changes from empty to non empty status, without updating every other cell in the column? We are simply attempting to automate a call log-- shouldn't be this difficult. Anyone have a solution for my little mind twister today Thanks in advance for the fantatsic help this forum provides |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thank you , that works just as we had hoped for.
"Mike H" wrote: Hi, To get a static time right click your worksheet, view code and paste this code in. Any entry in column A makes column B populate with a static NOW Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False With Target.Offset(, 1) .Value = Now .NumberFormat = ("dd/mm/yyyy hh:mm") End With Application.EnableEvents = True End If End Sub Mike "JR Hester" wrote: xecel 2007 on WindowsXP I need to time stamp entries in a spreadsheet. I thought the NOW() function would do teh trick, but alas, I was mistaken. In column "B" I used the formula =if(A#0, NOW(), "") which worked great, until I moved to the next line and made an entry into column A, that is when the both entries in column B were updated to the current time. Is there a way to record the time a cell changes from empty to non empty status, without updating every other cell in the column? We are simply attempting to automate a call log-- shouldn't be this difficult. Anyone have a solution for my little mind twister today Thanks in advance for the fantatsic help this forum provides |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Your welcome
"JR Hester" wrote: Thank you , that works just as we had hoped for. "Mike H" wrote: Hi, To get a static time right click your worksheet, view code and paste this code in. Any entry in column A makes column B populate with a static NOW Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False With Target.Offset(, 1) .Value = Now .NumberFormat = ("dd/mm/yyyy hh:mm") End With Application.EnableEvents = True End If End Sub Mike "JR Hester" wrote: xecel 2007 on WindowsXP I need to time stamp entries in a spreadsheet. I thought the NOW() function would do teh trick, but alas, I was mistaken. In column "B" I used the formula =if(A#0, NOW(), "") which worked great, until I moved to the next line and made an entry into column A, that is when the both entries in column B were updated to the current time. Is there a way to record the time a cell changes from empty to non empty status, without updating every other cell in the column? We are simply attempting to automate a call log-- shouldn't be this difficult. Anyone have a solution for my little mind twister today Thanks in advance for the fantatsic help this forum provides |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Mike,
Thanks for that solution yesterday. Admittedly I am not macro literate at this time. Now my assocaites need another version of that routine. They want to add a second version in teh same worksheet that targets column G for data entry and palce teh static NOW into column "F". I thought just making some changes to teh code might get the results we were looking for. I tried changing the offset to -1, 7 or even leaving at just 1, without any luck. Here is the last code I tried, am I any where close or is this worksheet change macro limited to one occurrence in a worksheet? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False With Target.Offset(, 1) ..Value = Now ..NumberFormat = ("hh:mm:ss") End With Application.EnableEvents = True End If End Sub Private Sub Worksheet_Change2(ByVal Target2 As Range) If Target2.Cells.Count 1 Or IsEmpty(Target2) Then Exit Sub If Not Intersect(Target2, Range("G:G")) Is Nothing Then Application.EnableEvents = False With Target2.Offset(, -1) ..Value = Now ..NumberFormat = ("hh:mm:ss") End With Application.EnableEvents = True End If End Sub Thanks again for any guidance you can offer. "Mike H" wrote: Your welcome "JR Hester" wrote: Thank you , that works just as we had hoped for. "Mike H" wrote: Hi, To get a static time right click your worksheet, view code and paste this code in. Any entry in column A makes column B populate with a static NOW Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False With Target.Offset(, 1) .Value = Now .NumberFormat = ("dd/mm/yyyy hh:mm") End With Application.EnableEvents = True End If End Sub Mike "JR Hester" wrote: xecel 2007 on WindowsXP I need to time stamp entries in a spreadsheet. I thought the NOW() function would do teh trick, but alas, I was mistaken. In column "B" I used the formula =if(A#0, NOW(), "") which worked great, until I moved to the next line and made an entry into column A, that is when the both entries in column B were updated to the current time. Is there a way to record the time a cell changes from empty to non empty status, without updating every other cell in the column? We are simply attempting to automate a call log-- shouldn't be this difficult. Anyone have a solution for my little mind twister today Thanks in advance for the fantatsic help this forum provides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Recording elapsed time | New Users to Excel | |||
recording a data entry date | Excel Worksheet Functions | |||
Macro recording problem. | Excel Worksheet Functions | |||
recording negative time | Excel Discussion (Misc queries) | |||
Recording time in Excel | Excel Discussion (Misc queries) |