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

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

Highlighted

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

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

 

 

 

 

Highlighted

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!



Highlighted

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

 

Highlighted

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!



Highlighted

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

Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (9,650)