Why can I not use iterative calucations in Excel Online. I am building a tracker for updates at my company, and cannot get static time tags for updates to the tracker

Question

I am building an attendance tracker for work and have built out a spreadsheet for projecting and tracking presence at work. I Have a drop down in 5 columns showing employee status for the day, but cannot get the modified date cell to populate the current date when a change is made in any column in that row. I have done a lot of research on the problem, and have found that I could use VBA, but that won't run online, now I find that Iterative calculation has also been removed from the online version. How can I make this work to automatically update the date in one column whenever I make a change in the drop-down options in that row?

https://1drv.ms/x/c/e1be84c2916cabef/EUBou41Z8jhNsAxm1xIW__ABTe4gXsr3mBb9DoYnQaDr1A?e=W3z2QT&nav=MTVfezAwMDAwMDAwLTAwMDEtMDAwMC0wMDAwLTAwMDAwMDAwMDAwMH0

Answer 1

Hi @Jared,

Thank you for posting your question in the Microsoft Q&A forum.

As you know, Excel Online does not support VBA or iterative calculations, which are commonly used in Excel Desktop to create static timestamps when a cell changes. This limitation is why you're unable to automatically populate a "last modified" date when dropdown values are changed in your attendance tracker.

To better assist you, could you please help narrow down the following:

• Is your Excel file stored in OneDrive or SharePoint?
• Do you or your organization have access to Power Automate or Office Scripts?
• Would you prefer a solution that works entirely within Excel Online, or are you open to using Excel Desktop for setup?

In the meantime, I recommend you try the following workaround in Excel Desktop:

1. Excel Online with Data Validation:
   Excel Online’s limitations prevent fully automated timestamping, but you can use Data Validation to create a semi-automated process. I have tested in your link**.**
   1. Set Up a Reference Cell:
      • In a hidden cell (in this case I use R3), enter =NOW() and format it as "mm/dd/yyyy hh:mm" (via Home > Number Format > Custom). Hide column R.
   2. Add Data Validation:
      • Select P3:P100 (your "MODIFIED" range).
      • Go to Data > Data Validation, set Allow: List, Source: =$R$3, and check "In-cell dropdown".
      • Format P3:P100 as "mm/dd/yyyy hh:mm".
   3. Usage:
      • After changing any status in F-L (for example: updating K3), select the dropdown in K3 to insert the current timestamp in P3
      • The timestamp will remain static until you select a new value.
   This method works for changes across F-L and ensures static timestamps in Excel Online.
2. Power Automate (recommended for Excel Online): If your company uses Microsoft 365, Power Automate can automate this. A flow can detect changes in F-K and update N automatically. This requires converting your data to a Table and setting up a flow with the Excel Online (Business) connector. Let me know if you’d like detailed steps or IT assistance.
3. Office Scripts (if available): If your tenant supports Office Scripts, you can write a script to detect changes in dropdowns and update the timestamp column. This can be triggered manually or via Power Automate.

Note: Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.         

Your detailed response will help us diagnose and investigate the issue more efficiently. If I misunderstood what you’re looking for, feel free to let me know or share a screenshot. I’d be happy to help further! 

I'm looking forward for your reply.

---

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. 

Answer 2

This is a truly annoying development. A static timestamp would have been a sensable built-in feature for decades and is a common thing to need in excel sheets. The VBA workaround in the past was fine, it required some deeper knowledge, but this was working.
Now in excel online, where power automate needs a paid additional license if you want to use triggers (for example recognizing a change in a document), office scripts doesn't work for security reasons in a default company setup and iteratice calculations have been removed, this is, from a user perspective, not understandable.
A static timestamp should be as easy as right clicking a cell and selecting it from a context menu or creating a conditional rule.
I don't think I've ever created a table that several people work with that did not have this in it. So - what is up with Microsoft in that regard?