cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Songuinario
New Member

Used Delay Until action based on an Excel spreadsheet date

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:

image.png

 

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

image.png

 

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

 

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

image.png

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions

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!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

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!!!!

View solution in original post

7 REPLIES 7
ManishJain
Solution Sage
Solution Sage

@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:

image.png

 

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

 

 

 

 

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

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



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

 

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!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



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!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

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!!!!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (3,731)