individual cell date modified will be marked in another cell
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: individual cell date modified will be marked in another cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question individual cell date modified will be marked in another cell

    I have a spreadsheet on Onedrive for me and my employees to keep track of our machine hrs for maintenance. I use Excel 2013 Pro to set it up.

    I need to be able to have the date that one cell is modified be enter in another cell to the right of that one.

    Say I have column "D",with the machine hrs and column "E" has the date column "D" was modified. I need to have column "E" update automatically when ever a cell in column "D" is modified.

    Example: say D5 is modified to 5,567hrs and E5 will automatically log down (todays date) 07/20/2019. D4 was not modified so E4 stills show 07/13/2019 (last date it was modified).

    I have tried this formula in cell E5 =IF(D5="",THEN,TODAY()) However when I open the page the next day all cells in column E shows the current days date and not the day it was modified.

    Thanks,

    CL1000

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,328
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    14 Thread(s)

    Default Re: individual cell date modified will be marked in another cell

    Put the following code in the events of your sheet.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Count > 1 Then Exit Sub
        If Target.Column = 4 Then Target.Offset(0, 1).Value = Date
    End Sub
    Sheet event
    Right click the tab of the sheet you want this to work, select view code & paste the code into the window that opens up.
    Regards Dante Amor

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,692
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: individual cell date modified will be marked in another cell

    Try this:
    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Modified  7/21/2019  12:58:17 AM  EDT
    If Target.Column = 4 Then
        If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
        Target(1, 2).Value = Date
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: individual cell date modified will be marked in another cell

    Thanks DanteAmor for your reply.

    I'm just a basic user of Excel, I know how to do the simple stuff and can figure out some formulas but after that I have not venture out into other areas like the code you posted. Can you expand your answer to walk me through it from the beginning, you know dummies 101 level?

    I will need to repeat the formula, code or whatever about 30 times for 30 different rows.

    Thanks again,

    CL

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: individual cell date modified will be marked in another cell

    Thanks "My Answer is this" for your reply.

    I'm just a basic user of Excel, I know how to do the simple stuff and can figure out some formulas but after that I have not venture out into other areas like the code you posted. Can you expand your answer to walk me through it from the beginning, you know dummies 101 level?

    I will need to repeat the formula, code or whatever about 30 times for 30 different rows.

    Thanks again,

    CL

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,692
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: individual cell date modified will be marked in another cell

    You will not need to do as you said:
    I will need to repeat the formula, code or whatever about 30 times for 30 different rows.

    This script performs the same task for all cells in column 4

    When ever you enter any value in column 4 todays date will be entered in column 5

    Did you try this script?
    Last edited by My Aswer Is This; Jul 21st, 2019 at 06:55 AM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: individual cell date modified will be marked in another cell

    Quote Originally Posted by My Aswer Is This View Post
    You will not need to do as you said:
    I will need to repeat the formula, code or whatever about 30 times for 30 different rows.

    This script performs the same task for all cells in column 4

    When ever you enter any value in column 4 todays date will be entered in column 5

    Did you try this script?
    No I have not, Sorry but I'm a total dumby with codes... where and how do I even start with a code?

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,692
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: individual cell date modified will be marked in another cell

    I explained in earlier post but here it is again:
    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window

    Then when you enter any value in column 4 the script runs.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,328
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    14 Thread(s)

    Default Re: individual cell date modified will be marked in another cell

    Quote Originally Posted by CL1000 View Post
    Thanks DanteAmor for your reply.

    I'm just a basic user of Excel, I know how to do the simple stuff and can figure out some formulas but after that I have not venture out into other areas like the code you posted. Can you expand your answer to walk me through it from the beginning, you know dummies 101 level?

    I will need to repeat the formula, code or whatever about 30 times for 30 different rows.

    Thanks again,

    CL
    To set the date in the auotmatic and remain permanent it must be with a VBA code. You just put a piece of data in column D and column E is filled in automatically.


    HOW TO INSTALL Event Code
    ------------------------------------
    If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Regards Dante Amor

  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: individual cell date modified will be marked in another cell

    OK, Thanks, sounds easy enough

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •