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...
Solved! Go to 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!
Proud to be a Flownaut!
Hi,
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!!!!
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
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...
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
Proud to be a Flownaut!
Hi,
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!
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!
Proud to be a Flownaut!
Hi,
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!!!!
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
At the monthly call, connect with other leaders and find out how community makes your experience even better.
User | Count |
---|---|
23 | |
23 | |
23 | |
22 | |
17 |
User | Count |
---|---|
56 | |
39 | |
38 | |
29 | |
26 |