Calculate total days

[Daniel]

Hi
I have a table of employee record of sick date, vacation date as PTO
(personal time off) and all record start end date as below.

My question is how to write a code so that I will have a summary table with
% of PTO in a year for each employees?

PTO code 100 = start to take off(no work)
PTO code 200= back to work

employee name PTO code date
A 100 2/23/07
B 100 5/07/07
C 100 8/26/07
A 200 4/23/07
B 200 6/04/07
C 200 9/19/07

From this table I can manually calculate employee A take PTO
from 2/23 to 4/23 so the percentage can be calculate as
60/365*100= 16.4% days off during 2007



Thanks in advance
Daniel

 

[Bernie Deitrick]

Daniel,

If you have your sample table in A1:C7, enter A into cell E2, B into E3, C
in E4, then in cell F2, array enter (Enter using Ctrl-Shift-Enter) the
formula

=(MAX(IF($A$2:$A$7=E2,IF($B$2:$B$7=200,$C$2:$C$7,""),""))-MAX(IF($A$2:$A$7=E2,IF($B$2:$B$7=100,$C$2:$C$7,""),"")))/365

This assumes that the names in column A are unique, appear twice, once each
with 100 and 200, and the dates are true dates. Format the cell F2 for
percentage, and copy down to match your list in E.

HTH,
Bernie
MS Excel MVP