find value based each week

[surajap123]

Hi All,

I have 3 columns in a table.

Sales    StartDate        EndDate

12         19/05/2014    25/07/2014

3           14/04/2014    20/06/2014

5           28/04/2014    27/06/2014

9           28/04/2014    27/06/2014

15        28/04/2014    01/08/2014

8          19/05/2014    27/06/2014

I want to know number of sales happend in each calender week. So when user select a week they could able to see number of sales happened on that particular week.

To allow the user to select a week, i dont have any week field. So i think i neeed to create a master calender.

But,Since the table has StartDate and EndDate, I am not sure how can i link these 2 dates to create a calender?

Please suggest !!

[tresesco]

Have a look at intervalmatch(), that should help you.

[surajap123]

Hi tresesco,

Thanks a lot for the suggestion..

I have gone through the intervalmatch function and tried to apply it for my requirement. But i am stuck while writing the script for my requirement. May be because i never used this before.

My requirement is to count the candidates occupied the room PER WEEK.

For each PersonID, i have From and ToDate. But dont have another single date which i can use in intervalmatch.

How can i use intervalmatch in this scenario?

Please have a look at the attached app i developed for my requirement.

Please suggest the script..

[surajap123]

Hi All,

Could anyone help me on this..I tried a lot, but couldnt get the right output..

[robert_mika]

Add column Week Number in your  ERoom data sheet and add:

=WEEKNUM(C2)

[surajap123]

Hi Robert,

Thanks for your reply.

If you look at ERoom Sheet, the Date range(FromDate and ToDate in the ERoom Sheet) is more than a week. In some row, the week range can be 3 weeks and different in other row.

So, how can I add one Week Number for each row, in the sheet?

[surajap123]

Hi Robert,

To simplify, I want to count the IDs which fall in a week range.

In this example the week is 19/05/2014 - 23/05/2014. So here the answer is 13.

FromDate	ToDate	ID
19/05/2014	25/07/2014	12
14/04/2014	20/06/2014	24
28/04/2014	27/06/2014	19
28/04/2014	27/06/2014	12
28/04/2014	01/08/2014	23
19/05/2014	27/06/2014	9
19/05/2014	27/06/2014	9
31/03/2014	06/06/2014	2
31/03/2014	23/05/2014	25
28/04/2014	13/06/2014	16
28/04/2014	23/05/2014	27
12/05/2014	13/06/2014	16
12/05/2014	23/05/2014	15