How many days it takes for someone to reach a target?

[Gareth90]

Hi Guys,

I was wondering if I could find out how long it takes for someone in sales to reach a target of say £1000?

My guess is I have to add the sales figures cumulatively and then return the date of when the target was reached?

Not sure where to go after that.

Any help would be great!

 

[jtakw]

Hi,

Once you have the Date when target is reached, then End Date minus Start Date.

 

[Gareth90]

Hi Jtakw

Thank you for your reply.

What sort of formula would I need to return the date?

Can the end date minus the start date be integrated into the orginal formula or does that have to be a separate formula?

 

[Logit]

.
Here is one way :

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Salesman​		Total Sales​	Days to Goal $1000.00​
2​	John Q. Public​		$1,098.00​	12​
3​	​		​	​
4​	Start Date​	Sales Amt​	​	​
5​	09/05/18​	$23.00​	​	​
6​	09/08/18​	$125.00​	​	​
7​	09/09/18​	$114.00​	​	​
8​	09/10/18​	$87.00​	​	Formula C2 : =SUM(B:B)
9​	09/11/18​	$176.00​	​	​
10​	09/13/18​	$147.00​	​	Formula D2 : =IF(C2>=1000,LARGE(A5:A100,1)-SMALL(A5:A100,1),"")
11​	09/14/18​	$215.00​	​	​
12​	09/17/18​	$211.00​	​	​
13​	​		​	​

 

[Gareth90]

Thank you for the reply Logit that was really useful.

I just wanted to add another dimension to this.

If I had multiple sales people - what would be the best way to show the date when an individual cumulatively reached $1000?

 

[jtakw]

Perhaps if you show a sample of your layout, it'll be easier to understand your requirements and to help you achieve what you need.

 

[Gareth90]

I hope this table somewhat shows it:

I would need to find out how long it took someone to reach £1000 from their respective start date:

Posting Date	Name	Margin					Start Date
30/11/2017	Jack Doyle	500.00					Jack Doyle	01/11/2017
01/01/2018	Kieran Hall	100.00					Kieran Hal	01/12/2017
04/02/2018	Jack Doyle	800.00					Hannah Bailey	01/01/2018
06/02/2018	Kieran Hall	250.00
30/03/2018	Hannah Bailey	190.00
01/04/2018	Hannah Bailey	1,500.00
05/04/2018	Jack Doyle	600.00
05/04/2018	Hannah Bailey	770.00
25/04/2018	Kieran Hall	455.00

<tbody>
</tbody>

Let me know if you need any further information - thank you

 

[Logit]

.
https://www.amazon.com/clouddrive/share/vMCpv1uOL8z56F2AEK0hLiYblFnHyslYdEa5dHn2Ldl

 

[Gareth90]

Thank you Logit.

If I have over 100 sales people what would you recommend the quickest way to do this without making multiple tabs for each person?

Thanks

 

[Logit]

.

	H​	I​	J​	K​
1​
2​	Start Date​	​	Accumulated Sales​	Days To Goal​
3​	Jack Doyle	1/11/2017​	$1,900.00​	613​
4​	Kieran Hall	1/12/2017​	$805.00​
5​	Hannah Bailey	9/10/2018​	$2,460.00​	6​
6​
7​
8​
9​	Formula in J3 and copied down : =SUMIF(B3:B100,H3,C3:C100)
10​
11​	Formula in K3 and copied down : =IF(J3>=1000,NOW()-I3,"")