date intervals with a twist

[kocab]

Hi

I have an excel problem that I have a hard time solving. Assume having a couple of date intervals:

from to
2012-08-01 2014-07-31

2013-04-05 2014-07-31

How do I count how many days in say april 2013 does the two intervals above "cover". Is there a formula that gives the answer 30(days) in the first interval and 26 in the second.

Would be extremly grateful for an answer!

 

[Redwolfx]

I'm sorry, I had a formula and part of it got deleted upon posting, I've cleared my Excel and I have to leave, I'll work on this again soon.

 

[Rick Rothstein]

Assume having a couple of date intervals:

from to
2012-08-01 2014-07-31

2013-04-05 2014-07-31

How do I count how many days in say april 2013 does the two intervals above "cover". Is there a formula that gives the answer 30(days) in the first interval and 26 in the second.

It would have helped if you told us where "things" were located at so we didn't have to make them up only for you to have to figure out how to change them for your actual layout. Nonetheless, assume A1 contains the year you want to search for and B1 contains the first three letters of the month's name, and A2 on down contains the start year and B2 on down contains the end year, this formula will return the number you want...

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&B2)),"yyyymmm")=A$1&B$1))

 

[kocab]

Im so sorry for the inconvinence. This is my first post so I haven't learn the correct way to post to post yet. It is as you assumed with the cells and i will try your formula.

Thanks alot!

 

[kocab]

It didn´t go so well when I tried your formula so I solved it as described below (probably not the best way )

[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD]from[/TD]
[TD]to[/TD]
[TD]from "remade" to measure april 2013[/TD]
[TD]to "remade" to measure april 2013[/TD]
[TD]number of days in april 2013[/TD]
[/TR]
[TR]
[TD="align: right"]2012-08-01[/TD]
[TD="align: right"]2013-07-31[/TD]
[TD="align: right"]2013-04-01[/TD]
[TD="align: right"]2013-04-30[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]







[TABLE="class: grid, width: 1550, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2012-08-01[/TD]
[TD]2013- 07-31[/TD]
[TD]<date(2013;4;1);date(2013;4;1);

=IF(A2<date(2013;4;1);date(2013;4;1);if(and(a2>DATE(2013;3;31);A2<date(2013;5;1));a2;""))

<date(2013;5;1));a2;""))< td=""></date(2013;5;1));a2;""))<></date(2013;5;1));a2;""))
</date(2013;4;1);date(2013;4;1);if(and(a2></date(2013;4;1);date(2013;4;1);
[/TD]
[TD]=IF(B2>DATE(2013;4;29);DATE(2013;4;30);
IF(AND(B2<date(2013;5;1);
B2>DATE(2013;3;31));B2;""))</date(2013;5;1);
[/TD]
[TD]=D2-C2+1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

Regarding your reported post and your problem with formulas not showing properly. That usually happens when you have a < sign followed by a letter. The solution is to put a space after the < sign in your formula. For example, I have posted the same formula twice below. The first one gets truncated but the second one is fine because of the space following the < sign.

=IF(A2<B2,1,0)

=IF(A2< B2,1,0)

 

[Rick Rothstein]

It didn´t go so well when I tried your formula...

I am not sure why it did not work for you as I tested it before I posted it... if A1 contained the year, B1 contained the first three letters of the month name, and both A2 and B2 contained real Excel dates, then the formula worked for me. I'm sorry you had problems with it.

 

[Peter_SSs]

Is this any use to you?

C1 houses the date being the first of the month of interest (1 April 2013 in this case). I have just formatted C1 to show month and year.

C2 is copied down.

Excel Workbook
	A	B	C
1	From	To	Apr 2013
2	01-Aug-12	31-Jul-14	30
3	05-Apr-13	31-Jul-14	26
4	01-Aug-12	05-Apr-13	5
5	05-Apr-13	07-Apr-13	3
6	01-Aug-12	07-Mar-13	0
7	05-May-14	09-May-14	0
8	31-Mar-13	01-May-13	30
9	31-Mar-13	30-Apr-13	30
10	01-Apr-13	01-Apr-13	1
Count Days

 

[kocab]

Thanks for the info

Welcome to the MrExcel board!

Regarding your reported post and your problem with formulas not showing properly. That usually happens when you have a < sign followed by a letter. The solution is to put a space after the < sign in your formula. For example, I have posted the same formula twice below. The first one gets truncated but the second one is fine because of the space following the < sign.

=IF(A2<b2,1,0)

=IF(A2< B2,1,0)

</b2,1,0)

 

[Peter_SSs]

Thanks for the info

No problem.
More importantly, did my suggestion in post #8 do what you wanted or did you end up getting Rick's formula to work, or some other solution or is your problem still unresolved?