cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thatmattwade
Level: Powered On

Need correct time format from Google Sheets to Delay Until action

I'm trying to take a time listed in a row in Google Sheets and use it as a source for a Delay until action in Flow. Flow originally gave me a format error (it wants everything in YYYY-MM-DDTHH:MM:SSZ format) which I've tried to provide, but it didn't like until I took the input and made it a variable that pulls in as a string (see below). But then it's in the wrong time zone. So I'm trying to convert to UTC, but it's not liking that either.

 

Basically, I just need to know how to input a time, from my time zone (EST), into a Delay until, from a Google Sheet cell. 

image.png

image.pngHere is the Flow with the expression for converting to UTC in the Delay until step.

image.pngHere is the error I'm receiving, which is complaining that I didn't provide the source time zone, even though I did (700, US Eastern).

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Need correct time format from Google Sheets to Delay Until action

Hi @thatmattwade

 

I took this approach to get the correct date/time format for the Delay Until action:

  1. In Google Sheets, I used the NOW() function, which gives me the date/time in the format: 2/18/2018 15:49:42
    DelayUntil-1.png
  2. In the Apply to each step, I added a "Convert time zone" action to convert the Google Sheets date/time field to the same Eastern Time and set the Format string to Round trip date/time pattern. This will output the Google Sheets date/time in the format: 2018-02-18T15:49:42.0000000
    DelayUntil-2.png
  3. In the Delay Until, I used the expression below. This is just concatenating the date/time returned in the "Convert time zone" with a Z at the end, which will give you 2018-02-18T15:49:42Z
    concat(body('Convert_time_zone'),'Z')
    DelayUntil-3.png

Below you can see the whole Flow with the outputs. Please let me know if you have any questions.

DelayUntil-4.png 

 

Regards,

Fausto Capellan, Jr


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


View solution in original post

2 REPLIES 2
Super User
Super User

Re: Need correct time format from Google Sheets to Delay Until action

Hi @thatmattwade

 

I took this approach to get the correct date/time format for the Delay Until action:

  1. In Google Sheets, I used the NOW() function, which gives me the date/time in the format: 2/18/2018 15:49:42
    DelayUntil-1.png
  2. In the Apply to each step, I added a "Convert time zone" action to convert the Google Sheets date/time field to the same Eastern Time and set the Format string to Round trip date/time pattern. This will output the Google Sheets date/time in the format: 2018-02-18T15:49:42.0000000
    DelayUntil-2.png
  3. In the Delay Until, I used the expression below. This is just concatenating the date/time returned in the "Convert time zone" with a Z at the end, which will give you 2018-02-18T15:49:42Z
    concat(body('Convert_time_zone'),'Z')
    DelayUntil-3.png

Below you can see the whole Flow with the outputs. Please let me know if you have any questions.

DelayUntil-4.png 

 

Regards,

Fausto Capellan, Jr


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


View solution in original post

thatmattwade
Level: Powered On

Re: Need correct time format from Google Sheets to Delay Until action

That worked perfectly, thanks @faustocapellanj! One minor detail I'm trying to sort out. Time zones are the bain of my existence in Office 365. I'm unable to get the time zone in GSheets to align with Flow. It will only post at the 'correct' time if I schedule the time for five hours ahead (I'm EST, that would make it UTC). I've tried doing both "convert from EST to UTC" and "convert from UTC to EST", but neither work (the former gives me an immediate result, since the time in UTC has already occurred and the latter throws an error). Any ideas how to make it so the input time in GSheets is actually the time the action is delayed until in my time zone?

Helpful resources

Announcements
firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (6,187)