Prev: Vlookup formula
Next: Incorporating Cell color fill in an "if" logical function?
From: Tacrier on 6 May 2010 12:58
I would like to calculate on a daily basis, accumulated vacation time based
on the following criteria:

Total daily hours (work and leave taken)
Hours must fall on a week day
Entitlement hours from another spreadsheet that are based on employee
seniority and regular shift hours.

'Sheet1' in my 'Book1.xls' looks like this:

A2:A372 Day of the week (starting with Sunday in A2)
B2:B372 Date beginning with April 1, 2010
C2:C372 Explanation (if necessary)
D2:D372 Work day 1 = it's a workday, blank = not a workday
E2:E372 Paid Daily Total
F2:F372 Worked hours
G2:G372 Annual Leave hours taken
H2:H372 Sick Leave hours taken
I2:I372 Mental health day taken
J2:J372 Education Leave taken
K2:K372 Bereavement Leave taken
L2:L372 Compassionate Leave taken
M2:M372 Actual Daily Total (Sum of F:L)
N2:N372 Time off in Lieu accumulated equal to Actual Daily Total - Paid
Daily Total
O2:O372 Approved Overtime
P2:P372 Overtime rate
Q2:Q372 Annual Leave Accumulated **
R2:R372 Annual Leave Accumulated
S2:S372 Employee ID

'Annual Leave Entitlements' sheet in my 'Entitlements' workbook looks like
this:
A2:A10 Regular workdays per year
B2:B10 Regular workdays per month
C2:C10 Regular work hours per month
D2:D10 Regular work hours per day
E2:E10 Years of Service
F2:F10 Entitlement weeks per year
G2:G10 Entitlement Days per year
H2:H10 Entitlement days per month
I2:I10 Entitlement hours per year
J2:J10 Entitlement hours per month
K2:K10 Entitlement Hours per day

I have another sheet that has all our staff names, original hire dates and
current years of service. ** above is where I would like the vacation time
calculation result to appear.


(Also, ff my spreadsheets look too convoluted, please let me know if you
have another suggestion as to how to track this information)

Thanks!

Trina





 | 
Pages: 1
Prev: Vlookup formula
Next: Incorporating Cell color fill in an "if" logical function?