Calculate No. of Days Based on Entered Dates

[shaikhrulez]

Hello Champs!

Lately, I have been struggling to calculate No. of Days based on date criteria.

I want to calculate No. of Days against each period in column C if the entered dates fall within the range based on dates entered in highlighted cells.

Here is the screenshot of manual days calculation I did for mentioned period. Any help in this would be greatly appreciated. Thanks.

 

[ETAF]

are they the results you expect in the image

as A5 is not in the range

if the entered dates fall within the range based on dates entered in highlighted cells.

1/1/2007 is outside the range

what do you want if its outside the range zero ?

 

[Chihiro]

Try...
In C5: Copy down.
=IFERROR(DATEDIF(MAX($A$2,$A5),MIN($B$2,$B5),"d"),"")

 

[mohamed ilyas]

hi

check is this ok?

 

[shaikhrulez]

Try...
In C5: Copy down.
=IFERROR(DATEDIF(MAX($A$2,$A5),MIN($B$2,$B5),"d"),"")

I don't know why but this didn't work. Thanks anyways!

hi

check is this ok?

Your solution worked. Thanks Buddy!

 

[Chihiro]

Hmm, didn't work how? Did it give error message?
DATEDIF has been in all Excel version since Excel 95 (though only documented for Excel 2000).
See sample attached, side by side with mohamed's formula.

 

[GraH - Guido]

Or =MIN(B5,$B$2)-MAX(A5,$A$2).

Or I'm missing the point.

 

[bosco_yip]

Or this, result as same as mohamed's formula.

=MAX(0,MIN(B5,B$2)-MAX(A5,A$2))