Forum Discussion
Timestamp issues in SharePoint
Hi. I have a SharePoint site where I have a column named Ready. I have managed to create another conditional column which stamps the time and date when you click Ready as yes. The problem I have is that when you edit the record later the timestamp updates to the current time. Does anyone know how to fix this so only the first date/time is kept?
The formula I'm using for the timestamp is:-
=IF(Ready="Yes",TEXT(NOW()+0.3336,"dd-mm-yyyy hh:mm"),"")
- The issue you're experiencing is because the formula you're using is based on the `NOW()` function, which returns the current date and time. This means that every time the item is edited, the formula will recalculate and update the timestamp to the current date and time.
To fix this, you can modify your formula to use a SharePoint column that stores the original value of the Ready column. Here's an example of how you can modify your formula:
1. Create a new column called "Ready Timestamp" (or any name you prefer) with the data type "Date and Time".
2. Modify your formula to check if the value of the "Ready" column has changed. If it has not changed, then return the existing value of the "Ready Timestamp" column. If it has changed, then update the "Ready Timestamp" column with the current date and time. Here's an example of how the modified formula could look:
=IF(Ready="Yes",IF([Ready]=[Previous Ready],"[Ready Timestamp]",TEXT(NOW()+0.3336,"dd-mm-yyyy hh:mm")),"")
In this formula, "[Previous Ready]" refers to a column that stores the previous value of the "Ready" column. You can create this column by following these steps:
1. Create a new column called "Previous Ready" (or any name you prefer) with the data type "Single line of text".
2. Modify your list view to include this column.
3. Use a SharePoint workflow to copy the value of the "Ready" column to the "Previous Ready" column every time the item is edited.
With this modified formula, the "Ready Timestamp" column will only be updated when the value of the "Ready" column changes, and the original timestamp will be preserved.Deleted
Hi
Thanks for your reply. Apologies, I’m not great on Sharepoint could you advise me a little further? I’m a bit confused as to how many new columns I need and where my formula needs to be.
- My list is as below – My current formula is in the Time flight ready column.
So I’m guessing the Ready Timestamp column you suggest is just the same as my ‘Time flight ready’? And the formula you gave me goes in here?
And thus my updated formula would be =IF(Ready="Yes",IF([Ready]=[Previous Ready],"[Time flight ready]",TEXT(NOW()+0.3336,"dd-mm-yyyy hh:mm")),"")
- Use a SharePoint workflow to copy the value of the "Ready" column to the "Previous Ready" column every time the item is edited. – How do I do this? I can create a new column easy enough.
Thanks in advance
Russell
Your formula is right, now only need to create a SharePoint Designer workflow to update the new Ready timestamp field,
You need to
1. install the SharePoint Designer
2. Connect to your SharePoint Site using designer,
3. Open the list
4. Create new Workflow
5. Add steps to update field
6. Publish Workflow
If you are on SharePoint Online, you can do the same using PowerAutomate.
