cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Memorable Member
Memorable Member

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!

View solution in original post

2 REPLIES 2
Memorable Member
Memorable Member

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!

View solution in original post

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
New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (85,102)