Need correct time format from Google Sheets to Delay Until action

[thatmattwade]

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. 

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).

[faustocapellanj · Accepted solution]

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
   
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
   
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')

   

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

 

Regards,

Fausto Capellan, Jr

[thatmattwade]

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?