Unanswered

Need To Extract Days from the derived Date format.

(0) Share

Report

Posted on by Sukesh_Reddy Microsoft Employee

I have a flow to get query results(workitems) from Azure devops, i am calculating the age of the bug with below function.

dateDifference(item()?['System.CreatedDate'], utcNow())

output:

{

"Age": "28.10:24:11.9458506"

i want to extract days from the above output i.e 28.

Can anyone help me with this.

Thanks in advance.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (2)

rzaneti 4,241 Super User 2025 Season 2 on at

Like (1)

Report

Hi @Sukesh_Reddy ,

You can use an expression like this: first(split(variables('timespan_days'), '.')), just replacing the text in red for your actual timespan content extracted from dateDifference(). this expression will give you the output as string, so you may also want to encapsulate it with an int() expression to convert it as a number.

I'm also sharing an article that I recently wrote about this topic, bringing some other scenarios on how to extract data from timespans: http://digitalmill.net/2024/04/16/extracting-days-and-time-from-timespans-in-power-automate/

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/
https://www.linkedin.com/in/raphael-haus-zaneti/

Was this reply helpful? Yes No
Sukesh_Reddy Microsoft Employee on at

Like (0)

Report

Thank you @rzaneti ,
Your suggestion helped me to figure out a solution for my blocker.
in my scenario i am retrieving multiple (around 200) work items from query, so i used apply for each in my case.
initialized the variable outside the loop and set the variable inside the loop.

Was this reply helpful? Yes No
Sukesh_Reddy Microsoft Employee on at

Like (0)

Report

@rzaneti ,

i am able to get the correct output but unable to apply to my table.
i want the above output to be displayed in column in a table one by one.

example output:

Was this reply helpful? Yes No
rzaneti 4,241 Super User 2025 Season 2 on at

Like (1)

Report

Hi @Sukesh_Reddy ,

I'm glad that the days quantity issue is solved!

About the table one, it looks like you are passing a string as input to your "Create a HTML table" action, while an array of objects is expected. I'm sharing a blog post about objects, for the users that reach this topic and never worked with this data type before.

Just to confirm: you want to return an HTML table with a single column (age of the bug in days), right? If this is the case, start by creating an array variable, where the array of objects will be stored:

For this example, I will create an additional array variable (you don't need to), just to simulate the dates from where we will extract the days quantity for each of your bugs:

Then, inside the apply to each, create an "Compose" action to craft you object. It is simple: you just need to add a "days" key, and set the expression to extract the days from timespan as value:

This is the final input: {"days": [expression]}. Feel free to change the key name for something more meaningful.

The following step is to add an "Append to array variable" action, passing your compose as input:

Now your array of objects is ready, and we can generate the table. Out of the loop, add a "Create HTML table" action, passing your array variable as input. If you choose the "automatic" option for the columns dropdown, the column name will match to the objects key. If you wish, it is possible to change the name of the column by choosing "custom" in the dropdown, but you will need to pass the expression item()[key_name] in the right input. For this example, we are using both approaches:

And these are the outputs for each approach:

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/
https://www.linkedin.com/in/raphael-haus-zaneti/

Was this reply helpful? Yes No
Sukesh_Reddy Microsoft Employee on at

Like (1)

Report

@rzaneti ,

Clarification:
i want all these columns in my table along with Days.
so, i tried to do it as per your suggestion.
Note: Above solution works but,
When i tried to add ID, AssignedTo, Title, Days it directly picks the loop and fails with the below error.

Was this reply helpful? Yes No
Verified answer

rzaneti 4,241 Super User 2025 Season 2 on at

Like (0)

Report

Hi @Sukesh_Reddy ,

The loop is generated because you are selecting columns from both the array variable and the "Get query results".

You can start by changing the "From" input in the "Create HTML table", keeping the "value" property only. When mapping the columns, add the expression to extract the days (first....) to the cell on the right of "Age". Currently, this formula is allocated in your "Compose" action.

As you need other columns in addition to the age, the "Compose" approach may not help you.

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/
https://www.linkedin.com/in/raphael-haus-zaneti/

Was this reply helpful? Yes No
Verified answer

Sukesh_Reddy Microsoft Employee on at

Like (1)

Report

@rzaneti ,

Thank you for the help @rzaneti this worked perfectly fine.

brief:
Adding this expression in the Create_html_table step me achieve the desired output.
first(split(dateDifference(item()?['System.CreatedDate'], utcNow()), '.'))

Was this reply helpful? Yes No