SOLVED

Filter on today's date in Sharepoint List doesn't work

Copper Contributor

Dear Community,

I have a SharePoint list with gallery view. In that view i have set a filter. In my column "Geboortedatum" i have set a date. But my filter which is set to "Geboortedatum" is equal to "[Today] doesn;t work. The column "Geboortedatum" is a type DATE/TIME column and is set to a long format. 

 

Does anyone know what the problem could be?

 

Gr. P

6 Replies

@Peki72 I just tried creating a new SharePoint list and list view based on [Today] filter on Date and Time column and it is working fine for me.

 

Here are the list view settings and all data (all items view) and filtered data (Today items view):

All items view

ganeshsanap_1-1692362248642.pngToday items view - Settings

ganeshsanap_2-1692362290085.pngToday items view - Output

ganeshsanap_3-1692362329009.png


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Hey!

Ik heb hetzelfde probleem en ik vraag me af of dit komt door de vertaling van de term [Today]?.

I have the same problem and I wonder if it's a bad translation of the term [Today] towards Dutch?

 

Is there already a solution?

 

Kind regards,
Ashley

best response confirmed by Peki72 (Copper Contributor)
Solution

@Ashley_Vdk As per the Microsoft documentation, it is correct: 

ganeshsanap_0-1704432760277.pngTry using [VANDAAG] instead of [TODAY].


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Hi @ganeshsanap , 

 

I did use the correct language, but it does not work.

Ashley_Vdk_0-1704461625981.png


I get the error that it is not possible to use "or" with an indexed column (error A), but if I remove the index on the column I get the error that I need an indexed column because my list is more than 5000 items (error B). Although my view is not more than 5000. 

If I click on "ok" when I get error A, then I see that the filtering with [Vandaag] did not work and the filtering on my column Soort (PW) did work.

I tried a lot of things, but I don't quite understand what I'm doing wrong. I also don't find the correct information online, mostly the help video's or websites don't use indexed columns or have a test library of 5 or so documents.

It seems there are some existing BLANK values in the column, if so you can fill the "Blanks" with some default values and then apply the filter.

 

Using Power Automate:

  1. Create a Flow:

    • Open Power Automate and create a new automated flow triggered manually or based on your requirements.
  2. Add Actions to the Flow:

    • Use the "Get items" action to retrieve items from the SharePoint list based on a condition. For example, you might retrieve items where the Date/Time column is empty.
  3. Apply Defaults to Blank Values:

    • Use the "Apply to each" control to loop through the retrieved items.
    • Inside the loop, use a condition to check if the Date/Time column is empty.
    • For the items where the Date/Time column is empty, use the "Update item" action to set a default date/time value.
  4. Set Default Value:

    • In the "Update item" action, specify the default date/time value you want to assign to the Date/Time column for those items.
  5. Save and Test:

    • Save the flow and manually trigger it or set it to run automatically based on your workflow requirements.
    • Test the flow to ensure it updates the Date/Time column for items where the value is blank.

 

PowerShell Scripting (for advanced users):

Alternatively, you can use PowerShell scripting to achieve a similar result by connecting to SharePoint Online using the SharePoint Online Management Shell:

 

# Connect to SharePoint Online
Connect-SPOService -Url "https://yourtenant.sharepoint.com"

# Get the specific list
$list = Get-SPOList -Identity "YourListName"

# Retrieve items where Date/Time column is blank
$items = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
$context.Load($items)
$context.ExecuteQuery()

# Loop through items and update blank values with a default date/time
foreach ($item in $items) {
if (-not $item["YourDateTimeColumn"]) {
$item["YourDateTimeColumn"] = Get-Date "YourDefaultDateTimeValue"
$item.Update()
$context.ExecuteQuery()
}
}

 

Replace "YourListName", "YourDateTimeColumn", and "YourDefaultDateTimeValue" with your specific list name, column name, and the default date/time value you want to set.

Always test scripts on a non-production environment before applying changes to your live SharePoint environment to avoid unintended consequences.

 

Hope that helps !

 

Note: [Today] will be remained as [Today], I don't think the language is bilingual as far as I knew.

1 best response

Accepted Solutions
best response confirmed by Peki72 (Copper Contributor)
Solution

@Ashley_Vdk As per the Microsoft documentation, it is correct: 

ganeshsanap_0-1704432760277.pngTry using [VANDAAG] instead of [TODAY].


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

View solution in original post