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 :



[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

Salesman​

[/td][td][/td][td]

Total Sales​

[/td][td]

Days to Goal $1000.00​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

John Q. Public​

[/td][td][/td][td]

$1,098.00​

[/td][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

​

[/td][td][/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

Start Date​

[/td][td]

Sales Amt​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

09/05/18​

[/td][td]

$23.00​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]

09/08/18​

[/td][td]

$125.00​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]

09/09/18​

[/td][td]

$114.00​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]

09/10/18​

[/td][td]

$87.00​

[/td][td]

​

[/td][td]Formula C2 : =SUM(B:B)[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]

09/11/18​

[/td][td]

$176.00​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]

09/13/18​

[/td][td]

$147.00​

[/td][td]

​

[/td][td]Formula D2 : =IF(C2>=1000,LARGE(A5:A100,1)-SMALL(A5:A100,1),"")[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]

09/14/18​

[/td][td]

$215.00​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]

09/17/18​

[/td][td]

$211.00​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]

​

[/td][td][/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[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:

[TABLE="width: 679"]
<tbody>[TR]
[TD]Posting Date[/TD]
[TD]Name[/TD]
[TD]Margin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/11/2017[/TD]
[TD]Jack Doyle[/TD]
[TD] 500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jack Doyle[/TD]
[TD="align: right"]01/11/2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018[/TD]
[TD]Kieran Hall[/TD]
[TD] 100.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Kieran Hal[/TD]
[TD="align: right"]01/12/2017[/TD]
[/TR]
[TR]
[TD="align: right"]04/02/2018[/TD]
[TD]Jack Doyle[/TD]
[TD] 800.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hannah Bailey[/TD]
[TD="align: right"]01/01/2018[/TD]
[/TR]
[TR]
[TD="align: right"]06/02/2018[/TD]
[TD]Kieran Hall[/TD]
[TD] 250.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/03/2018[/TD]
[TD]Hannah Bailey[/TD]
[TD] 190.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/04/2018[/TD]
[TD]Hannah Bailey[/TD]
[TD] 1,500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/04/2018[/TD]
[TD]Jack Doyle[/TD]
[TD] 600.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/04/2018[/TD]
[TD]Hannah Bailey[/TD]
[TD] 770.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25/04/2018[/TD]
[TD]Kieran Hall[/TD]
[TD] 455.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

.


[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

Start Date​

[/td][td]

​

[/td][td]

Accumulated Sales​

[/td][td]

Days To Goal​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]Jack Doyle[/td][td]

1/11/2017​

[/td][td]

$1,900.00​

[/td][td]

613​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]Kieran Hall[/td][td]

1/12/2017​

[/td][td]

$805.00​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]Hannah Bailey[/td][td]

9/10/2018​

[/td][td]

$2,460.00​

[/td][td]

6​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]Formula in J3 and copied down : =SUMIF(B3:B100,H3,C3:C100)[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]Formula in K3 and copied down : =IF(J3>=1000,NOW()-I3,"")[/td][td][/td][td][/td][td][/td][/tr]
[/table]