cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gwill1983
Helper IV
Helper IV

Format Date in Return Values

Hi all,

 

I have a flow with my PVA topic which pulls a date from excel, but it is returning it as a number. 

 

After my 'Get a row' action I want the date to return as a date, not a text string.

Gwill1983_0-1647437949157.png

Gwill1983_1-1647438309956.png

It is the "W/C Date" in the output above that I am trying to format.....

 

I know how to format this in power automate, but the return values section doesnt allow formulas to return a value. 

 

I am fairly certain it will be in a compose action, but cant get any formula that I try to work.

 

Can anybody help with a solution for this?

1 ACCEPTED SOLUTION

Accepted Solutions
renatoromao
Super User
Super User

@Gwill1983 ,

 

Sometimes, this alert appears but you can try to confirm multiple times. (sometimes works)

 

Also, you can follow my steps below:

 

format1.png

 

format2.png

 

format3.png


Did I answer your question? Mark my post as a solution!
Thanks!

Renato Romão,

Connect with me here 😉

Power Virtual Agents course (+2.760 students) : English | Português

View solution in original post

7 REPLIES 7
renatoromao
Super User
Super User

Hi @Gwill1983 ,

 

You need to format this date.

I have added your code below, you can copy and paste within your PVA output (within your Flow):

 

@{if(empty(outputs('Get_a_row')?['body/W~1C Date']),null,addDays('1899-12-30',int(outputs('Get_a_row')?['body/W~1C Date']),'yyyy-MM-dd'))}

 

Note: yyyy-MM-dd we are formatting as Year-Month-Day


Did I answer your question? Mark my post as a solution!
Thanks!

Renato Romão,

Connect with me here 😉

Power Virtual Agents course (+2.760 students) : English | Português

Thanks.

 

I added this into the return values as below:

 

Gwill1983_0-1647439782196.png

 

In my bot it just returned the  symbol however

 

Gwill1983_1-1647439839713.png

Any ideas what I am doing wrong?

renatoromao
Super User
Super User

@Gwill1983 ,

 

Remove the text added and open the Dynamic Content panel (right side) >> Go to Expression tab >> Add the following code and click OK if(empty(outputs('Get_a_row')?['body/W~1C Date']),null,addDays('1899-12-30',int(outputs('Get_a_row')?['body/W~1C Date']),'yyyy-MM-dd'))


Did I answer your question? Mark my post as a solution!
Thanks!

Renato Romão,

Connect with me here 😉

Power Virtual Agents course (+2.760 students) : English | Português
Gwill1983
Helper IV
Helper IV

Ah! I had to zoom out for this to appear! Apologies

 

Getting this error message now

 

Gwill1983_0-1647440726840.png

 

 

 

renatoromao
Super User
Super User

@Gwill1983 ,

 

Sometimes, this alert appears but you can try to confirm multiple times. (sometimes works)

 

Also, you can follow my steps below:

 

format1.png

 

format2.png

 

format3.png


Did I answer your question? Mark my post as a solution!
Thanks!

Renato Romão,

Connect with me here 😉

Power Virtual Agents course (+2.760 students) : English | Português
renatoromao
Super User
Super User

@Gwill1983 ,

 

If still not working, you can open the Dynamic Content panel, select you "W/C Date" field. This Excel column will appear in your output, then you can select the input and copy all content there, and paste in a notepad.

 

You will see something like that:

@{output(....)} 

 

Then, you can replace the output within the expression below (without @, {, and } 😞

 if(empty(______PASTE_YOUR_OUTPUT_HERE_WITHOUT_@_AND_{_}______),null,addDays('1899-12-30',int(______PASTE_YOUR_OUTPUT_HERE_WITHOUT_@_AND_{_}______),'yyyy-MM-dd'))


Did I answer your question? Mark my post as a solution!
Thanks!

Renato Romão,

Connect with me here 😉

Power Virtual Agents course (+2.760 students) : English | Português
Gwill1983
Helper IV
Helper IV

Perfect!! All sorted. Thanks for your help

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Virtual Agents Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Virtual Agents!

Top Solution Authors
Top Kudoed Authors
Users online (3,558)