Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date-time updates every cell?
I have a worksheet that has a formula in all cells in column H that says if there is a comment in column G then put the current date/time in H(current cell). My formula looks like the following in H5; =IF(ISBLANK(G5),"",NOW()) The problem is that if I enter a comment in any cell in column G it updates all of my dates in column H. Using Excel 2007 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date-time updates every cell?
The NOW() and TODAY() functions are meant for you to have formulas take action action the current date (and/or date-time), not as a time stamp. You can do what you want using VBA event code. Delete all of your formulas in Column H, right click the tab at the bottom of your worksheet, select View Code from the popup menu that appears and copy/paste the following code into the code window that opened up... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 Then Application.EnableEvents = False If Target.Value < "" Then Target.Offset(, 1).Value = Now Else Target.Offset(, 1).Value = "" End If Application.EnableEvents = True End If End Sub Now, when you go back to your worksheet and enter something in Column G, the date and time will be placed next to it in Column H. As written, if you delete an entry in Column G, the date that was originally displayed for the comment in Column H will be deleted as well. -- Rick (MVP - Excel) "Selah West" wrote in message ... I have a worksheet that has a formula in all cells in column H that says if there is a comment in column G then put the current date/time in H(current cell). My formula looks like the following in H5; =IF(ISBLANK(G5),"",NOW()) The problem is that if I enter a comment in any cell in column G it updates all of my dates in column H. Using Excel 2007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date-time updates every cell? | Excel Worksheet Functions | |||
Overlapping time calculations and automatic time updates | Excel Worksheet Functions | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Worksheet Functions | |||
Continuous Time/Date Updates | Excel Programming |