+ Reply to Thread
Results 1 to 2 of 2

Count the Hours between certain intervals

  1. #1
    Registered User
    Join Date
    12-23-2014
    Location
    Ontario, Canada
    MS-Off Ver
    2007
    Posts
    9

    Count the Hours between certain intervals

    Good Afternoon,

    I am looking to calculate the total hours worked between certain intervals.

    Column A - Date
    Column B - Start Time
    Column C - End Time
    Column D - Time Between 6pm to 12am
    Column E - Time Between 12am and 7am

    I've tried used IF formula's but keep coming up with unusual numbers. Perhaps I am missing something

    Any help is appreciated

    Regards,
    Last edited by Ixorian; 08-26-2015 at 04:07 PM.

  2. #2
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Count the Hours between certain intervals

    I'm assuming the Start time and End time can only fall within the same date. I.e. no start date 1-Jan and finish 2-Jan next day.

    Using some dummy numbers. Here's what I got.
    Row\Col
    A
    B
    C
    D
    E
    1
    Date Start time End Time 1800:2400 0000:0700
    2
    Dates 8:00 PM 12:00 AM 4 -
    3
    Dates 3:00 AM 6:00 AM - 3
    4
    Dates 4:00 PM 11:00 PM 5 -
    5
    Dates 4:00 AM 12:00 PM - 3


    Formula in D2 - copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula in E2 - copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Change the section in red to whatever time limit you want.
    Note that, TIME(24,0,0) and TIME(0,0,0) is the same, i.e. =0. Hence, if you want 12AM as an end shift, you need to put in a 1 (see formula D2).

+ 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: 6
    Last Post: 07-17-2012, 04:49 AM
  2. Replies: 6
    Last Post: 03-25-2010, 07:50 AM
  3. Replies: 18
    Last Post: 09-06-2005, 11:05 AM
  4. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Domenic in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 09-06-2005, 04:05 AM
  5. Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  7. Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  8. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 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