Help with a formula to count number of days

D_Spark

Board Regular
Joined
Feb 4, 2007
Messages
232
Hi all,
I need some help. On a workbook I have the following values

Cell A4=02/04/2008
Cell B4=02/05/2008
Cell D2=01/04/2008
Cell E2=01/05/2008
Cell F2=01/06/2008
Cell D3=30/04/2008
Cell E3=31/05/2008
Cell F3=30/06/2008

What I am attempting is to create a formula in Cells D4, E4 and F4 to count the number of days in the range 02/04/2008to02/05/2008 that fall within each month

I have started with a formula in cell D4 of:
=IF($A$4<=D2,IF($B$4>=D3,DAYS360(D2,D3))+1)+IF($A$4>=D2,IF(D3>=$A$4,DAYS360($A$4,D3))+1)

but whilst this appears to work as a one-off, when I amend for the other months it breaksdown.

eg
The value returned in cell F4 is 1
using
=IF($A$4<=F2,IF($B$4>=F3,DAYS360(F2,F3))+1)+IF($A$4>=F2,IF(F3>=$A$4,DAYS360($A$4,F3))+1)

where it should be zero as A4 is less that F2 and B4 is less than F2



Can anyone assist or suggest a better way to address this using excel?
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
D4: =sumproduct(--(row(indirect(d$2&":"&d$3))>=$a4),--(row(indirect(d$2&":"&d$3))<=$b4))
 
Upvote 0

Forum statistics

Threads
1,207,089
Messages
6,076,517
Members
446,211
Latest member
b306750

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top