Answered

Format Date Time Condition for Multiple Excel Fields

(0) Share

Report

Posted on by jboyes1

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).

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

fchopo 8,003 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
jboyes1 13 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
jboyes1 13 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report
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

Was this reply helpful? Yes No
Verified answer

jboyes1 13 on at

Like (0)

Report

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!

Was this reply helpful? Yes No