+ Reply to Thread
Results 1 to 6 of 6

Need formula to calculate number of hours in a time span that are between 6PM and 6AM

  1. #1
    Registered User
    Join Date
    08-02-2012
    Location
    Indianapolis, IN
    MS-Off Ver
    2007
    Posts
    68

    Question Need formula to calculate number of hours in a time span that are between 6PM and 6AM

    Our employees work many different shifts. Whatever shift they work, they get night premium for those hours that fall between 6PM and 6AM the next morning. Some shifts start and end before midnight, and other shifts start before midnight and go until after midnight, and some start before midnight and go until after 6AM, and others start at midnight and go until after 6AM. Below is an attachment showing some example shifts. I need a formula that will calculate the number of hours in the shift that fall between 18:00 and 06:00. If possible, I need the same formula to be used whether or not the shift crosses midnight. Thanks in advance for any help.

    Test.xlsx

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Need formula to calculate number of hours in a time span that are between 6PM and 6AM

    How about:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Registered User
    Join Date
    08-02-2012
    Location
    Indianapolis, IN
    MS-Off Ver
    2007
    Posts
    68

    Re: Need formula to calculate number of hours in a time span that are between 6PM and 6AM

    Quote Originally Posted by ConneXionLost View Post
    How about:

    Please Login or Register  to view this content.
    That gives me the total hours. I need it to give me the hours during that span that fall between 6PM and 6AM.

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

    Re: Need formula to calculate number of hours in a time span that are between 6PM and 6AM

    Try this formula in C2 copied down

    =MOD(B2-A2,1)*24-(B2<A2)*12-MEDIAN(B2*24,6,18)+MEDIAN(A2*24,6,18)

    format as number
    Audere est facere

  5. #5
    Registered User
    Join Date
    08-02-2012
    Location
    Indianapolis, IN
    MS-Off Ver
    2007
    Posts
    68

    Re: Need formula to calculate number of hours in a time span that are between 6PM and 6AM

    Thanks daddylonglegs. That worked for me.

  6. #6
    Registered User
    Join Date
    11-05-2014
    Location
    Wisconsin
    MS-Off Ver
    2010
    Posts
    1

    Re: Need formula to calculate number of hours in a time span that are between 6PM and 6AM

    =MOD(B2-A2,1)*24-(B2<A2)*12-MEDIAN(B2*24,6,18)+MEDIAN(A2*24,6,18)

    How would this formula read if you wanted to calculate time span between midnight and 4AM?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 09-21-2012, 12:58 AM
  2. Replies: 1
    Last Post: 11-02-2011, 06:50 PM
  3. Replies: 2
    Last Post: 01-21-2010, 11:09 AM
  4. Time Span in hours
    By Juncus in forum Excel General
    Replies: 1
    Last Post: 11-08-2008, 10:41 AM
  5. Replies: 5
    Last Post: 06-07-2008, 02:32 PM

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