Incorrectly brings back dates from two years: Filtering a a text column containing 'date and time' in a SharePoint List

(0) Share

Report

Posted on by roxy1

195

Hi,

I currently have a SharePoint list and when a task is completed on a corresponding Planner, it updates the column 'Task Completed' with the date and time, the date is in format DD/MM/YYYY which is due to our location, the time is UTC time I believe (another flow get's the information from the 'Completed Date Time' of the task). Unfortunately I made this column in the list a text column, and I am having trouble filtering all tasks completed in the past month.

Column settings:

My expressions in the filter query are the following :

formatDateTime(startOfMonth(getPastTime(1,'Month')),'MM/dd/yyyy HH:MM:ss')

formatDateTime(startOfMonth(utcNow()),'MM/dd/yyyy HH:MM:ss')

Unfortunately this does bring back the right days and months, but is bringing back both 2022 and 2021 results (when I only wanted this current year!). Can anyone help? Thank you!

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Sort by

CFernandes 8,504 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hey @roxy1

Can you try the Filter Query below -

Created gt '@{addDays(utcNow('yyyy-MM-ddTHH:mm:ssZ'),-30)}'

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.

Was this reply helpful? Yes No
roxy1 195 on at

Like
a
(0)

Report
Copy link

Link copied!

HI @CFernandes thanks for the speedy reply! I've tried your suggestion, however, it now brings back no values at all. I think this because the Task Completed date and time is not in this format in the SharePoint list.

Was this reply helpful? Yes No
CFernandes 8,504 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @roxy1 ,

Can you perform the test below -

Try with Created Column and see if it works - If it works.

Change the Date Format 'MM/dd/yyyy HH:MM:ss' in your expression - the expression should be the below

'@{addDays(utcNow('MM/dd/yyyy HH:MM:ss'),-30)}'

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

A simple solution is to just change your Task Completed column from Single line of text to Date and Time.

However, I would DEFINITELY test this on another list with the same formatting, etc. to confirm you don't get any data loss due to incorrect date formats already entered. You would also need to test the process that updates the SharePoint list from Planner to ensure it would still work as expected.

I did a quick test in my site and have DD/MM/YYYY format and regional settings set to Australia, and all worked as expected.

Benefits are you can use the actual dates in Power Automate and you also get proper Date sorting/filtering on the column/list.

Was this reply helpful? Yes No
roxy1 195 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi Grant,

I've been thinking about doing this too, and have read a lot about data loss. I will try this on a test list!

Was this reply helpful? Yes No
roxy1 195 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

So sorry this did not work either. I've had to change my column setting to 'Date and Time' unfortunately with some date loss I've had to manually enter again.

Thanks for your suggestions though!

Was this reply helpful? Yes No
roxy1 195 on at

Like
a
(0)

Report
Copy link

Link copied!

I've had to change my column setting to 'Date and Time' as it was the only solution. Unfortunately with some date loss I've had to manually enter again!

Was this reply helpful? Yes No