# Track time spent in status

**URL:** <https://community.fibery.io/t/track-time-spent-in-status/2789>\
**Category:** Get Help\
**Created:** [May 12, 2022, 10:43am UTC](https://community.fibery.io/t/track-time-spent-in-status/2789 "2022-05-12T10:43:00Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nick](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/nick/32/4744_2.png) [@Nick](https://community.fibery.io/u/Nick)\
**Post date:** [May 12, 2022, 10:43am UTC](https://community.fibery.io/t/track-time-spent-in-status/2789/1 "2022-05-12T10:43:00Z")

</div>

Hi there!

I have one task that I don’t understand how to solve. I have already read [this](https://community.fibery.io/t/time-in-status-for-field-or-field-modification-date/1181) topic and looked [this](https://help.fibery.io/en/articles/5179284-how-to-create-cumulative-charts-cfd-burn-up-down-charts) example. However, there is still no suitable solution.

I have 2 database: State and Lead. My goal is track how much time Lead spend at each State. Ideally, I want to get something like the graph in the image.

 ![img2](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/4/4289c49759b8c9ead5fc24018419918ed7815378.png)

One of the solutions is to create an additional field for the lead and write down the date of transition to the next status in it. And also create a field with a formula that will calculate the number of days that the lead spent in the status (Days in status).

But there’s a problem. If I have 5 statuses, then the lead will have 10 fields. This will greatly interfere with readability.

Maybe there is some more elegant solution?

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [May 12, 2022, 11:06am UTC](https://community.fibery.io/t/track-time-spent-in-status/2789/2 "2022-05-12T11:06:34Z")

</div>

I think it should be possible using reports, but I have a question: what does the month mean in the image you shared? Is it the month that each Lead was created, or is it something else?

Say I have two Leads that can be in states A, B, C, D and the following transitions, what should the chart look like?

Lead 1  
20th Jan - created (state A)  
5th Feb - changed to state B  
10th March - changed to state C  
11th March - changed to state A  
4th April - changed to state D

Lead 2  
25th Feb - created (state A)  
3rd March - changed to state B  
7th March - changed to state A  
10th March - changed to state C  
20th March - changed to state B  
5th April - changed to state D

(assume that today is 10th April)

The reason I ask, is to determine what the y-axis value(s) for state A or state B should be, for example.

---

<div class="post-metadata">

**Author:** ![Nick](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/nick/32/4744_2.png) [@Nick](https://community.fibery.io/u/Nick)\
**Post date:** [May 12, 2022, 12:02pm UTC](https://community.fibery.io/t/track-time-spent-in-status/2789/3 "2022-05-12T12:02:04Z")

</div>

1. “What does the month mean in the image you shared?” – Yes, it does. Those leads that were created in May are included in the selection for this month.

2. “…is to determine what the y-axis value(s) for state A or state B should be, for example.” – I think it should be the difference between today and the date the lead was created, expressed in days.

For example, for Lead 1 there will be the following status statistics:  
A: 50 days (11th March - 20th Jan)  
B: 33 days (10th March - 5th Feb)

But if we can set a condition, for example, for status B “If we move the Lead from status A, then we calculate the transition date. If not, we don’t calculate it.” Not sure if this is even possible 😅

In this case, Lead 1 will have the following values:  
A: 16 days (5th Feb - 20th Jan)  
B: 33 days (10th March - 5th Feb)

This is not entirely correct and there are many nuances, but I do not find a better solution.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [May 12, 2022, 12:17pm UTC](https://community.fibery.io/t/track-time-spent-in-status/2789/4 "2022-05-12T12:17:45Z")

</div>

So here is how I think you should do it:

- create a chart report using historical data for Leads
- use State as the x-axis
- use the formula below for y-axis
- choose a line chart
- use Creation date for colour coding (grouped by month)

```auto
SUM(
	DATEDIFF(
		[Modification Date],
		IF(
			DATEPART(
				[Modification Valid To],
				'year'
			) == 9999,
			NOW(),
			[Modification Valid To]
		),
		'day'
	)
)

```

Here is a gif of me doing it (but the resulting chart looks flat because my database is less than a day old!)  
 ![firefox_IdJ1C2f9ev](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/1/141f63e16e8abfdb0ebbad620f4d9f08798278ec.gif)

Note: you will probably want to give your y-axis a more meaningful title(!)

![firefox_fLchLZRmzT](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/4/4f0fc6b3b15da0cc0fe61867d7fcc79f574623e1.gif)

and the report can get a better name than New report 🙂

Let us know how you get on 🙂

---

<div class="post-metadata">

**Author:** ![Nick](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/nick/32/4744_2.png) [@Nick](https://community.fibery.io/u/Nick)\
**Post date:** [May 17, 2022, 5:38am UTC](https://community.fibery.io/t/track-time-spent-in-status/2789/5 "2022-05-17T05:38:25Z")

</div>

It’s done. Thank you so much for your help!

We also thought about how to exclude extreme values. For example, when a lead card goes through the entire funnel in a day. This happens if a client worked with us on another project and immediately wants to move on to another stage.

But we didn’t come up with any optimal solution 🙂