LAST DATE MODIFIED (CELL LEVEL)

[COGICPENNY]

Trying to create a formula or code that will show the following:

1. Date created (COLUMN A)
2. Date modified (COLUMN C)

This file has a lot of people working within it. Need granularity in when a new row is added (COLUMN A), and when the NOTES/COMMENTS (COLUMN AA) was last updated reflected in column C.

The tab name is: PUCV PARTNERS NJ 12142021
https://powerchangeslives-my.sharepoint ... A?e=yHGFqT

Thanks so much,
Penny

[DocAElstein]

Hello Penny
I can’t seem to do much with that file link you gave. Sometimes it doesn’t work at all, and when it does, I don’t see any way to download a file. Is it Web Office Excel? If so, I think that does not support coding.
Usually some sort of Event coding would be used to do what you want. I don’t think you can do those sorts of things with a formula.

Alan

[HansV]

Unfortunately, Excel does not have an event that occurs when a row is added, and the methods that simulate such an event are tricky.
Is there a specific column that will ALWAYS be filled in first when a new row is added?

[COGICPENNY]

The new route added create date is the least important, we can always manually enter the create date for the new contact (row),, but what's most important is the last time the NOTES/COMMENTS were updated that would be reflected in the LAST UPDATED field. I need a way to track the progress of the file since they're supposed to update the NOTES/COMMENTS each time they interact with a contact. A it is now, I have to manually scroll and read several lines within the NOTES/COMMENTS cell just to see that their last update wth that contact was over a month ago. Looking for a formula that will show me quickly this information. Hope this makes sense.

[HansV]

A formula won't work for this, you need VBA. If the workbook is shared, that would not be an option, sadly.

Right-click the sheet tab.
Select View Code from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Instruct all users to allow macros when they open the workbook.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("AA2:AA" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Intersect(Range("AA2:AA" & Rows.Count), Target).Offset(0, -24).Value = Date
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

[COGICPENNY]

Please clarify what is meant by "shared"? It's on our SharePoint system which means everyone has access simultaneously to the file. If that's what you mean by share and it won't work, are there any other options for us even if it's a paid add-in?

[HansV]

I'd test it in your SharePoint environment - hopefully it'll work but I cannot test it myself.

[COGICPENNY]

Hi Hans,
Just getting back to this after being away for a bit. The formula you provided still didn't tell me how to add it into the worksheet. I need to find the last time the cell "AG" (Notes/Comments) was updated. I created a column DATE UPDATED right next to it so that the result can be placed there. Here is the file link: https://1drv.ms/x/s!AsyIxS27y-3ThNprZhU ... w?e=CinXeL

Please put the code in and update the cell, because I can't figure it out.

Thanks,
Penny

[HansV]

To update the cell(s) to the left, the code needs to use .Offset(0, -1) instead of .Offset(0, -24).
See this version. I have added a comment in AG7.

[COGICPENNY]

Okay, first, let me just tell you that "YOU ARE A PURE GENIUS"!!!! This is going to help significantly. Now that I can see it works, few minor adjustments and questions:
1. I really wanted this to go in "COLUMN C" right after "ENTERED BY" so that it would be "DATE ADDED", "ENTERED BY", "LAST UPDATE" (changed the name so that it's exactly as it will appear in my pivot table). This will let me know the last time the NOTES/COMMENTS were updated. If it can be done, please move the column, otherwise we'll just have to use what you provided.
2. I need it to have the time of day as well (12 hour day, not military format) so it would be: 04/01/2022 08:28 AM. When I tried to simply reformat the cell using the DATE/TIME formatting it comes up at 12:00 AM for today's date which isn't accurate.
3. If I have the "LAST UPDATE" in date and time format, when I do my pivot table can I still change the data format to only show the date? When I run my productivity report it will be across by dates, not full date/time, but for the spreadsheet view I want to see the date AND time it was last updated.

Here's the link to your updates: https://1drv.ms/x/s!AsyIxS27y-3ThNprZhU ... w?e=DQMfR3

Thanks so much,
Penny

[HansV]

See new version

In your pivot table, right-click any date/time in the LAST UPDATE field.
Select Group... from the context menu.
Select only Days and click OK.

[COGICPENNY]

Didn't test the pivot table yet, but so far the LAST UPDATE looks to work perfectly fine. Thanks so much!