+ Reply to Thread
Results 1 to 8 of 8

time calculation without lunch hour

  1. #1
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    time calculation without lunch hour

    Hi

    Please find attached file for your ref. Can someone please
    1) provide a formula that can minus out 12pm to 1pm lunch hour in column F?
    2) provide a good website that explain times function in excel?


    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: time calcuation without lunch hour

    Hi,

    you could subtract -1/24 (one hour)

    =C1-B1-1/24

    Hope it helps


    http://www.cpearson.com/excel/datearith.htm
    Last edited by canapone; 01-27-2012 at 11:02 PM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: time calcuation without lunch hour

    CANAPONE's solution works if the shift always includes the time from 12-1. Harrold, that's what your examples show but is that always the case? Your formulas seem to show that you have a good grasp on time calculations. Here is a page on dates and times from a very good Excel site.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    Re: time calcuation without lunch hour

    Hi Canapone,

    Thanks for your reply.

    i am doing payroll and there lots of O/T claims. If possible, i would like to find a formula that can automate deduct 12pm-1pm from the calculation. i prefer not to manually amend some of the formula where those time are involved. It resulted formula not consisitent.

    Thanks

  5. #5
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    Re: time calcuation without lunch hour

    Hi 6StringJazzer,


    Harrold, that's what your examples show but is that always the case?
    No, it is not.

    Basically, everything is done manually. the workers fill in the OT form stating OT start & finish. Some of them cheated by include normal working hours, same OT to claim more than once, etc. The supervisors approve these OT without checking properly. So now, i need to use Excel to calculate the actual OT hours. and with a formula to calculate without lunch hour will make my work easier.

    Thanks

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: time calcuation without lunch hour

    Hi,

    example: the formula does not consider hours or minutes from 12 am to 1 pm

    Please Login or Register  to view this content.
    Regards
    Last edited by canapone; 01-27-2012 at 11:46 PM.

  7. #7
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    Re: time calculation without lunch hour

    Hi Canapone,

    I tried your formula and dont know whether i have put in the correct cell. Is it possible to upload a file for me?

    Really appreciate for your advice.

    Thanks

  8. #8
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: time calculation without lunch hour

    Hi,

    after double-checking my previous attempt, I prefer


    Please Login or Register  to view this content.
    The formula cuts off period 12:00 am 1:00 pm

    See the example attached

    Let me know if you get unwanted results.

    You'll get for sure better solutions.

    Regards
    Attached Files Attached Files
    Last edited by canapone; 01-29-2012 at 02:51 AM. Reason: no ";" in American standard formula

+ 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