Excel Time Calulating Macro

Scope of Work / Objective: We are looking to get an Excel Macro created that will be used to help track our Paid Time off for Payroll Deliverables: 1 Excel Book with a Macro for Calculation I’ve got an Excel sheet with 4 worksheets: First Sheet is Employee Hour Workbook The second Sheet is Paid Time off Tracking The third Sheet is Paid Time off Log The fourth Sheet is Paid Time off Schedule What we need to be done: We need to get Column B, C, D, and E formatted to a consistent hour: minute: second formula Steps this Macro Needs to do: Section 1: PTO Calculation for Time Accrued 1. When this macro is executed (button) it will need to do the following It will need to reference the PTO key here per employee Then look up the Calculation Value on Fourth Sheet is Paid Time off Schedule It will then need to multiply out the hours worked in the Regular Time Column (Employee Hour Worksheet) Col B to the PTO Level; Note PTO Time does not Accrue on Holiday or PTO Time Used So for Example, if Employee J has a PTO level of “1” and worked 22:47 Hours it would multiply 22:47 hours by .0396 = .09022 and then convert the decimal place out to an hour and minute conversion. ** This calculation amount might need to be adjusted to hours and minutes instead of decimals ** It will add this to the amount available to use in Column H in the Employee Hour Workbook Worksheet and add this in the PTO Available worksheet up to the maximum amount allotted for the PTO Class Section 2: PTO Calculation for Time Used In this section we will want to subtract any PTO time used (if any) from the PTO time Available so For Example if an employee has 80:00:00 Hours Available and Uses 6:00:00 hours then the value remaining for the Column H Available would be 74:00:00 Section 3: Log File This is in the third workbook – but for each time the macro is processed we want to generate a log file that will keep track of the PTO calculated along with amount used. I would prefer to keep this where the newest records were on top. This long file needs to be time-stamped and done for each employee. When we process payroll we tend to just reuse the last Excel Sheet and do a “Save As” for the most current time.