cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jboyes1
Regular Visitor

Format Date Time Condition for Multiple Excel Fields

I have a scheduled flow which I want to search multiple date fields in an Excel table each day to see if today is 3 days before the date in the fields, and if yes, to send me an email based on some other field conditions.

 

I've got as far as scheduling the flow and getting the items from the table (with ISO8601 formatting), but with regards to comparing the times, when this flow runs the condition returns a 'false' output, even though the date in the field is set to 3 days from today (today is 13/12/21 and the date in my table is 16/12/21):

 

formatDateTime(item()?['Start date'], 'yyyy-MM-dd')
is equal to
formatdatetime(addDays(utcNow(), -3), 'yyyy-MM-dd').
 
Can anyone see what I'm missing? I've done this code for just one of my date columns, "Start date", and am hoping to just repeat it with some more conditions to check the other date columns I want a reminder email for.
 
I wonder whether I have to convert the time before this step (although I'm not sure about how to do this for multiple fields as most of the flows I've seen have only done this for one), or whether I need to do something to shorten the time format first as whilst the date is 3 days after today, the time the response was submitted will be different to that of the date that's being searched for (T00:00:00.0000000Z).

 

jboyes1_0-1639395796241.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
jboyes1
Regular Visitor

Thank you so much @fchopo for your help but I think I managed to fix the issue with the same process as above but I simply started a fresh flow from scratch as I'd read that sometimes it won't work when you've been moving things around.

 

I'm going to try and add a few more comparisons in for my other date columns so will return if I get stuck!

View solution in original post

6 REPLIES 6
fchopo
Super User
Super User

Hi @jboyes1 

At what time is your workflow running? utcNow() will return date and time in UTC, you might be in a different time zone, and that could be the reason the values are different.

Regards,

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
jboyes1
Regular Visitor

It's UTC+00:00 - I'm testing the flow by manually running it though. It's also tricky as I can't see what the outputs are for the formulas to see what else might be going wrong.

 

jboyes1_0-1639404979470.png

 

fchopo
Super User
Super User

You should be able to see how dates are coming from Excel. Are the Excel cells formatted as datetime values?

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
jboyes1
Regular Visitor

Here's where I'm at now - I'd had a play around and when I tried running the formulae through their own 'apply to each' command and they seemed to be returning themselves as outputs, so I've tried using Compose to create some outputs and am comparing those instead:

 

Compose shows the time of my Excel field:

formatDateTime(item()?['Start date'], 'yyyy-MM-dd')
Compose 2 formats utcNow to the same format:
formatDateTime(utcNow(), 'yyyy-MM-dd')
Compose 3 adds on 3 days:
addDays(outputs('Compose_2'), 3, 'yyyy-MM-dd')
 
The condition then checks to see if:
outputs('Compose') is equal to outputs('Compose 3').
 
You can see below that even though both Compose and Compose 3 are returning as "2021-12-16", the expression result is still returning as false.

 

jboyes1_1-1639409962904.png

 

fchopo
Super User
Super User

OK!

Finally, let's try to convert those values to integer ones using the ticks function. Try to use the ticks function in both elements and compare the values again. It should work comparing numbers!

ticks(formatDateTime(item()?['Start date'], 'yyyy-MM-dd')) equals ticks(addDays(utcNow(),-3,'yyyy-MM-dd'))

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
jboyes1
Regular Visitor

Thank you so much @fchopo for your help but I think I managed to fix the issue with the same process as above but I simply started a fresh flow from scratch as I'd read that sometimes it won't work when you've been moving things around.

 

I'm going to try and add a few more comparisons in for my other date columns so will return if I get stuck!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (4,101)