+ Reply to Thread
Results 1 to 6 of 6

Counting values in a time range - when the time range crosses midnight

  1. #1
    Registered User
    Join Date
    06-30-2013
    Location
    Cairns, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Counting values in a time range - when the time range crosses midnight

    Hi,

    I receive data from 2 different programs in an excel format and I am trying to count the number of appointments performed by a person in Table1 during a specific time period in table 2. I have been reasonably successful when the hours are with the same 24hr period but I am having trouble crossing the midnight barrier, for example how many scans in table 1 were performed during the time period 23:50 to 00:40 in table 2. Any suggestions would be much appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Counting values in a time range - when the time range crosses midnight

    try the formula in the attached workbook.
    When the working hours go through midnight, you have to change the date.
    Hope this help
    Attached Files Attached Files
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Counting values in a time range - when the time range crosses midnight

    Try in O4;
    Please Login or Register  to view this content.
    Column M & N may not be used with this formula, it can be deleted if needed.
    Quang PT

  4. #4
    Registered User
    Join Date
    06-30-2013
    Location
    Cairns, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Counting values in a time range - when the time range crosses midnight

    Thanks very much. That was most helpful and did the trick nicely

  5. #5
    Registered User
    Join Date
    06-30-2013
    Location
    Cairns, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Counting values in a time range - when the time range crosses midnight

    i didn't quite get this one to work but I think that could be me more than anything. Thanks for your help

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Counting values in a time range - when the time range crosses midnight

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

    And also since you have two solvers you should mention which is which... so the solvers can also check the formulas they gave for error and correct it for the benefit of other members.

    Regards,
    Vladimir


    Side note/ Tip:


    You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
    By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

+ 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. [SOLVED] Calculating total time worked between a range over midnight.
    By amurr in forum Excel General
    Replies: 10
    Last Post: 03-13-2019, 03:27 AM
  2. compare time values - current time with time in cell range?
    By wyattea in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-13-2013, 11:28 PM
  3. Help with counting how many are within a time range, for a specific time
    By snswilliams in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-29-2013, 02:19 PM
  4. Formula for Time Sheet that Crosses Midnight
    By imjuspiayin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2013, 10:24 PM
  5. Counting time ranges within a range
    By Ducatisto in forum Excel General
    Replies: 4
    Last Post: 10-17-2008, 02:16 AM

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