how to calculate the late and under time

**gerard_gonzales33** · 09-11-2012, 08:40 PM

Hi there everyone!

Need your expert help in my file.

Am working on my time sheet that needs to compute for the late and under time. My work starts at 8:30am and ends at 5:30pm. With unpaid break at 12:30 pm to 1:30 pm. So far what I have is that it can compute for the late and under time but is includes the unpaid break. How can solve this?

A1= long date
B1= time in
C1= time out
D1= late
E1= under time

Thanks

**Al Chara** · 09-11-2012, 08:59 PM

It might help if you post a sample of your data and how you want it to look.

**gerard_gonzales33** · 09-11-2012, 10:30 PM

Hi Al Chara,

Thanks for your reply.

**Al Chara** · 09-12-2012, 12:59 AM

I downloaded your file, but I'm not sure what the formula in cell S9 is doing.

Please Login or Register  to view this content.

As far as I can see, S9 is empty if B9=0, if B9 is not empty and if A9 is a weekday, S6 is not empty, B9 is greater than S6, G9, J9, M9, and P9, then S9 = B9-S6, if not then S9=0.

You mention in your file that S9 should be 7 minutes late. But the formula actually returns 0 because G9=4, which is greater than B9. B9 is formatted as time (13:37 or 1:37:00 PM). Remember that Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day. Therefore, 13:37 is actually stored as 0.567361111111111 (13/24 + 37/60/24). So as long as there is 1 or greater in either S6, G9, J9, M9, or P9, then the formula will return 0. This is one issue that I think you need to sort out.

The other issue is how B9-S6 is supposed to return 7 mins. In your example B9=13:37 and S6=8:30 AM. Subtracting the two returns 5:07. This is the same with row 13. How is B13-S6 supposed to return 35 minutes, when B13=14:05 and s6=8:30AM? Changing S6 to 13:30, will return the correct results, assuming you fix the first issue with the values in columns G, J, M, and P.

I hope this helps. Let me know if you have additional questions.

**gerard_gonzales33** · 09-12-2012, 02:43 AM

Legend:
4.00 represents as half day
coloumn A format is long date
coloumn B,C,S,and T format is hh:mm
coloumn G,H,J,K,M,N,P and Q format is number

Under the columns G, J,M and P the AM side is connected to tardy, while columns H,K,N and Q the PM side is connected to undertime.My problem is how could I formulate a formula that the unpaid break i.e. 12:30 to 13:30 be connected or involved in my existing formula.Do you think because of the format of the GJKMNP and Q affects the result. Am running out of ideas/solution for this. I hope you could help me with this.

Thank you