I formatted timesheets in excel that I use a master template to pull employee’s time into and then upload into Solomon. That part is working well except that I still have to record leave onto paper records by hand-we keep a master leave book because Solomon has a habit of wiping out employee’s leave at random. I was hoping to put a template in each employee’s excel timesheet file and pull the time used from the corresponding timesheets to calculate accruals. The problem that I am having is that employees are credited with their vacation and sick leave according to the years of service on their anniversary date each month. So for example:
STAFF ANNIVERSARY DATE PAYROLL DATE
If anniversary date is: Date available leave
shows on payroll stub:
Nov 24, 2012 – Dec 07, 2012 Dec 14, 2012
Dec 08, 2012 – Dec 21, 2012 Dec 28, 2012
Dec 22, 2012 – Jan 04, 2013 Jan 11, 2013
Jan 05, 2013 – Jan 18, 2013 Jan 25, 2013
Jan 19, 2013 – Feb 01, 2013 Feb 08, 2013
Feb 02, 2013– Feb 15, 2013 Feb 22, 2013
Feb 16, 2013 – Mar 01, 2013 Mar 08, 2013
Mar 02, 2013– Mar 15, 2013 Mar 22, 2013
Mar 16, 2013– Mar 29, 2013 Apr 05, 2013
Mar 30, 2013 – Apr 12, 2013 Apr 19, 2013
I have written and rewritten formulas trying to make the earning cell only populate when the monthly anniversary date falls during the pay period, but I can’t seem to get it quite right. I’ve started from scratch and tried modifying templates. The spreadsheet I attached are templates that I have modified to calculate the years of service and populate the correct accrual.
I would greatly appreciate any help!
Vacation Template.xlsx
Bookmarks