Count Weekly Staff Formula

[Finalfight40]

Hi All

I am looking for some help with a formula.

I have a simplified table below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[TD]CATEGORY[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]14/04/2018[/TD]
[TD][/TD]
[TD]Comp[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]09/07/2018[/TD]
[TD]08/09/2018[/TD]
[TD]Comp[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]01/02/2018[/TD]
[TD]15/05/2018[/TD]
[TD]Comp[/TD]
[/TR]
[TR]
[TD]Cathy[/TD]
[TD]01/07/2018[/TD]
[TD][/TD]
[TD]MM[/TD]
[/TR]
[TR]
[TD]Jenny[/TD]
[TD]03/07/2018[/TD]
[TD]10/07/2018[/TD]
[TD]Comp[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]02/07/2018[/TD]
[TD]09/07/2018[/TD]
[/TR]
[TR]
[TD]*DESIRED RESULT* 1.8[/TD]
[TD]*DESIRED RESULT* 2.4[/TD]
[/TR]
</tbody>[/TABLE]

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