Is It possible to write a TFS Query to get Actual Time Taken for a Tasks?

Question

I have been using TFS to track my backlog items and I am now trying to write a query to see how long I took on particular tasks in the last 7 days. So Far I have this query:

Work Item Type = Task

AND State = Done

AND Closed Date = @Today - 7

AND Area Path = @Project

AND Assigned To = @Me

and have added the column "Closed Date" which shows the time work stopped on this item. But I cannot get any information as to when work started on the Task

Feels like the data should be there as on particular tasks it has the following kind of information:

Is this possible? I don't mind extracting the data to Excel to analyse.

The reason I ask is because I would then like to go on to compare the amount of hours assigned to a particular task and compare it to amount of hours actually taken to help my predictions of time taken in future.

Corespondent

Answer 1

TFS is not intended to be a time tracking tool. You could query the work item history with the TFS API and check the timestamps on when the state transitions occurred if you really wanted to.

Agile projects don't focus on how long individual tasks take -- they focus on how much value the development team is providing over the course of a set period of time. One thing might be estimated low, one task might be estimated high, but it ultimately doesn't matter as long as the team delivers what they committed to deliver.

Introject

I'm not sure I agree with that. Kanban teams often measure the time it takes for work items to pass through all the stages of their workflow (cycle time). They use this as a mechanism to measure how efficient their work in progress limits are. Perhaps you were focusing more on Scrum? – Electret

Cycle time is not what you asked in your question, and not about comparing estimate to actual. – Zoilazoilla

You can easily calculate cycle time with the dates between state transitions – Zoilazoilla

Is this because you only commit to what is defined and reasonable to accomplish within a given sprint time? And that each chunk should be well defined and understood before being taken on by the dev team? Rather than taking large ambiguous goals that would span unnecessary amounts of time? – Pediment

Answer 2

It's a pretty simple query actually. Here's what I'm using.

(Work Item Type = User Story
OR Work Item Type = Bug
)
AND State <> New
AND State <> Removed

Simply include the Activated Date, the Resolved Date, and the Closed Date in the displayed columns. The time between Activated and Closed is your total cycle time.

My team uses Resolved as a "pending deployment" status, so comparing Resolved to Closed allows us to determine how long it takes to get an item from "done" to "in prod".

Egypt

This is great, can we get the difference between activated date and closed date in query itself, I mean manipulating the result, I know we can export this to excel and can do it, but if its possible with query then it will be good. – Heimer

No @TusharMali, but if you’re on Windows you can create a data connection to the query. Then you just need to enter the formula and refresh the data. – Egypt

If this solves your problem, don’t forget to vote. ;) – Egypt

@TusharMali just another tip. Use NETWORKDAYS() in your excel function. It removes weekends and (optionally) holidays from the calculation. – Egypt

Answer 3

1. It's a good practice to track Cycle Time for a team to discover areas for improvements (for User Story level, yes)
2. It's not good that user must go Excel to calculate Cycle Time. I can't use diagram Azure Devops tools with this calculations and its not automated.
3. Cycle Time widget don't shows results for several teams and it's not flexible tool, so it's not enough

Coquelicot

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Cocainism