Count Weekly Staff Formula

[Finalfight40]

Hi All

I am looking for some help with a formula.

I have a simplified table below:

NAME	START DATE	END DATE	CATEGORY
George	14/04/2018		Comp
Jeff	09/07/2018	08/09/2018	Comp
Bob	01/02/2018	15/05/2018	Comp
Cathy	01/07/2018		MM
Jenny	03/07/2018	10/07/2018	Comp

<tbody>
</tbody>

And on another sheet i have the week commencing dates, sample below (the dates would just increase by 1 week every time):

02/07/2018	09/07/2018
*DESIRED RESULT* 1.8	*DESIRED RESULT* 2.4

<tbody>
</tbody>

How this formula would work:

In this example i am only looking for Comp's that are currently with us so:

• The formula would need to look at the category name for Comp.
• If the end date is blank, they are still with us and should assume they will be with us for foreseeable future.
• The table can include future starters so would also need to check the start dates.
• If they are leaving or starting midway through the week (Jenny for example), it would need to factor in how many days they are with us for that week. For example Jenny start on a Tuesday which would mean she would be here for 0.8 of the first week, would leave on the Tuesday and therefore be here for 0.4 of a week.

(assume data starts in A1 for both sheets)

Any help is apprecaited

 

[Finalfight40]

Never mind, i found a work around