Forum Discussion
Solved
Enter date when cell updated
I've got a spreadsheet that we're using as an action tracker.
I want to automatically update a cell to show the date an issue was raised.
I know this can be done with VBA or using the following ( the formula is in cell C28, so a circular reference)
=IF(B28<>"",IF(C28="",TODAY(),C28),"")
The formula requires the enabling of circular references. Neither this nor VBA work when accessing via SharePoint, yet this is where we are storing the spreadsheet as it needs to be shareable with an external customer.
Is there another way to perform this seemingly simple task?
I understand the limitation you are facing with Excel for the web and SharePoint regarding circular references and dynamic updates without VBA. Unfortunately, Excel formulas alone cannot dynamically update the date without introducing a circular reference or using VBA. The circular reference issue is a limitation of Excel for web and SharePoint, preventing formulas from referring back to themselves. While VBA can provide a solution, it requires scripting expertise and is not user-friendly for shared spreadsheets. Given these constraints, let us explore an alternative approach that involves using a combination of data validation and helper columns:
1. Helper Column for Initial Entry Date:
- Create a new column (let's say column D) and use the following formula in cell D28:
=IF(B28<>"", IF(D28="", TODAY(), D28), "")
- This formula checks if cell B28 is not blank and if cell D28 is also blank. If both conditions are met, it sets the date to the current date using TODAY(). If either condition is not met, it leaves the existing date in place.
2. Data Validation to Lock Date:
- Select cell B28.
- Go to the "Data" tab.
- Click "Data Validation."
- Under the "Settings" tab:
- Allow: Choose "Date."
- Criteria: Choose "Greater than or equal to."
- Start Date: Choose a reasonable date range that covers your expected entry period.
- Under the "Error Alert" tab, you can set an error message to instruct users that the date is now locked.
- Apply data validation to cell B28 to prevent further changes once a date is entered:
This way, users can enter a date in cell B28 only once, and the corresponding date in column D will be set at that time. The data validation in cell B28 will prevent further changes.
Please test this approach in your specific setup to ensure it meets your requirements. Keep in mind that users can potentially bypass data validation rules by copying and pasting values, so it's essential to communicate the intended usage guidelines to the users of the spreadsheet.
Alternative, to achieve dynamic date updating without circular references or VBA, you need to consider external applications like 3rt party AddIns or services that can communicate with Excel and update the date accordingly.
Given your constraints (the use of SharePoint and the limitation on circular references), you can achieve a similar result without using a circular reference or VBA.
Here is an alternative approach:
1. Using an Auxiliary Column:
- Instead of having the date in the same cell as the formula, you can use an auxiliary column to store the date.
- For example, if you want the date to be stored in column D, you can use the following formula in cell D28:
=IF(B28<>"", IF(D28="", TODAY(), D28), "")
- This way, the date is stored in a separate column and does not create a circular reference.
2. Conditional Formatting to Hide Dates:
- You can use conditional formatting to hide the dates in column D if the corresponding cell in column B is blank.
- Select column D.
- Go to "Home" > "Conditional Formatting" > "New Rule."
- Use a formula to determine which cells to format, and enter the following formula:
=$B28=""
- Set the formatting options to hide the dates (e.g., set the font color to match the cell background).
This way, the date will only be visible when there is an entry in the corresponding cell in column B. This method does not rely on circular references or VBA, and it should work well in SharePoint or other online platforms.
Remember to adjust the cell references and formatting options based on your specific spreadsheet layout and requirements. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Hi NikolinoDE, thanks for the reply. I'm confused by your first answer though. Haven't you just moved what I've done from column C to column D? And therefore cell D28 now contains the circular reference?
If you're meaning to reference a separate cell, won't that separate cell always be blank and therefore the cell with the formula will, if B28 is not blank, always evaluate "TODAY()" and therefore update every time I open the sheet, rather than only when B28 is first filled in?
Sorry if I'm missing something here, but this is the problem I've been trying to solve.
I understand the limitation you are facing with Excel for the web and SharePoint regarding circular references and dynamic updates without VBA. Unfortunately, Excel formulas alone cannot dynamically update the date without introducing a circular reference or using VBA. The circular reference issue is a limitation of Excel for web and SharePoint, preventing formulas from referring back to themselves. While VBA can provide a solution, it requires scripting expertise and is not user-friendly for shared spreadsheets. Given these constraints, let us explore an alternative approach that involves using a combination of data validation and helper columns:
1. Helper Column for Initial Entry Date:
- Create a new column (let's say column D) and use the following formula in cell D28:
=IF(B28<>"", IF(D28="", TODAY(), D28), "")
- This formula checks if cell B28 is not blank and if cell D28 is also blank. If both conditions are met, it sets the date to the current date using TODAY(). If either condition is not met, it leaves the existing date in place.
2. Data Validation to Lock Date:
- Select cell B28.
- Go to the "Data" tab.
- Click "Data Validation."
- Under the "Settings" tab:
- Allow: Choose "Date."
- Criteria: Choose "Greater than or equal to."
- Start Date: Choose a reasonable date range that covers your expected entry period.
- Under the "Error Alert" tab, you can set an error message to instruct users that the date is now locked.
- Apply data validation to cell B28 to prevent further changes once a date is entered:
This way, users can enter a date in cell B28 only once, and the corresponding date in column D will be set at that time. The data validation in cell B28 will prevent further changes.
Please test this approach in your specific setup to ensure it meets your requirements. Keep in mind that users can potentially bypass data validation rules by copying and pasting values, so it's essential to communicate the intended usage guidelines to the users of the spreadsheet.
Alternative, to achieve dynamic date updating without circular references or VBA, you need to consider external applications like 3rt party AddIns or services that can communicate with Excel and update the date accordingly.
