Date Range

[shahzad_afzal]

Hi

I have data of every sales guy in the team of their daily sales across the year with dates...

I need to dynamically extract start data and number of days every sales guy is taking to earn $2000 in total... This will help me understand product vs. season of product vs. region of sales of the sales guy...

Our target to earn $2000 is min 120 days and maximum 160 days...

How to extract suitable time window in excel please...?

[#2]

Hi@shahzad_afzal,

I have created a sample file for you in which your task (if I have understood it correctly) is solved. It calculates for each date how many days it takes to reach $2000.

Get back to me if I didn't understand it correctly.

For the function I used a formula which is not yet available in every Excel version. In case of need you can try it in Excel for the web.

There are probably still simpler possibilities. Let's see if someone else has a better idea.

=IFERROR(XLOOKUP(2000,BYROW(ROW(2:161),LAMBDA(in,SUMIF(M2:M161,"<="&in,B2:B161))),A2:A161,,1)-A2+1,NA())

[#3]

@dscheikey Thanks for your response. I am attaching a file for your review as the formula in your above message is complicated. I have added an explanation too for your review.

[#4]

@shahzad_afzalUnfortunately, it is still very difficult for me to understand what you actually want to do. Your explanations are contradictory and not comprehensible for me. I don't understand if you are calculating time periods or sums at fixed time periods. When do the 160 days come into play. Your example only makes a calculation with 120 days. Strictly speaking 121 days. I have attached another example. Possibly we come further with it.

[#5]

Dear
Your sheet gave me quite a straightforward way to count a continuous 120 rows to check the total sum equaling 2,000. I can find the nearest date where the difference is not more than 2 or 3 so I can consider it as my start date. If I want to have a range, example; the sum of 121 days comes to 1990 and sum of 126 days comes to 2000. How to adjust this variation? I need answer as 126.

[#6]

Please prepare your question with a new document or a screenshot. I absolutely do not understand what the question should be. Which variation? Where?

[#7]

Hi.
Now 120 days process is working perfectly fine and thanks for supporting with quite effortless process. 120 - 160 days mean; we have min 120 days to achieve target and max 160 days. After 160 days, the sales guy didn't qualify for the sales incentive. Is this possible to have min and max days formula to count for $2000?

[#8]

@shahzad_afzal 

I come back to my first formula, which already did exactly what you want. Minimum and maximum do not work at all. You can only count how many days it takes to reach the 2000$. That is the result of minimum and maximum.

I have made an example again. There it should possibly become clearer.