# Calculate Late and Under time

1. ## Calculate Late and Under time

Happy Day to all,

A1= time in
B1= time out
C1= time in
D1= time out

I want to calculate the late and under time,
Office start at 9:am w/30 mins Grace period,
The break time is one hour only, please include over breaktime in calculation.
End of office hours 6:00 pm, strickly no over time

2. ## Re: Calculate Late and Under time

=if(d2>time(18,0,0),time(18,0,0),d2)-a2-(c2-b2)

3. ## Re: Calculate Late and Under time

There is a also a simple Macro that will do this for you.

``Please Login or Register  to view this content.``
The time format is 24hrs e.g., 16:10

To calculate hours, if start time is in A2 and finish time in B2
In C2 put =B2-A2

You can add the break duration
Enter it in D2 as 0:45
The formulas in C2 and E2 are also shown

A B C D E
1 Start Finish Total Break Worked
2 8:00 17:47 9:47 0:45 9:02
=B2-A2 =C2-D2

I hope this helps.

4. ## Re: Calculate Late and Under time

day92

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

5. ## Re: Calculate Late and Under time

My apologies - below is my new updated post:

``Please Login or Register  to view this content.``
The time format is 24hrs e.g., 16:10

To calculate hours, if start time is in A2 and finish time in B2
In C2 put =B2-A2

You can add the break duration
Enter it in D2 as 0:45
The formulas in C2 and E2 are also shown

A B C D E
1 Start Finish Total Break Worked
2 8:00 17:47 9:47 0:45 9:02
=B2-A2 =C2-D2

I hope this helps.

6. ## Re: Calculate Late and Under time

hi day 92, what if the employee work for half day only, let say 9: am to 1:00 pm only?

7. ## Re: Calculate Late and Under time

I will take a look at the Macro and adjust it but in the meantime the post my Tony seems to work fine.

8. ## Re: Calculate Late and Under time

Please see the attachement, that is the format i want, is that possible???

9. ## Re: Calculate Late and Under time

Hello everyone, im new user and still new in excel, and i also don't know how to make now post, im really sorry.

A1= time in, 8:30
B1= time out, 20:00

office hour start at 08:30am with 1/2hour Grace period,
but need to cover back late hour within same day.

End hour at 05:30pm, means 8hour work with 1hour breaktime,
and overtime until time out.

my excel now, 100% same like my references.
https://www.techrepublic.com/blog/wi...-excel-246146/

Can you help me how to create the correct formula?

Thank you.

10. ## Re: Calculate Late and Under time

Sunway2017,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

11. ## Re: Calculate Late and Under time

Dear protonLeah,
Apologizes for this issue. can you teach me how to make new thread? i try to make my own thread yesterday, but i really cant find. Thank you sir.

12. ## Re: Calculate Late and Under time

Dear protonLeah,
thank you for your kind help, now i understand how to make new thread.
Thank you very much :D

for blues_oo7 and Excel Forum,
i am really sorry for making trouble on another member thread. i will not do same issue in future.

13. ## Re: Calculate Late and Under time

Salary rate per day/8=hours per day/60mins equivalent to 1 hour X Minutes of late

14. ## Re: Calculate Late and Under time

=641.51/8=hours per day/60*Minutes of late

15. ## Re: Calculate Late and Under time

=(Salary/8)*hours of late+(salary/8/60)*minutes of late

Page 1 of 2 1 2 Last

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1