+ Reply to Thread
Results 1 to 9 of 9

Calculate based on date and time range

  1. #1
    Registered User
    Join Date
    05-02-2012
    Location
    NC
    MS-Off Ver
    Excel 2011 - Macintosh
    Posts
    21

    Calculate based on date and time range

    Hi,

    I have a file that is already totaling needed values based on an entered date range.
    Now I would like to see if a way exist to narrow it by time of day also.
    One problem is the time is recorded in two columns with the AM/PM in the second column.

    new.jpg

    Looking at the attached file can the formula in the green box be adjusted to accept a time range being added in the red boxes?
    It would still need to work even if the time range is left blank.

    I'd love to know how to chart this in 8 hour increments but that is another day.

    Thanks for any suggestions.
    Attached Files Attached Files
    Last edited by local1; 09-23-2015 at 08:04 AM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,419

    Re: Calculate based on date and time range

    Hi,

    Just extend the SUMIFS to include column D & the J3:K3 cells. i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-02-2012
    Location
    NC
    MS-Off Ver
    Excel 2011 - Macintosh
    Posts
    21

    Re: Calculate based on date and time range

    Very Nice.

    How do I account for the AM/PM time periods? This data is over a 24 hour period. Right now column D has a 12 hour cycle with the time period (AM/PM) recorded in column E

    So if a 1 AM - 6 AM range needs to be applied how would that be done?
    Or do D & E need to be combined somehow? They are separated in the original data.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,110

    Re: Calculate based on date and time range

    =sumproduct(($c$5:$c$357+$d$5:$d$357+($d$5:$d$357<=0.5)*($e$5:$e$357="pm")*0.5>=$h3+if(or($j3=0,$k3=0),0,$j3))*($c$5:$c$357+$d$5:$d$357+($d$5:$d$357<=0.5)*($e$5:$e$357="pm")*0.5<=$i3+if(or($j3=0,$k3=0),1,$k3))*($b$5:$b$357))
    Please Login or Register  to view this content.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Registered User
    Join Date
    05-02-2012
    Location
    NC
    MS-Off Ver
    Excel 2011 - Macintosh
    Posts
    21

    Re: Calculate based on date and time range

    That does seem to do the trick. Thanks to you both.

    The second one is a bit more complex for me this early before my needed coffee :-)



    So for the holy grail - do you have the magic mouse clicks that would turn this into a running chart, per day, broken into three 8 hour increments that begin at 7am?

    Even if you don't the help is very appreciated.

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,419

    Re: Calculate based on date and time range

    Hi,

    Personally I'd add a helper column and join all three columns C:E
    i.e. in F5 copied down

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then your SUMIFS is simplified to

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-02-2012
    Location
    NC
    MS-Off Ver
    Excel 2011 - Macintosh
    Posts
    21

    Re: Calculate based on date and time range

    That does make edits easier.

    On the copied down part - that seems to make the file huge since it pre-fills the entire column with the formula.
    Does a way exist to auto fill the needed formula as needed? Such as when the needed data has been populated into previously unused cells the formula is added in then.

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,419

    Re: Calculate based on date and time range

    Hi,

    Yes indeed. A common solution is to hold the master formula above the data somewhere and then either copy it and paste it to the new rows, then copy the formula you've just put in the new rows and paste back as values.

    Personally I just create a macro to perform the task above if the file is getting large solely because of the formulae.

    However I'd be interested to hear how big the file is and how many rows the formula has been copied to. Are you sure it hasn't accidentally been copied to all 1 million + rows?

  9. #9
    Registered User
    Join Date
    05-02-2012
    Location
    NC
    MS-Off Ver
    Excel 2011 - Macintosh
    Posts
    21

    Re: Calculate based on date and time range

    The hope is to not have to come back and paste in formulas as the data grows.

    Right now only one data source has been added. Once all is working as desired 7 more sources will be referenced so all can be consolidated.

    File size is just over 10 megs. And sheepishly admitting that the formula has been pasted to countless cells - the entire column.
    So, can you point to a posting that simply shows how to use a macro as mentioned? One that I suppose would look at a cell, determine that data is present, then add a formula to a cell in designated column on same row as new data that is recognized.
    Last edited by local1; 09-25-2015 at 08:37 AM.

+ 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. Calculate Effective Date based on Another Date Range
    By Alphabex in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-28-2015, 01:43 PM
  2. Calculate End Date & Time Based on User Input
    By sdawson83 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-23-2015, 06:36 AM
  3. [SOLVED] Calculate time remaining based on end date & time and current date
    By Kaz09 in forum Excel Formulas & Functions
    Replies: 33
    Last Post: 08-19-2014, 07:11 AM
  4. Auto Insert Date and time and calculate Age based .
    By SANU PN in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-15-2014, 02:08 AM
  5. Calculate Date Based On System Time
    By jhudson444 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-23-2013, 10:28 PM
  6. Calculate ship date based on date and time order is received
    By joekomar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2013, 10:57 PM
  7. Calculate a new date/time based on the input
    By akabraha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2007, 06:30 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