Date and Time Column - Current Date
28.12.2017
PROBLEM:
This is often important to have a field with current date in it, each time when you open it. E.g. if you want to have some KPI field to generate an indicator related to whether an item is overdue or not. In this case you’d need to compare the date when the item was created/added/planned with the current date.
However, if you create OOTB Date and Time Column and choose to display the current date, it will always display the same date – the current date when the field was created.
So how to achieve the following – automatically refreshed current day values for each list item in order to calculate corresponding KPI?
SOLUTION:
CREATE 2 DateTime Columns and 1 KPI Column. First DateTime column for a current date must be with default value "Today's Date". Second DateTime column for a created date (or any other date for comparison) must be with default value "None".
KPI Column with the formula: =DATEDIF([Created Date],[Today date],"d")
Additionally, you can set up some logic for indicators in KPI. E.g.: if the calculated value is greater or equal 10, then the goals are met if the calculated value is between 2 and 10, we display warning otherwise (less then 2), the goals are not met.
That all works but the “Today Date” column becomes a problem tomorrow. If you come back in then, the date will still show 11/10 and not 11/11, and as such the calcs using it won’t be accurate.
There can be a few solutions for displaying always the current date in the DateTime column. In this article we’d like to offer what we think is the most effective. This is creating a workflow for “today” date-format column.
In SharePoint Designer create workflow with the following settings:
Stage: Stage 1 Set Workflow Status to Initiated then Update item in Current item then Pause for 0 days, 12 hours, 0 minutes then Set Workflow Status to LastUpdated [%Current Item: Modified%]
Transition to stage Go to Stage 1
Current item and [%Current Item: Modified%] must be additionally modified (see screenshot). For Current item – Date Value must be Current date. For [%Current Item: Modified%] – Lookup for String must contain the options: Data source: Current item. Field from source: Modified. Return field as: Short Date.
