Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count days
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 -- Dan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
count days
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. -- HTH, Barb Reinhardt "Dan" wrote: 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 -- Dan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
count days
that worked, and way simplier than what i was planning!
-- Dan "Barb Reinhardt" wrote: 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. -- HTH, Barb Reinhardt "Dan" wrote: 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 -- Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT how many ROWS ago out of 10 days that the highest high in 10 days was made | Excel Worksheet Functions | |||
count days | New Users to Excel | |||
Count # days | Excel Worksheet Functions | |||
count down the days??? | Excel Discussion (Misc queries) | |||
Count of days | Excel Worksheet Functions |