You’re offline. This is a read only version of the page.
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).
Column settings:
Unfortunately I made this column in the list a text column, and I am having trouble filtering all tasks completed in the past month.
The below does not work - I think it is because the column is text, contains the time, as well as the different date format of dd/mm/yyyy as showing in the test flow run:
First expression used in this filter query is formatDateTime(startOfMonth(getPastTime(1,'Month')),'MM-dd-yyyy')
The second expression used is formatDateTime(startOfMonth(utcNow()),'MM-dd-yyyy')
(sorry if you end up answering my queries again @grantjenkins 😂)
@v-chengfen-msft Sorry I need your help again - I thought the above fixed it, but it is still pulling data from the incorrect year. It pulling dates from the right month, but both in 2021 and 2022. The formatting has not changed in the column on the same list so I at a loss!
Solved my own query - the date in the text column was formatted 1/10/22 (not with hyphens!)
I changed the expression to the following which worked:
formatDateTime(startOfMonth(getPastTime(1,'Month')),'MM/dd/yyyy 00:00:00')
I've managed to hard code the time in so it searches from midnight to midnight using the expressions:
formatDateTime(startOfMonth(getPastTime(1,'Month')),'MM-dd-yyyy 00:00:00')
formatDateTime(startOfMonth(utcNow()),'MM-dd-yyyy 00:00:00')
Though some reason the results (this also happened before I changed the expression) it brings back are items from that column from the completely different month as well as year!
Hi @v-chengfen-msft ah wonderful - so simple when the solution is shown to me! Thank you!
Did you do the 'compose' step just to show me the output in your example?
Hi @roxy1 ,
Please try :
First expression used in this filter query formatDateTime(startOfMonth(getPastTime(1,'Month')),'MM-dd-yyyy hh:MM:ss')
The second expression used is formatDateTime(startOfMonth(utcNow()),'MM-dd-yyyy hh:MM:ss')
Here is test for your reference:
Here is result:
Best Regards
Cheng Feng
Share
Report
195
All responses (
Answers (