+ Reply to Thread
Results 1 to 19 of 19

Calculate Late and Under time

  1. #1
    Registered User
    Join Date
    02-08-2010
    Location
    Ghana
    MS-Off Ver
    Excel 2010
    Posts
    48

    Calculate Late and Under time

    Happy Day to all,

    Can you please help me,

    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. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Calculate Late and Under time

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


    click on the * Add Reputation if this was useful or entertaining.

  3. #3
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    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.
    Last edited by AliGW; 11-25-2017 at 03:31 AM. Reason: Code tags added.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    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
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    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. #6
    Registered User
    Join Date
    02-08-2010
    Location
    Ghana
    MS-Off Ver
    Excel 2010
    Posts
    48

    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. #7
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    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. #8
    Registered User
    Join Date
    02-08-2010
    Location
    Ghana
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Calculate Late and Under time

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

    THANK YOU IN ADVANCE
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-24-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Talking 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.

    please help me, i seek your guidance,

    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?

    your cooperation is highly appreciated.
    Thank you.

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    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.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new 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.
    Ben Van Johnson

  11. #11
    Registered User
    Join Date
    11-24-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    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. #12
    Registered User
    Join Date
    11-24-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    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. #13
    Registered User
    Join Date
    04-16-2019
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    4

    Re: Calculate Late and Under time

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

  14. #14
    Registered User
    Join Date
    04-16-2019
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    4

    Re: Calculate Late and Under time

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

  15. #15
    Registered User
    Join Date
    04-16-2019
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    4

    Re: Calculate Late and Under time

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

    i hope this will help you.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,394

    Re: Calculate Late and Under time

    Jhovanny - the question was asked 9 years ago - I doubt the solution is still needed, but thanks for trying.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  17. #17
    Registered User
    Join Date
    04-16-2019
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    4

    Re: Calculate Late and Under time

    Hahaha Welcome

  18. #18
    Registered User
    Join Date
    08-22-2018
    Location
    Manila, Philippines
    MS-Off Ver
    Office 2007
    Posts
    1

    Re: Calculate Late and Under time

    Please help for the syntax in excel to calculate late and undertime and to auto input the required time out:
    Work time is 8:00 but sliding to 8:30 therefore if you IN by 8:15 you should OUT by 5:15; if you In 8:35 you precisely have 5 minutes late and when you out by 4:00 ,you have and under time of 1 hour and thirty minutes. Can you help me for the syntax to compute:
    a) SHOULD OUT TIME
    b) Late
    c) undertime

    Thank you

  19. #19
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Calculate Late and Under time

    lhynne0813,
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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