Results 1 to 16 of 16

sumproduct adding date to time range only works for same date

Threaded View

  1. #1
    Registered User
    Join Date
    12-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 365
    Posts
    17

    sumproduct adding date to time range only works for same date

    Hello

    I need to sum up minutes, if any of the specified time stamp (workbreak) falls within a time range.
    The specified time stamp cannot be hardcoded for the specific date. It needs to be only time as it is part of a standard work schedule.
    The time range can span several dates and the time stamp must be counted for every passing date.

    So far I have this, which works if both start and end time is on the same date:

    =SUMPRODUCT((INT(A5)+$H$3:$H$4>=A5)*(INT(A6)+$H$3:$H$4<=A6)*$I$3:$I$4)

    I've also tried sumifs, which also works if start and end time is on same date:
    =SUMIFS($I$3:$I$4;$H$3:$H$4;">="&MOD(A5;1);$H$3:$H$4;"<="&MOD(A6;1))

    A5 is start time
    A6 is end time
    H3:H4 is two timestamps which needs to be found
    I3:I4 is minutes which needs to be added for each timestamp

    The difference between the to methods is that with SUMPROCUCT I add the date to the time stamp. In the SUMIFS I remove the date from the start/end time, both to get the same format for time range and time stamp.

    I'm working in Excel365

    Can you help me, please?
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. adding date and time and subtracting date-time2 from date-time1
    By tinkerbelle in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-22-2018, 10:05 AM
  2. [SOLVED] Index Sumproduct on date from date time
    By SimChengKeng in forum Excel General
    Replies: 2
    Last Post: 11-19-2018, 12:07 PM
  3. Coloring cells based on date range and adding a shape at a certain date
    By moomphas in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2018, 05:17 AM
  4. Find a date time range within start and stop date time?
    By Rodney Fernandes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-31-2017, 02:19 AM
  5. Replies: 3
    Last Post: 09-20-2016, 03:13 PM
  6. Replies: 2
    Last Post: 05-28-2014, 06:52 AM
  7. Adding Date and Time to Yield Date
    By fengfeng in forum Excel General
    Replies: 1
    Last Post: 01-05-2009, 02:11 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