+ Reply to Thread
Results 1 to 2 of 2

Sumproduct Array not recognizing time after midnight

  1. 02-13-2019, 01:41 PM #1
    NorthBear
    NorthBear is offline
    Registered User
    Join Date
    03-28-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    24

    Sumproduct Array not recognizing time after midnight

    Howdy -

    I received help via this forum some time ago to help wrap up this sheet, but I'm having trouble getting the formula to recognize and display headcount after 12:00 AM Midnight. Any help figuring out the error would be appreciated. I'll explain how this sheet works and then attach it!

    Team Sheet: Consists of start times, end times, days off, and the number of people on said team. This is all input.

    Staffing Sheet: This sheet reads the information on the team tab and displays the number of people working during each 15 minute portion of the day. The problem lies in this sheet, as it suddenly stops displaying numbers after 12 Midnight. I've highlighted this portion in the sheet attached to.

    I've tried a number of different things, including the formatting, to try and correct this. Thanks for any help ahead of time!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-13-2019, 06:27 PM #2
    dflak
    dflak is offline
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,926

    Re: Sumproduct Array not recognizing time after midnight

    The best way to handle this is to include the "full date." Pretend that it is January 1, 1900 (start of the Excel universe as far as time calculations are concerned). If a person's start time is less than the end time then assume the person worked entirely on January 1. However if the person's start time is greater than the end time, then that means the person worked over midnight into the next day. So you need to calculate a new end time = end time + 1.

    End time to use in calculation = if (Start_Time < End_Time, End_Time, End_Time + 1)
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Want to extract time before and after midnight for a time range that spans midnight
    By schurchill39 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2018, 04:18 PM
  2. [SOLVED] Calculating time differences in 24 hour time when going past midnight
    By Thug in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-29-2018, 10:22 AM
  3. How Can I Work Out a time Before Midnight and After Midnight???
    By ArmandDataCube in forum Excel General
    Replies: 7
    Last Post: 08-23-2017, 06:13 PM
  4. [SOLVED] Text Time to Excel Format, Then Sort Time Past Midnight
    By BuntyMac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-27-2014, 03:13 AM
  5. SUMPRODUCT with ID numbers that has minus signs and using time as an array...
    By excellenthelp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-10-2013, 01:26 PM
  6. Excel 2007 : Notification time Plus - real time tracking PAST midnight with Conditional formatting
    By theymademeregister in forum Excel General
    Replies: 4
    Last Post: 01-11-2012, 07:59 PM
  7. SUMPRODUCT not recognizing text criteria
    By lallo.jr in forum Excel General
    Replies: 1
    Last Post: 05-10-2010, 10:42 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1