VBA to calculate half day/ full day as per time sheet

Hi Friends,

I need your help in the attached time sheet to calculate whether the employee is entitled for half day or full day salary. The rule is simple: Either he logs in after 1200 hrs or logs after 2100 hours or both, he is marked as half day, else full day.

I would like conditional formatting for half day employee cells, name filled with red, and also copied to a new sheet.

Attached is workbook for reference.

Thank you for the response. It's a retail outlet whose working window is 11am till 9pm. All in single shift

Ok,thanks. I've been working on it this weekend. Should get back to you today with what you need.

There may be some differences between this attachment and what you've been working on there. You are using Excel 2010 and I use 2007.

If you have trouble getting this attachment to work, it may be necessary to copy the code into your version. If you have to do this PLEASE USE A COPY OF YOUR VERSION, NOT THE ORIGINAL. That way if anything goes wrong
you won't lose the original file.

The changes made to the workbook include :

Column AM has Formulas and Conditional Formatting in various cells relating to the "In Time" and "Out Time"
entries. The word ERR is placed above those cells as a visual indication.

Column AP is a "helper column" in that the numbers contained in the cells are transferred to the Report Sheet
when the Monthly Clocking Report is run.

When an employee clocks in late or clocks out early, the cells in column AM will turn red and indicate how
many times in the pay period they missed a timely time punch. Their name will also turn red. The numbers
in those cells are automatically copied to the adjacent cell in Colum AP.

At the end of the pay period you click the Report button. The report is generated from the data in the
monthly Clock-In / Clock-Out Report to the Report Sheet.

You can then print that sheet for filing - or - you can click the ADD LINE button to give blank lines for
the following month's report.

The two buttons on the Report Sheet are for adding additional lines to the spreadsheet. This way you can maintain a copy of all Clocking discrepancies. If this is your desire, be certain to use the ADD MULTI LINE button ... cuz there
has to be enough empty rows (starting at row 3) for additional data to be transferred from the clock-in / clock-out sheet. If you don't move existing data down, the new data will over write the existing data.

Experiment with the two buttons (Add Rows) to see how they function. The ADD MULTI LINE button works slightly different from the other.

HTH

I Think this resolves. Thank you!

You're Welcome ! Glad to help.