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

Scheduling a refresh of volatile function in Excel Online sheet

Hi there,

 

Currently I am pretty far into building a Power Automate Flow that takes recurring tasks from an Excel Online sheet and lists them into MS Planner.

 

Every business day, the daily tasks are listed using the start and end date from the Excel Online sheet. These dates are dynamic using volatile functions such as Today(). When I test the flow during the day it works just fine. However, when I schedule it to run in the morning, it lists the tasks with yesterday's date. This seems to be due to the volatile functions that are only updated upon entering the Excel online sheet.

 

Has anyone encountered this issue before? How did you fix this?

 

Thank you in advance!

 

4 REPLIES 4
ChristianAbata
Super User II
Super User II

hi @modifylol  in my experience this is because when you use expression like this. formatDateTime(utcNow(),'yyyy-MM-dd')  you are showing de date in UTC format so maeby for your cuntry the utc starts a day at the meddle of your day so the date in planner is different.

To enshure that my date don't change for my UTC I use a function with hours include like this formatDateTime(utcNow(),'yyyy-MM-ddTHH:mm:ss') inside an action called convert date time then I'm able to change the utc to the correct hour in the day that means I'm actually in my day becouse is for example 9:00:00am 

 

I hope I can explain better... basically you need an hour jejej



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA

Hi @ChristianAbata,

 

Thank you for your response, and sorry for my delay.

Since I am in Western-EU, UTC is -2hrs. I have scheduled the listing of the planner task to be at 9 am (UTC = 7am), so I don't think that this is the issue. My problem is that I can't seem to refresh my Excel online file without opening the sheet manually. I have tried to add, update, and delete rows using flows but it does not update the date value (volatile formula in Excel online).

 

 

Any update on this issue will be greatly appreciated. I have the same requirement and cannot find a way through.

Hi AJ1411,

 

The previous comment by someone else had a lot to do with my solution despite my comment saying it is not the problem.

 

In my company's IT system the day is set to start at 9 am. If I were to use a formula such as =Today() at this minute (8:20 am) it would return the 12th of July rather than the 13th. I'd make sure to check how it is in your case before proceeding.

 

Let me know!

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Users online (34,918)