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 formulaNext: Incorporating Cell color fill in an "if" logical function?