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!

 

[billszysz]

Hi kocab,

From me, i think, easy to understanding formula
Cell C1 - date, always first day of the month (i.e 2014-04-01)
A2 and B2 - your dates.
C2 - formula

=IF(MIN(EOMONTH(C1,0),B2)-MAX(C1,A2)+1>0,MIN(EOMONTH(C1,0),B2)-MAX(C1,A2)+1,0)

Regards

 

[markmzz]

Maybe this:

Layout

[TABLE="width: 123"]
<tbody>[TR]
[TD="width: 63, bgcolor: transparent"]From[/TD]
[TD="width: 63, bgcolor: transparent"]To[/TD]
[TD="width: 39, bgcolor: transparent, align: right"]abr/13[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/08/2012[/TD]
[TD="bgcolor: transparent, align: right"]31/07/2014[/TD]
[TD="bgcolor: yellow, align: right"]30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]05/04/2013[/TD]
[TD="bgcolor: transparent, align: right"]31/07/2014[/TD]
[TD="bgcolor: yellow, align: right"]26[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/08/2012[/TD]
[TD="bgcolor: transparent, align: right"]05/04/2013[/TD]
[TD="bgcolor: yellow, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]05/04/2013[/TD]
[TD="bgcolor: transparent, align: right"]07/04/2013[/TD]
[TD="bgcolor: yellow, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/08/2012[/TD]
[TD="bgcolor: transparent, align: right"]07/03/2013[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]05/05/2014[/TD]
[TD="bgcolor: transparent, align: right"]09/05/2014[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]31/03/2013[/TD]
[TD="bgcolor: transparent, align: right"]01/05/2013[/TD]
[TD="bgcolor: yellow, align: right"]30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]31/03/2013[/TD]
[TD="bgcolor: transparent, align: right"]30/04/2013[/TD]
[TD="bgcolor: yellow, align: right"]30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/04/2013[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2013[/TD]
[TD="bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]01/03/2013[/TD]
[TD="bgcolor: transparent, align: right"]31/03/2013[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]02/03/2013[/TD]
[TD="bgcolor: transparent, align: right"]20/04/2013[/TD]
[TD="bgcolor: yellow, align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]************[/TD]
[TD="bgcolor: transparent"]**********[/TD]
[TD="bgcolor: transparent"]******[/TD]
[/TR]
</tbody>[/TABLE]

Formula

In C2

=MAX(MIN($B2,EOMONTH($C$1,0))-MAX($A2,$C$1)+1,0)

Or

=MAX(MIN($B2,EOMONTH($C$1,0))-MAX($A2,$C$1-DAY($C$1)+1)+1,0)

And copy down.

Markmzz

 

[billszysz]

Heh.... this is cool )
I mean, markmzz's formula

=MAX(MIN($B2,EOMONTH($C$1,0))-MAX($A2,$C$1)+1,0)

 

[kocab]

Thank you Peter this really did the job! Except for one thing that i didn't mention - that is that there are some rows where the B-column is empty which means that the person still works at the company. I solved it by typing a date beyond the month the C-column is intended to check to get the full month. This isn't the most elegant solution but it works. Anyway thank you very much, this really saves me a lot of time and reduces the risk of making any errors. THANKS!

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.

Count Days

*	A	B	C

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]
[TD="align: right"]From
[/TD]
[TD="align: right"]To
[/TD]
[TD="align: right"]Apr 2013
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="align: right"]01-Aug-12
[/TD]
[TD="align: right"]31-Jul-14
[/TD]
[TD="align: right"]30
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]
[TD="align: right"]05-Apr-13
[/TD]
[TD="align: right"]31-Jul-14
[/TD]
[TD="align: right"]26
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="align: right"]01-Aug-12
[/TD]
[TD="align: right"]05-Apr-13
[/TD]
[TD="align: right"]5
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="align: right"]05-Apr-13
[/TD]
[TD="align: right"]07-Apr-13
[/TD]
[TD="align: right"]3
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="align: right"]01-Aug-12
[/TD]
[TD="align: right"]07-Mar-13
[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]
[TD="align: right"]05-May-14
[/TD]
[TD="align: right"]09-May-14
[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #CACACA, align: center"]8
[/TD]
[TD="align: right"]31-Mar-13
[/TD]
[TD="align: right"]01-May-13
[/TD]
[TD="align: right"]30
[/TD]

[TD="bgcolor: #CACACA, align: center"]9
[/TD]
[TD="align: right"]31-Mar-13
[/TD]
[TD="align: right"]30-Apr-13
[/TD]
[TD="align: right"]30
[/TD]

[TD="bgcolor: #CACACA, align: center"]10
[/TD]
[TD="align: right"]01-Apr-13
[/TD]
[TD="align: right"]01-Apr-13
[/TD]
[TD="align: right"]1
[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =B2-A2+1-MAX(0,B2-MAX(A2-1,EOMONTH(C$1,0)))-MAX(0,MIN(C$1,B2+1)-A2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Except for one thing that i didn't mention - that is that there are some rows where the B-column is empty which means that the person still works at the company. I solved it by typing a date beyond the month the C-column is intended to check to get the full month. This isn't the most elegant solution but it works.

Did you try the (first) markmzz formula suggested in post #12?
If I understand what you are saying now, I think you will find his formula (shorter than mine anyway) already handles this situation (elegantly ).