+ Reply to Thread
Results 1 to 4 of 4

Formula count of hours between start/finish times

  1. #1
    Registered User
    Join Date
    02-14-2011
    Location
    Copenhagen
    MS-Off Ver
    Excel 2007
    Posts
    5

    Formula count of hours between start/finish times

    Hello every body,

    I am creating a log of hours worked, so my input is start/finish times in hh:mm format, and I wish for excel to calculate automatically specific hours between start/finish times.

    example:
    start: 18:00
    finish 06:00

    night hours: I help in finding a formula to count the total hours 22:00 to 04:00 giving 6,0

    Any help appreciated
    robenson
    Last edited by robenson; 02-14-2011 at 04:22 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Formula count of hours between start/finish times

    Try =MOD(A2-A1, 1)

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula count of hours between start/finish times

    In terms of night shift - if we assume B2 holds Finish and A2 start then:

    Please Login or Register  to view this content.
    edit: was missing 22:00 to midnight
    Last edited by DonkeyOte; 02-14-2011 at 04:56 AM.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Formula count of hours between start/finish times

    Assuming that shift could be any length up to 24 hours, e.g. even 03:00 until 22:30, for example, then try this formula in C2 where A2 is shift start and B2 shift end

    =MOD(B2-A2;1)*24-(A2>B2)*18-MEDIAN(4;22;B2*24)+MEDIAN(4;22;A2*24)

    format C2 as number to get decimal hours
    Audere est facere

+ 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