Used Delay Until action based on an Excel spreadsheet date

[Songuinario]

Hi,

I'm trying to get a date from a table in an Excel online spreasheet and use that date to perform a delay until action, but I can't format the date to be used by delay until action.

I don't know how to do that directly so I create a variable first for the date:

After I tried to format the variable for the format that Delay Until would understand using the following steps:

The expression that I'm using is formatdatetime(outputs('Compor'),'yyyy-MM-ddThh:mm:ss').

But, I'm getting and error in this part:

Error in a google free translation:

It was not possible to process the model's language expressions in the 'Set_variable' entries for the action in row '1' and in column '9684': 'In function' formatdatetime ', the value provided for the date and time string '30 / 04 / 2020 17:15:00 'was not valid. The date and time string must match the ISO 8601 format.

Don't know what I'm doing wrong...

[ManishJain]

Hi ,

Please refer to following threads : 

https://powerusers.microsoft.com/t5/Building-Flows/Excel-dates-turned-into-integers/td-p/130364

https://powerusers.microsoft.com/t5/Building-Flows/Excel-Online-Date/td-p/134200

Thanks

[Songuinario]

@ManishJain 

Thanks for the links, I'm really, total new to this thing of power automate and programming, at all. Sorry if my understanding is wrong.

But I think, the connector is getting me the date with the correct information as you can see in this print:

I think there is something wrong in the expression that I tried to use.

I also tried the adddays solution, but I got the same error...

[efialttes]

Hi!

I believe ISO 8601 does not expect this format...

30/04/2020 17:15:00 

.. but this other instead

2020-04-30T17:15:00.0000000Z

Hope this makes sense

[Songuinario]

Hi,

@efialttes 

Thanks for your response!!!

I'm trying to convert my excel date to that format... I tried this expression:

formatdatetime(outputs('Compor'),'yyyy-MM-ddThh:mm:ss')

But, I'm failing hard, I guess...

[efialttes]

Hi again

So your original expression is:

formatdatetime(outputs('Compor'),'yyyy-MM-ddThh:mm:ss')

The problem is, outputs('Compor') shall be compliant with ISO 8601 format...

...but its current content has this format...

30/04/2020 17:15:00 

...instead of this other

2020-04-30T17:15:00.0000000Z

Did the links already recommended by other community members in this same thread suggest a way to convert your current format to ISO8601?

Thanx!

[efialttes · Accepted solution]

Let's keep outputs('Compor') as it is

Year: split(first(split(outputs('Compor'),' ')),'/')[2]

Month: split(first(split(outputs('Compor'),' ')),'/')[1]

Day: split(first(split(outputs('Compor'),' ')),'/')[0]

Hours, minutes, seconds: last(split(outputs('Compor'),' '))

...so you can use concat() based expression to place them in the ISO8601 expected format, and add the corresponding characters in between

concat(split(first(split(outputs('Compor'),' ')),'/')[2],'-',split(first(split(outputs('Compor'),' ')),'/')[1],'-',split(first(split(outputs('Compor'),' ')),'/')[0],'T',last(split(outputs('Compor'),' ')),'.0000000Z')

Not with my laptop, unable to test

Thanx!

[Songuinario · Accepted solution]

Hi,

@efialttes 

Just used your idea to break the date... But I did it directly in EXCEL (because I have a little more knowledge) and then compose the information in Power Automate!!!!

Worked as a charm!!!

Many thanks!!!!