cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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

Here 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).Here 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
Highlighted
Solution Sage
Solution Sage

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
Highlighted
Solution Sage
Solution Sage

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

Highlighted
Frequent Visitor

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

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

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!

Top Solution Authors
Users online (6,419)