Calculate days in a month

[N Harkawat]

I have data in columns as follows:

Start date | End date | April | May | June


What I am trying to get is how many days are in each of the column months.
For instance :
Start date - Jan-4-08 End date: April-2-2008
then a formula that will post 2 in column April , 0 in May and 0 in June

15-April-08 to 9th June 2008 will put 15 , 31 , 9 in respective columns
02-June-08 to 09-July-08 will put 0,0,28

thx

 

[Rick Rothstein]

The simplest way to set this up (at least to me) would be to put the date
for the last day of the each of your header months in Row 1 (where you show
April, May and June) and Custom Format them with mmmm so only the month will
show. To put the last day of each month in those header cells, put this
formula in where April is to be displayed...

=DATE(2008,COLUMNS($A)+1,0)

and copy it across. Note that the "D" in "COLUMNS($A)" is the 4th letter
of the alphabet corresponding to April which is the 4th month of the year.
When you copy across, the "D" will increment to "E" and "F" respectively.
Remember, Custom Format these cells with mmmm to display only the month.
Okay, now that we have the last day of each month available to us, the
formula that goes in Row 2 (first data row) becomes somewhat simpler to
write...

=IF(MONTH($A2)=MONTH(C$1),C$1-$A2,IF(AND(MONTH($A2)<MONTH(C$1),MONTH($B2)>MONTH(C$1)),DAY(C$1),$B2-C$1+DAY(C$1)))

Copy this across, then copy them down. Note that the above formula is
dependent on the starting cell for the month headers being C1 (change that
reference as needed).

 

[Héctor Miguel]

hi, !

I have data in columns as follows:
Start date | End date | April | May | June
What I am trying to get is how many days are in each of the column months.
For instance:
Start date - Jan-4-08 End date: April-2-2008
then a formula that will post 2 in column April , 0 in May and 0 in June
15-April-08 to 9th June 2008 will put 15 , 31 , 9 in respective columns
02-June-08 to 09-July-08 will put 0,0,28

assumptions:

- row1 = titles
April, May & June are "real" date-entries (the last day each month) w/ custom format: "mmm"

- first account in cell [C2] w/ the formula:
=sumproduct(--isnumber(match(row(indirect($a2&":"&$b2)),row(indirect(date(year(c$1),month(c$1),0)+1&":"&c$1)),0)))

- copy-cross then copy-down

note: first and last matching days are included
- revise your expectations for 15,31,9 (16,31,9) and 0,0,28 (0,0,29)

hth,
hector.