I am inquiring if there is an excel function or office script that would give me date and time that an individual cell was last modified in excel web.

Shadowsing 0 Reputation points
2024-01-23T07:29:12.8833333+00:00

Hi, I am wondering if there is an excel function that would give me a date (and time) that an individual cell was last modified. Also, if it is possible via Office script?  


For example: If there is any change in say column B, then corresponding cell in column A should capture when that change was made.

Cell B2 has an entry "NO" it is changed to some other value say "yes" then I'd like cell A2 to give me the time stamp for when that happens. Post modification, I'd like the A2 cell to read "23/01/24 at 12:54pm", i.e when the change was made (format is not important). I know it is possible via VBA but users (30+) will be opening this workbook via sharepoint on excel web where VBAs don't work. I have no knowledge of Office Scripts. Could anyone please help with this?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,694 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,300 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Jiajing Hua-MFST 7,705 Reputation points Microsoft Vendor
    2024-01-24T07:07:21.15+00:00

    Hi @Shadowsing

    If there is any change in say column B, then corresponding cell in column A should capture when that change was made

    If you do not want use VBA, here is a workaround, please have a check.

    • Go to File > Options > Formulas > Tick the box of "Enable iterative calculation".
    • Select the cells in Column A, set the Date format.
    • Add an auxiliary column, such as column C. You can hide it later.
    • Enter the formula =IF(OR(B2="",A2=""),"",IF(A2=NOW(),B2,C2)) in C2, enter the formula =IF(OR(A2="",B2<>C2),NOW(),A2) in A2.

    User's image


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.