Get current date in SharePoint

[Lel_999987]

Hi all,

how do I get current date which is updated dynamically when a new day starts?

E.g I have a date column and a status column. 
I want to set the status to  "not due", "overdue" or "due" depending on the date column and the status column should provide filter functionality. 
As I must learned a calculated column did not work because the today() function will only updating when the item has changed. 

Could I do it somehow in PowerApps Form or is there some other way native in Sharepoint?
For some reassons Power Automate is not an option. 

[#2]

@Lel_999987 

To achieve a dynamic "not due," "overdue," or "due" status based on the current date in SharePoint Online without using Power Automate, you can use JavaScript in SharePoint's column formatting feature. Here is a step-by-step guide:

1. Create a Choice Column for Status:
• Create a new choice column (e.g., "Status") in your SharePoint list where you want to track the statuses.
2. Add Date Column:
• Ensure you have a date column (e.g., "Due Date") in the list that contains the date to which you want to compare the current date.
3. Update Column Formatting:

• Go to your SharePoint list.
• Click on the gear icon (settings) in the upper-right corner.
• Choose "List settings."

• Under "Columns," click on the name of the "Status" column.
1. Edit Column Formatting:

• Scroll down to the "Column Formatting" section.

• Click on the "Edit" button.
1. Enter JavaScript Code:
• In the "Format column" pane, you can enter JavaScript code to dynamically set the "Status" column value based on the "Due Date" column and the current date. Here is a sample code snippet:

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "children": [
      {
         "elmType": "span",
         "style": {
            "color": "=if([$DueDate] <= @now, 'red', if([$DueDate] < @now + 3, 'orange', 'green'))"
         },
         "txtContent": "=if([$DueDate] <= @now, 'Overdue', if([$DueDate] < @now + 3, 'Due Soon', 'Not Due'))"
      }
   ]
}

This code checks if the "Due Date" is less than or equal to the current date (@now). If so, it sets the text to "Overdue" and the color to red. If the "Due Date" is within the next 3 days, it sets the text to "Due Soon" and the color to orange. Otherwise, it sets the text to "Not Due" and the color to green.

1. Save and Apply Formatting:

• Click the "Save" button.
• Click the "OK" button to apply the column formatting.

Now, when you view your SharePoint list, the "Status" column will dynamically update based on the "Due Date" column and the current date. Please note that this approach relies on client-side rendering, so it will work for views in SharePoint Online. However, users may need to refresh the page to see the status update as the date changes.The text and steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

[#3]

Hi @NikolinoDE ,
thanks for your fast reply.
I forget to mention, that the status column shoud be able to filter the results and I guess your approach will not provide filter functionality, right?

[#4]

@Lel_999987 

You are correct, approach will not provide filter functionality, was not a feature requested in the first message from you.The approach I provided using column formatting will not allow you to filter the results based on the dynamically calculated status. Column formatting is primarily for changing the visual representation of data within a column, and it does not affect the underlying data or its filtering capabilities.

If you need to filter the results based on the "not due," "overdue," or "due" status, you may need to consider alternative methods:

1. Calculated Column (with Limitations): As you mentioned earlier, calculated columns have limitations in terms of when they update. However, if the limitation is acceptable for your use case (i.e., you do not need real-time updates), you can use a calculated column to determine the status based on the "Due Date." Then, you can use SharePoint's built-in filtering capabilities on this calculated column.
2. Custom Solutions (Development): If you have access to SharePoint development capabilities, you can create a custom solution using SharePoint Framework (SPFx) or other development tools. This would allow you to implement dynamic filtering and real-time status updates based on the current date.
3. PowerApps: Since you mentioned PowerApps, it is worth exploring this option further. PowerApps can be used to create custom forms and apps for SharePoint lists and can provide dynamic filtering and calculated field capabilities. It may be possible to achieve your desired functionality using PowerApps.
4. Third-Party Tools: There are third-party SharePoint add-ins and tools available that can extend SharePoint's functionality, including filtering and dynamic status updates. Some of these tools may offer the features you need out of the box.

Ultimately, the choice of method will depend on your specific requirements, technical capabilities, and constraints within your SharePoint environment. Consider the trade-offs and limitations of each approach to determine the best fit for your needs. The text and steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants