Calculate In/out timing difference



i have a task to calculate the employees overtime per week. The normal duty of each employee is 7 hours per day starting from 9AM and 35hours per week. 

If the employee works more than 40 hours per week, the overtime will be calculated for him. If he work less than 40 hours, he will get zero overtime. 

The time is flexi. Between 9AM and 10AM. Any employee punch before 9AM is ok, but the counting of his 7 hours will start from 9AM and onwards only. 

Can anybody help in creating such logic in Excel. 





The task you describe consist of 80% design and 20% logic.

  1. Design is the format in which the information is presented.
    Which columns will there be? What do they contain?
    How is the data entered? How is the result presented?
    None of this requires a knowledge of Excel.
    It's what business consultants do, not Excel experts.
  2. Logic is how the data are evaluated to arrive at the result your form displays.
    An employee arrives at 9 AM. That is data.
    An employee leavers at 16:55. That is data.
    The logic will determine that he worked for 8 hours and 55 minutes.
    If there is a rule of calculation and a column to show the result logic can also determine which part of this time is considerd overtime.

To start off your project create the layout, the design. Enter some numbers (data) in the fields you have provided. To calculate work time you subtract the start time from end time. If you need help with that come back here and you will get it.


Answer the Question

You must create an account to use the forum. Create an Account or Login