[SOLVED] How to sum dates without overlap


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

 
 
Thread Tools Rate Thread Display Modes
  #1  
Old 24th February 2014, 13:52
rvz's Avatar
rvz rvz is offline
Junior Member
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use Formulae
 
Join Date: Feb 2014
Location: Amsterdam, Netherlands
Posts: 5
Posts Per Day: 0.00
My Top Tip Count: 0
Thanks to others: 3
Thanked 0 Times in 0 Posts
Reputation: 10 (4) rvz is on a distinguished road
Netherlands
Question How to sum dates without overlap
     Excel: Functions   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: Functions   

To define down time per system per month (in Excel 2007), I face problems excluding overlapping dates. Although our maintenance data covers multiple systems and recovery operations, in this simplified example I only show 1 system and 5 recovery operations. Since different operations can be executed at the same time, overlap should be excluded to sum system down time. Can anybody help me define a formula that can sum the down time in which overlapping operations are excluded?

system; operation; start; end; days down.
1; 1; 1-1-2013; 8-1-2013; 8.
1; 2; 2-1-2013; 4-1-2013; 3.
1; 3; 6-1-2013; 8-1-2013; 3.
1; 4; 7-1-2013; 12-1-2013; 6.
1; 5; 21-1-2013; 31-1-2013; 11.

sum days down 31
sum days down overlap excluded 23

I really tried to solve this, but so far I was not able to fix this with only formulas.. Any help please???



The Code Cage Advertisment
Advertisement
  #2  
Old 24th February 2014, 14:45
NBVC's Avatar
NBVC NBVC is offline
The Code Cage Staff
MS Office Version: Excel 2003 - 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2008
Location: Mississauga Canada
Posts: 1,364
Posts Per Day: 0.42
My Top Tip Count: 2
Thanks to others: 64
Thanked 81 Times in 81 Posts
Reputation: 1435 (36) NBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud of
2 Top Tips
Canada
Default Re: How to sum dates without overlap

------ Register to get rid of these "In Post" ads! ------


Assuming your data is in A1:E6 (including headers)

Add another "helper column".

In F2 enter formula:

My Formula:
=E2


and in F3 enter formula:
My Formula:
=SUMPRODUCT(--ISNA(MATCH(ROW(INDIRECT(C3&":"&D3)),ROW(INDIRECT(MIN(C$2:C2)&":"&MAX(D$2:D2))),0)))


copied down

Then you can sum that column

e.g.
My Formula:
=SUM(F2:F6)
gives 23.
__________________
Where there is a will there are many ways. You just have to pick one!
Microsoft MVP - Excel



1 members found this post helpful.
The Following User Says Thank You to NBVC For This Useful Post:
rvz (27th February 2014)
  #3  
Old 24th February 2014, 16:53
rvz's Avatar
rvz rvz is offline
Junior Member
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use Formulae
 
Join Date: Feb 2014
Location: Amsterdam, Netherlands
Posts: 5
Posts Per Day: 0.00
My Top Tip Count: 0
Thanks to others: 3
Thanked 0 Times in 0 Posts
Reputation: 10 (4) rvz is on a distinguished road
Netherlands
Default Re: How to sum dates without overlap

------ Register to get rid of these "In Post" ads! ------


Awesome! Thank you very much for your quick respond!!!!!



The Code Cage Advertisment
Advertisement
  #4  
Old 25th February 2014, 12:42
rvz's Avatar
rvz rvz is offline
Junior Member
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use Formulae
 
Join Date: Feb 2014
Location: Amsterdam, Netherlands
Posts: 5
Posts Per Day: 0.00
My Top Tip Count: 0
Thanks to others: 3
Thanked 0 Times in 0 Posts
Reputation: 10 (4) rvz is on a distinguished road
Netherlands
Question Re: How to sum dates without overlap

------ Register to get rid of these "In Post" ads! ------


NBVC thank you again, your formula definitely works perfectly for one single system. However, right now I'm trying to adjust your formula to our database and found a new challenge. By selecting each single system by hand and adjusting your formula, I am able to define down time per system excluding overlap. But, how can I sum the individual down time per system without overlap using one combined formula like you showed above?

system; operation; start; end; days down.
1; 11; 1-1-2013; 8-1-2013; 8.
1; 12; 2-1-2013; 4-1-2013; 3.
1; 13; 6-1-2013; 8-1-2013; 3.
1; 14; 7-1-2013; 12-1-2013; 6.
1; 15; 21-1-2013; 31-1-2013; 11.
2; 21; 7-1-2013; 12-1-2013; 6.
2; 22; 8-1-2013; 9-1-2013; 2.
2; 23; 23-1-2013; 29-1-2013; 7.
3; 31; 14-1-2013; 17-1-2013; 4.
3; 32; 16-1-2013; 22-1-2013; 7.
4; 41; 27-1-2013; 29-1-2013; 3.

sum days down 60
sum days down overlap per system excluded 48
sum days down system 1 overlap excluded 23
sum days down system 2 overlap excluded 13
sum days down system 3 overlap excluded 9
sum days down system 4 overlap excluded 3



  #5  
Old 25th February 2014, 14:33
NBVC's Avatar
NBVC NBVC is offline
The Code Cage Staff
MS Office Version: Excel 2003 - 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2008
Location: Mississauga Canada
Posts: 1,364
Posts Per Day: 0.42
My Top Tip Count: 2
Thanks to others: 64
Thanked 81 Times in 81 Posts
Reputation: 1435 (36) NBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud of
2 Top Tips
Canada
Default Re: How to sum dates without overlap

------ Register to get rid of these "In Post" ads! ------


Try this:

In F2:

My Formula:
=SUMPRODUCT(--ISNA(MATCH(ROW(INDIRECT(C2&":"&D2)),ROW(INDIRECT(MIN(INDEX(C$1:C1,MATCH(A2,A$1:A1,0)):C1)&":"&MAX(INDEX(D$1:D1,MATCH(A2,A$1:A1,0)):D1))),0)))


copied down.

then if you list the unique system numbers, say in J2 down, then in K2 use formula:

My Formula:
=SUMIF(A:A,J2,F:F)


copied down (where A:F contains the data, and J2 contains the number 1 for system 1)
__________________
Where there is a will there are many ways. You just have to pick one!
Microsoft MVP - Excel



1 members found this post helpful.
The Following User Says Thank You to NBVC For This Useful Post:
rvz (27th February 2014)
  #6  
Old 27th February 2014, 10:17
rvz's Avatar
rvz rvz is offline
Junior Member
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use Formulae
 
Join Date: Feb 2014
Location: Amsterdam, Netherlands
Posts: 5
Posts Per Day: 0.00
My Top Tip Count: 0
Thanks to others: 3
Thanked 0 Times in 0 Posts
Reputation: 10 (4) rvz is on a distinguished road
Netherlands
Default Re: How to sum dates without overlap

------ Register to get rid of these "In Post" ads! ------


Good morning NBVC,

Right now I am able to use your formula for our database. To take it to a next level I am now trying to sum per system without overlap per month. To do so, I am trying to make two helper columns adjusting the operation dates to the month I report on.

I made a formula for each possibility an operation can be in, (partly) during the month, or not. Do I miss something?

How can these formulas (M2:N13) be turned into two formulas that can fill in the ""adjusted"" start and end dates (C2:D30)? Because then I can add your previous formula to report per system per month without overlap.

Thanks again!!!

RVZ
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.



The Code Cage Advertisment
Advertisement
  #7  
Old 27th February 2014, 14:15
NBVC's Avatar
NBVC NBVC is offline
The Code Cage Staff
MS Office Version: Excel 2003 - 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2008
Location: Mississauga Canada
Posts: 1,364
Posts Per Day: 0.42
My Top Tip Count: 2
Thanks to others: 64
Thanked 81 Times in 81 Posts
Reputation: 1435 (36) NBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud of
2 Top Tips
Canada
Default Re: How to sum dates without overlap

------ Register to get rid of these "In Post" ads! ------


I don't understand this request. Please give expected results with explanation on how achieved.

Also, I don't see system numbers. Is that going to be another next step? Please try to consolidate what you are looking for all in one, instead of all these unrelated steps. It will expedite the process.
__________________
Where there is a will there are many ways. You just have to pick one!
Microsoft MVP - Excel



  #8  
Old 27th February 2014, 17:42
rvz's Avatar
rvz rvz is offline
Junior Member
MS Office Version: MS Office 97, 2003, 2007,2010
MS Office Skill Level: Can use Formulae
 
Join Date: Feb 2014
Location: Amsterdam, Netherlands
Posts: 5
Posts Per Day: 0.00
My Top Tip Count: 0
Thanks to others: 3
Thanked 0 Times in 0 Posts
Reputation: 10 (4) rvz is on a distinguished road
Netherlands
Default Re: How to sum dates without overlap

------ Register to get rid of these "In Post" ads! ------


Sorry you're right, after each step I try to figure out myself how to adjust your formulas. Our database contains columns A:E. Your earlier formulas are used in columns H:K, so far they work perfectly, column K is what I need.

However, I filled helper columns F:G by hand to be able to state down time per period (in this case December 2013).

Column F:
"-" when the operation did not take place in December 2013;
"1-12-13" when the operation started before 1-12-13 and ended 1-12-13 or later;
"original start date" when the operation started in December 2013.

Column G:
"-" when the operation did not take place in December 2013;
"31-12-13" when started in or before December 2013 and ended after 31-12-2013;
"original end date" when the operation ended in December 2013.

How can helper columns F:G be filled automatically with the dates a system was down in December 2013?
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.



  #9  
Old 28th February 2014, 14:41
NBVC's Avatar
NBVC NBVC is offline
The Code Cage Staff
MS Office Version: Excel 2003 - 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2008
Location: Mississauga Canada
Posts: 1,364
Posts Per Day: 0.42
My Top Tip Count: 2
Thanks to others: 64
Thanked 81 Times in 81 Posts
Reputation: 1435 (36) NBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud of
2 Top Tips
Canada
Default Re: How to sum dates without overlap

------ Register to get rid of these "In Post" ads! ------


In F2, try:

My Formula:
=IF(OR($C2>DATE(2013,12,31),$D2

copied down

and in G2:

My Formula:
=IF(OR($C2>DATE(2013,12,31),$D2

A more flexible alternative, if you are going to test various months, would be to dedicate a cell with the date of the fist of the month of interest... Say in N1 you enter Dec 1, 2013

Then formula in F2 becomes:

My Formula:
=IF(OR($C2>EOMONTH($N$1,0),$D2<$N$1),0,MAX($C2,$N$1))


and in G2:

My Formula:
=IF(OR($C2>EOMONTH($N$1,0),$D2<$N$1),0,MIN($D2,EOMONTH($N$1,0)))


both copied down.

Now when you change date in N1 to Nov 1, 2013, for example, all the formulas auto-revise accordingly.
__________________
Where there is a will there are many ways. You just have to pick one!
Microsoft MVP - Excel



1 members found this post helpful.
The Following User Says Thank You to NBVC For This Useful Post:
rvz (10th March 2014)
The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
dates, overlap, sum

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 02:06.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.20686 seconds
  • Memory Usage 6,677KB
  • Queries Executed 18 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (4)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (9)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (9)post_thanks_box
  • (3)post_thanks_box_bit
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (3)post_thanks_postbit
  • (9)post_thanks_postbit_info
  • (9)postbit
  • (2)postbit_attachment
  • (9)postbit_ctoptip
  • (9)postbit_onlinestatus
  • (45)postbit_reputation
  • (9)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (3)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • reputation_power
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • error_fetch
  • post_thanks_function_fetch_thanks_bit_start
  • post_thanks_function_show_thanks_date_start
  • post_thanks_function_fetch_thanks_bit_end
  • post_thanks_function_fetch_post_thanks_template_start
  • post_thanks_function_fetch_post_thanks_template_end
  • postbit_attachment
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete