+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 17

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,186

    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 2007
    Posts
    570

    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,902

    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 2007
    Posts
    570

    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 2007
    Posts
    570

    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/MSO200;Win10/MSO2016
    Posts
    10,220

    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.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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