[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I need a formula

Posted on 2011-10-13
10
Medium Priority
?
235 Views
Last Modified: 2012-05-12
I need a formula in column B to look at column A's data. If the corresponding cell in Column A is changed in any way, it replaces the date that is in the Cell in Column B. Can this be done with a formula or is a macro going to be necessary?

See the attached for an example.
10-13-11-Date-Change.xlsx
0
Comment
Question by:wrt1mea
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 36964991
Hi, I'm thinking that a formula in column B 'watching' a cell in column A waiting for it to change will create a circular reference. So you will need to write a macro for this.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 36965007
I placed an additional row in D with this formula:  =DATE(YEAR($B$1), MONTH($B$1), DAY($B$1)+A1-$A$1)

Probably not the best method though. And it will change the month when necessary.
0
 
LVL 81

Expert Comment

by:byundt
ID: 36965021
You can manually time-stamp a cell by holding the Control and Shift keys down and hitting the colon key (CTRL Shift :). You can manually date stamp a cell by holding the Control key down and hitting the semicolon ; (CTRL ;). The results will be formatted using the default short time and date format for your locale.

If you want the time-stamping to be automatic, by far the best approach uses VBA code. Here is a macro that must be installed in the code pane for the worksheet being watched. Since a lot of people want to time stamp more than one cell, I generalized the code to put the time stamp in column B next to the cell in column A that was changed. If column A is blanked, then so too is column B. The code is restricted to watching cells A1:A10, but that is easily changed in the place indicated. Note that you'll need to format the cells that will be time-stamped to display the time/date as you wish.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range, cel As Range
Set targ = Intersect([A1:A10], Target)         'Change the reference to A1:A10 to the actual range you want to watch for changes
If targ Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each cel In targ.Cells
    If cel = "" Then
        cel.Offset(0, 1).ClearContents
    Else
        cel.Offset(0, 1) = Now()
    End If
Next
Application.EnableEvents = True
End Sub


Another approach puts the time-stamp in your cell when you double-click it. This sub also goes in the worksheet code pane.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim targ As Range, cel As Range
Set targ = Intersect([B1:B10], Target)      'Change the reference to B1:B10 to the actual range you want to time-stamp
If targ Is Nothing Then Exit Sub

For Each cel In targ.Cells
    cel = Now()
Next
Cancel = True
End Sub


I don't recommend it at all--but if you simply must have a formula for this purpose (rather than the recommended VBA code), then you'll need to turn on iterations (using the Tools...Options...Calculation menu item) with a maximum of 1 iteration. So too will anyone you give the workbook to--they'll get a circular reference error message if they don't. You would then use a formula like:
=IF(A2="","",IF(B2="",NOW(),B2))           formula that time-stamps B2 when cell A2 has data entered
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 81

Expert Comment

by:byundt
ID: 36965032
If you use the macro (as I strongly recommend), you will need to format column B with the desired date format.
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 36965124
Byundt..Thanks for the strong hint as I am going the way of the macro. How could I include two columns to watch for changes? If I wanted to watch Column D or Column F, then it Date/Time stamps Column !?
0
 

Expert Comment

by:lohi
ID: 36965235
Hi
We need to write macro then only we can see answer other column.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 1000 total points
ID: 36965353
wrt1mea,

Please see attached. The macro only looks at cells in the sheet's UsedRange (otherwise something like deleting a column may have dramatic effects!).

Code is...
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xTarget As Range
Dim xCell As Range
Dim xChange As Range

Set xTarget = Intersect(Target, Union(Range("D:D"), Range("F:F")), ActiveSheet.UsedRange)
If xTarget Is Nothing Then Exit Sub

Application.EnableEvents = False
    For Each xCell In xTarget
        If xChange Is Nothing Then
            Set xChange = Cells(xCell.Row, 2)
        Else
            Set xChange = Union(xChange, Cells(xCell.Row, 2))
        End If
        xChange = Format(Now(), "mm/dd/yyyy")
    Next
Application.EnableEvents = True

End Sub

Open in new window


Regards,
Brian. 10-13-11-Date-Change-V2.xlsm
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 1000 total points
ID: 36965357
The code below watches cells D1:D10 and F1:F10 for user changes in those cells. If any occur, it date stamps column I in the same row. The date stamp is removed if a cell in column D or F is cleared.  This macro must be placed in the worksheet code pane, and won't work at all if you put it anywhere else.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range, cel As Range
Set targ = Intersect(Union(Range("D1:D10"), Range("F1:F10")), Target)        'Change the reference to D1:D10 and F1:F10 to your desired ranges

Application.EnableEvents = False
For Each cel In targ.Cells
    If cel = "" Then
        cel.EntireRow.Range("I1").ClearContents         'Clear the date in column I, same row
    Else
        cel.EntireRow.Range("I1") = Date                     'Add a date in column I, same row
    End If
Next
Application.EnableEvents = True
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 36965409
Works Great!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36965500
Thanks, wrt1mea!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question