+ Reply to Thread
Results 1 to 10 of 10

how to split date results with SUMIFS

  1. 09-30-2015, 03:41 PM #1
    graphicjunkie
    graphicjunkie is offline
    Registered User
    Join Date
    09-26-2015
    Location
    Colorado, USA
    MS-Off Ver
    Office 10
    Posts
    11

    how to split date results with SUMIFS

    You guys were a huge help with my initial question on this. Thanks! (http://www.excelforum.com/excel-new-...correctly.html)

    However, I need to know how to take it one step further.

    How can I make it split the count if the start date is at the end of one month, and ends after the start of the next

    START END TOTAL HOURS
    06/30/2015 11:00 PM 07/01/2015 03:00 AM 4

    On the reporting page, row 9 has these calculations. This should really break it out as June having 1 hour, and July having 3 hours for this job.

    And further more, at the end of the year. If a job is running after midnight, the time spend on the next day needs to be calculated on that day.
    As is, it groups everything via the start date.


    My spreadsheet:
    DROP BOX: https://www.dropbox.com/s/1cmsbspoyq...ISED.xlsx?dl=0
    Reply With QuoteRegister To Reply
  2. 10-02-2015, 01:43 PM #2
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: how to split date results with SUMIFS

    Subtract the start time from the end time

    A
    B
    C
    1
    		 Start End
    2
    30/06/2015 11:00 PM
    01/07/2015 3:00 AM
    4:00
    3
    		 =B2-A2


    Sorry, misunderstood.
    Last edited by newdoverman; 10-02-2015 at 01:46 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  3. 10-02-2015, 02:22 PM #3
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: how to split date results with SUMIFS

    With Start time in A2 and End time in B2 enter this in C2 to give the time from start to end or to end of day:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Enter this in D2 and calculates if the period goes into a second day
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    For the total enter the following and format as [h]:mm
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    A
    B
    C
    D
    E
    1
    		 Start End Day 1 Day2 Total
    2
    01/06/2015 1:00 AM
    01/06/2015 3:00 AM
    2:00
    2:00
    3
    01/06/2015 10:00 PM
    02/06/2015 4:30 AM
    2:00
    4:30
    6:30
    4
    01/06/2015 9:15 AM
    02/06/2015 10:00 AM
    14:45
    10:00
    24:45
    Reply With QuoteRegister To Reply
  4. 10-02-2015, 02:26 PM #4
    graphicjunkie
    graphicjunkie is offline
    Registered User
    Join Date
    09-26-2015
    Location
    Colorado, USA
    MS-Off Ver
    Office 10
    Posts
    11

    Re: how to split date results with SUMIFS

    Thanks...I will try that.
    Reply With QuoteRegister To Reply
  5. 10-02-2015, 03:34 PM #5
    graphicjunkie
    graphicjunkie is offline
    Registered User
    Join Date
    09-26-2015
    Location
    Colorado, USA
    MS-Off Ver
    Office 10
    Posts
    11

    Re: how to split date results with SUMIFS

    Actually, after looking at this compared to my existing worksheet...is there a way to incorporate additional formula code into the sumifs command to achieve this?
    I would like to pursue that first before having to completely recode my worksheet.

    From a job collection standpoint(MF_CREMATIONS sheet), we don't care if the job spans over night, into the next calendar month or year. For that data set, it's just another job.
    For the reporting (MF_REPORTING), that's where it has to show it.
    Reply With QuoteRegister To Reply
  6. 10-02-2015, 04:35 PM #6
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: how to split date results with SUMIFS

    The link to your data doesn't work so I don't know what you are working with.


    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Reply With QuoteRegister To Reply
  7. 10-03-2015, 11:54 AM #7
    graphicjunkie
    graphicjunkie is offline
    Registered User
    Join Date
    09-26-2015
    Location
    Colorado, USA
    MS-Off Ver
    Office 10
    Posts
    11

    Re: how to split date results with SUMIFS

    Not sure why my original doesn't work. But here is the most up to date with the requested actual and desired sheets.

    Here is the link to dropbox. It won't let me attach it.
    https://www.dropbox.com/s/1cmsbspoyq...ISED.xlsx?dl=0
    Reply With QuoteRegister To Reply
  8. 10-03-2015, 01:42 PM #8
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: how to split date results with SUMIFS

    I think that I have this figured out. I amended the MF_CREMATIONS worksheet to include columns for Day 1, Day 2, Monthly Carryover to go with the TOTALS column that you already have.
    Formula for Day 1
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

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

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

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


    Formula for DESIRED_REPORTING!B9 filled across:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 10-03-2015, 02:06 PM #9
    graphicjunkie
    graphicjunkie is offline
    Registered User
    Join Date
    09-26-2015
    Location
    Colorado, USA
    MS-Off Ver
    Office 10
    Posts
    11

    Re: how to split date results with SUMIFS

    Thanks Newdoverman. That works perfect!!! Reps added!
    Now I get to hack that apart to figure out what is going on.
    Reply With QuoteRegister To Reply
  10. 10-03-2015, 04:18 PM #10
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: how to split date results with SUMIFS

    I'm glad that it worked out for you and thank you for the rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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. Sumifs returning inconsistent results
    By WilliamWelch in forum Excel General
    Replies: 9
    Last Post: 03-26-2015, 05:08 PM
  2. Split Positive and Negative results
    By andreasnw in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2014, 12:37 PM
  3. Inaccurate results of SUMIFS and SUM function
    By Ganesh7299 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2013, 11:13 AM
  4. Filtering data from the results of a sumifs formula
    By marks_28 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-27-2013, 11:55 AM
  5. [SOLVED] Combining correlation results and SumIFs
    By ilikeexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2012, 10:02 AM
  6. SUMIFS with one condition for two results in one cell?
    By Davzx in forum Excel General
    Replies: 3
    Last Post: 05-26-2012, 08:02 AM
  7. split results to multiple worksheets
    By Rich in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-11-2005, 03:06 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