Date logic not working with my workflow

(0) Share

Report

Posted on by riti17

Hi All,

@ManishSolankiand Community helpers,

I am new to Power Automate and I am stuck with a problem here where I want to compare a simple date from an excel with now

This is my sample data source

1) Part 1 is fine where Feedback = N

2) But as you can see today is 29th of Jan and date over there is 30th of jan

Regards,

Ritesh

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

Chriddle 8,441 Super User 2025 Season 2 on at

Like (1)

Report

Have you set the DateTime Format in the Excel connector to ISO-8601?

If so, can you show the values in the flow run that were compared?

Are you aware that utcNow('yyyy-MM-dd') does not necessarily represent the date in your time zone?

Was this reply helpful? Yes No
riti17 57 on at

Like (0)

Report

hi @Chriddle
Thanks for your reply, I am using google sheet connector and I don't see this
Or, I need to check somewhere else ?

Yes, I am aware and I don't think that is the issue

I have used the date values in the body of the email as below

This should fail but it is passing

and I am getting the email when I am not suppose to.

Date formats are different , Is this the reason and how can we sync the format ? I was not able to do that with the formatdatetime function

Regards,
Ritesh

Was this reply helpful? Yes No
Verified answer

Chriddle 8,441 Super User 2025 Season 2 on at

Like (0)

Report
You are right, it's the date format.

You compare '02/29/2024' with '2024-01-30' and since dates are strings and therefore compared from left to right, the value from the sheet is always smaller.

Use parseDateTime() to convert to ISO-8601 and formatDateTime() to exclude the time part before comparing to utcNow('yyyy-MM-dd'):

formatDateTime( parseDateTime('02/29/2024'), 'yyyy-MM-dd' )

In general it's a good idea to use parseDateTime() if the connector does not provide ISO-8601 formatted datetimes.

However, here, isn't it possible to set the sheet's column type to "Date" and have the connector provide correctly formatted date strings? (I have no experience with Google connectors, but that sounds reasonable to me.)

Was this reply helpful? Yes No
riti17 57 on at

Like (0)

Report

hi @Chriddle

Your suggestion was quite useful and I am accepting your solution.

I just had one follow-up question on this,
I tried the date difference now but getting blank in the output,

Date1(working)=
formatDateTime(
parseDateTime(items('Foreach')?['Certifcation_x0020_Date']),
'yyyy-MM-dd'
)

Date2(working)=
formatDateTime(utcNow(),'yyyy-MM-dd')

But when I use
DateDiff=dateDifference(formatDateTime(utcNow(),'yyyy-MM-dd'), formatDateTime(
parseDateTime(items('Foreach')?['Certifcation_x0020_Date']),
'yyyy-MM-dd'))

This is not working, perhaps I will raise a new question

Regards,
Ritesh

Was this reply helpful? Yes No