count days

[Dan]

I have a list of 41 dates employees can possibly work. I need to calculate
how many days each employee worked of the 41 dependant on their start date.
so I can work out a percentage of days worked. So if employee john started
work on the 1st day and was still employed on the last day, we would divide
total days actually worked by total days possible worked (41) to find
percentage of days worked.

however the days possible will change from 41 if an employee started after
the firsts possible day.

A B C D
name 10/04/2007 10/22/2007 10/31/2007
1 tom yes yes

2 JOe yes yes

3 sally yes yes yes

 

[Barb Reinhardt]

I'd use this formula

=SUMPRODUCT(--(C$1:H$1>=B3),--(C3:H3="Yes"))/SUMPRODUCT(--(C$1:H$1>=B3))

C1:H1 contain the dates. I added a column B that contains the start date
B3 contains the start date for the individual. C3:H3 contain Yes or Blank
as to whether the worker worked those days. Format the cell with this
formujla as % and you'll get the percentage of time they've worked since they
started.

 

[Dan]

that worked, and way simplier than what i was planning!