cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LeonardoS
New Member

Condition for current time being equal to time in an excel online file

Hi! I am creating a Recurrent flow to send reminders everyday at a specific hour stored as a value in Excel Online.

The reasoning is having a table that based on a column that enables the reminder (list of items = 'On', 'Off'). I have got different recipients, one in each row, and I want to send a reminder at 10:00 AM each day during THEIR time zone. To do this I convert what would be 10:00 AM in their time zone to CEST/CET which is my time zone in a new column in the Excel table. So basically I end up with a table with a name and the hour of my day in which the reminder should be sent.

So:
- List rows in a table
- Apply to each
- Condition
- hh:mm in Column X equals Current hh:mm (no seconds considered as the flow runs every minute of the day)

How do I create this condition of comparing a 'time' (hour+minute - no date) value of an Excel Online value with the current 'time' to trigger an action when match is TRUE?

I've gone for the 

formatDateTime(utcNow(),'h:mm')
is equal to
formatDateTime('My_Column','h:mm')

But it never matches.

Also tried comparing the formatDateTime(utcNow(),'h:mm') expression with the dynamic content of that column after formatting the column value to match this h:mm expression and still never matches. Tried hh:mm too and still no results.
3 REPLIES 3
abm
Super User
Super User

Hi @LeonardoS 

 

To get the hour and minutes you can do the following:

 

For hour and minutes in 24 hour clock

formatDateTime(convertFromUtc(utcNow(),'GMT Standard Time'),'HH:mm')
 
For hour and minutes in 12 hour (AM/PM) clock
formatDateTime(convertFromUtc(utcNow(), 'GMT Standard Time'), 'hh:mm')
 
Above I have used GMT Standard Time to convert to my local time.
 
If you want to convert to your time zone please refer the below website & use the name of time zone.
 
 
You can also do this using convert time zone.
 
image.png
 
Above the result will be as follows:
image.png
 
 
 
 
 

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Hi @abm , thanks for your reply. Based on your input on how to align times from different sources for an 'is equal to' condition, is this correct?

Recurrence =  1 minute
List Rows Present in a Table
Apply to Each > Excel 'Value'

Condition

formatDateTime('<column name>','hh:mm')
is equal to
formatDateTime(convertFromUtc(utcNow(), 'Romance Standard Time'), 'hh:mm')
If YES > Send an email...

Where the values in the column of the Excel files are in 'hh:mm' format and already expressed in Romance Standard Time.

Error message displayed:

InvalidTemplate. Unable to process template language expressions for action 'Condition' at line '1' and column '15180': 'In function 'formatDateTime', the value provided for date time string 'My Column' was not valid. The datetime string must match ISO 8601 format.'.

Time in the Excel file formatted as time: 

1:02 PM

Clock Hour:

1:02 PM

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (972)