how do I calculate days of every month from a given period (start date and
end date)

Hi Firoz

As in the below example; B1-A1 will do (and format the formula cell to
General)..

Col A Col B ColC
8/1/2009 8/9/2009 =B1-A1
8/1/2009 8/9/2009 =DATEDIF(A2,B2,"d")

If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

how do I calculate days of every month from a given period (start date and
end date)

Hi Jacob,
thanks for your reply
it seems my qstn was not clear enough
is it possible to get the result as below ?

Date In Date Out Jan Feb Mar
9-Jan-09 16-Mar-09 22 29 15
where Date In and Date Out is input data and Jan, Feb and Mar are results
is there any formula to get the days of every month for a given period of
time ?
regards
Firoz

"Jacob Skaria" wrote:

Hi Firoz

As in the below example; B1-A1 will do (and format the formula cell to
General)..

Col A Col B ColC
8/1/2009 8/9/2009 =B1-A1
8/1/2009 8/9/2009 =DATEDIF(A2,B2,"d")

If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

how do I calculate days of every month from a given period (start date and
end date)

Let's say you have dates in C1 to N1, formatted as mmm so it displays Jan
through Dec.
In C2, enter:
=IF(MONTH(C$1)<MONTH($A2),0,IF(MONTH(C$1)=MONTH($A 2),EOMONTH($A2,0)-$A2,IF(MONTH(C$1)<MONTH($B2),DAY(DATE(YEAR($A2),MO NTH(C$1)+1,0)),IF(MONTH(C$1)=MONTH($B2),DAY($B2),0 ))))
Copy across to N2, and down if appropriate.

By the way the correct answers for your example are 28 days for February and
16 days for March.

Regards,
Fred

"Firoz Khan" wrote in message
...
Hi Jacob,
thanks for your reply
it seems my qstn was not clear enough
is it possible to get the result as below ?

Date In Date Out Jan Feb Mar
9-Jan-09 16-Mar-09 22 29 15
where Date In and Date Out is input data and Jan, Feb and Mar are results
is there any formula to get the days of every month for a given period of
time ?
regards
Firoz

"Jacob Skaria" wrote:

Hi Firoz

As in the below example; B1-A1 will do (and format the formula cell to
General)..

Col A Col B ColC
8/1/2009 8/9/2009 =B1-A1
8/1/2009 8/9/2009 =DATEDIF(A2,B2,"d")

If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

how do I calculate days of every month from a given period (start date
and
end date)

On Sun, 9 Aug 2009 06:18:01 -0700, Firoz Khan
wrote:

Hi Jacob,
thanks for your reply
it seems my qstn was not clear enough
is it possible to get the result as below ?

Date In Date Out Jan Feb Mar
9-Jan-09 16-Mar-09 22 29 15
where Date In and Date Out is input data and Jan, Feb and Mar are results
is there any formula to get the days of every month for a given period of
time ?
regards
Firoz


A1: Date In
B1: Date Out
A2: 9-Jan-09
B2: 16-Mar-09

C1-N1 1-Jan-2009 1-Feb-2009 etc
Format C1-N1 as mmm yyyy

C2:
=IF($B2$A2,--TEXT(MIN($B2,EOMONTH(C$1,0))-MAX($A2,C$1-DAY(C$1)),"0;\0;\0"),"")

Fill right to N2
Fill down as far as necessary.

If you don't like seeing the 0's when there are zero days in the month, use
custom formatting to blank them out (e.g. format/cells/number/custom type: 0;;

If you get a #NAME! error, see HELP for the EOMONTH function to correct that.
If installing the ATP is not allowable, post back.

--ron

On Sun, 9 Aug 2009 09:55:15 -0600, "Fred Smith" wrote:

Let's say you have dates in C1 to N1, formatted as mmm so it displays Jan
through Dec.
In C2, enter:
=IF(MONTH(C$1)<MONTH($A2),0,IF(MONTH(C$1)=MONTH($ A2),EOMONTH($A2,0)-$A2,IF(MONTH(C$1)<MONTH($B2),DAY(DATE(YEAR($A2),MO NTH(C$1)+1,0)),IF(MONTH(C$1)=MONTH($B2),DAY($B2),0 ))))
Copy across to N2, and down if appropriate.

By the way the correct answers for your example are 28 days for February and
16 days for March.

Regards,
Fred

IT seems that if A2 and B2 are empty, your formula gives a result of 31 for
Jan.
--ron

Consider this....

Start date = 1/31/2009

Should the count for January be 0 or 1?

C1:N1 = 1/1/2009, 2/1/2009, 3/1/2009...12/1/2009 formatted to display as
mmm.

To count 1/31/2009 as 1:

=MAX(0,MIN($B2,C1+31-DAY(C1+31))-MAX($A2,C1)+1)

To count 1/31/2009 as 0:

=MAX(0,MIN($B2,C1+31-DAY(C1+31))-MAX($A2+1,C1)+1)

--
Biff
Microsoft Excel MVP


"Fred Smith" wrote in message
...
Let's say you have dates in C1 to N1, formatted as mmm so it displays Jan
through Dec.
In C2, enter:
=IF(MONTH(C$1)<MONTH($A2),0,IF(MONTH(C$1)=MONTH($A 2),EOMONTH($A2,0)-$A2,IF(MONTH(C$1)<MONTH($B2),DAY(DATE(YEAR($A2),MO NTH(C$1)+1,0)),IF(MONTH(C$1)=MONTH($B2),DAY($B2),0 ))))
Copy across to N2, and down if appropriate.

By the way the correct answers for your example are 28 days for February
and 16 days for March.

Regards,
Fred

"Firoz Khan" wrote in message
...
Hi Jacob,
thanks for your reply
it seems my qstn was not clear enough
is it possible to get the result as below ?

Date In Date Out Jan Feb Mar
9-Jan-09 16-Mar-09 22 29 15
where Date In and Date Out is input data and Jan, Feb and Mar are results
is there any formula to get the days of every month for a given period of
time ?
regards
Firoz

"Jacob Skaria" wrote:

Hi Firoz

As in the below example; B1-A1 will do (and format the formula cell to
General)..

Col A Col B ColC
8/1/2009 8/9/2009 =B1-A1
8/1/2009 8/9/2009 =DATEDIF(A2,B2,"d")

If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

how do I calculate days of every month from a given period (start date
and
end date)

On Sun, 9 Aug 2009 06:18:01 -0700, Firoz Khan
wrote:

Hi Jacob,
thanks for your reply
it seems my qstn was not clear enough
is it possible to get the result as below ?

Date In Date Out Jan Feb Mar
9-Jan-09 16-Mar-09 22 29 15
where Date In and Date Out is input data and Jan, Feb and Mar are results
is there any formula to get the days of every month for a given period of
time ?
regards
Firoz

Some minor changes should be made in my formula depending on how you want to
count.

For example, in your example, you are apparently not counting either the first
or the last day of the interval.

Is that what you want?

The formula I first proposed doesn't count the first day (day_in) but does
count the last day.

If you want to include BOTH the first and last day of the interval in your
count, then, with the same setup as before, try:

C2:
=IF($B2$A2,SUMPRODUCT(--(MONTH(ROW(INDIRECT($A2&":"&$B2)))=MONTH(C$1))),"" )

Fill right to N2 and down as far as required.

If you don't want to count either the Date_In or Date_Out (or both), merely add
1 to Date_In, or subtract 1 from Date_out, as appropriate.

e.g. -- to not count first but count last (similar to what you posted):
=IF($B2$A2,SUMPRODUCT(--(MONTH(ROW(INDIRECT($A2+1&":"&$B2)))=MONTH(C$1))), "")


--ron

Hi Firoz

--Suppose you have your data arranged in the below format. Please note that
the months displayed in cell C1, D1 etc; are entries in excel date formats
formatted to display as mmm-yy

Col A Col B Col C Col D Col E
Date In Date Out Jan-09 Feb-09 Mar-09
9-Jan-09 16-Mar-09 23 28 16

--Also note that with the above example there are 23 days inclusive of date-in

--The formula to be applied in cell C2 is given below. Copy the cells to the
right as required

=MAX(0,MIN($B3+1,DATE(YEAR(C$2),MONTH(C$2)+1,1))-MAX($A3,DATE(YEAR(C$2),MONTH(C$2),1)))


If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

Hi Jacob,
thanks for your reply
it seems my qstn was not clear enough
is it possible to get the result as below ?

Date In Date Out Jan Feb Mar
9-Jan-09 16-Mar-09 22 29 15
where Date In and Date Out is input data and Jan, Feb and Mar are results
is there any formula to get the days of every month for a given period of
time ?
regards
Firoz

"Jacob Skaria" wrote:

Hi Firoz

As in the below example; B1-A1 will do (and format the formula cell to
General)..

Col A Col B ColC
8/1/2009 8/9/2009 =B1-A1
8/1/2009 8/9/2009 =DATEDIF(A2,B2,"d")

If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

how do I calculate days of every month from a given period (start date and
end date)

On Sun, 09 Aug 2009 13:12:34 -0400, Ron Rosenfeld
wrote:

On Sun, 9 Aug 2009 06:18:01 -0700, Firoz Khan
wrote:

Hi Jacob,
thanks for your reply
it seems my qstn was not clear enough
is it possible to get the result as below ?

Date In Date Out Jan Feb Mar
9-Jan-09 16-Mar-09 22 29 15
where Date In and Date Out is input data and Jan, Feb and Mar are results
is there any formula to get the days of every month for a given period of
time ?
regards
Firoz

Some minor changes should be made in my formula depending on how you want to
count.

For example, in your example, you are apparently not counting either the first
or the last day of the interval.

Is that what you want?

The formula I first proposed doesn't count the first day (day_in) but does
count the last day.

If you want to include BOTH the first and last day of the interval in your
count, then, with the same setup as before, try:

C2:
=IF($B2$A2,SUMPRODUCT(--(MONTH(ROW(INDIRECT($A2&":"&$B2)))=MONTH(C$1))),"" )

Fill right to N2 and down as far as required.

If you don't want to count either the Date_In or Date_Out (or both), merely add
1 to Date_In, or subtract 1 from Date_out, as appropriate.

e.g. -- to not count first but count last (similar to what you posted):
=IF($B2$A2,SUMPRODUCT(--(MONTH(ROW(INDIRECT($A2+1&":"&$B2)))=MONTH(C$1))), "")


--ron


These formulas, by the way, give a result in days per month, without respect to
year. Again, without more detail as to your precise requirements, this is just
another option. It's easy enough to add a "year check" to the above, if you
want.
--ron

Dear All,
Thank you SO much for your support

Jacob,
is it ok if I remove +1 to remove check in date ?
=MAX(0,MIN($B3,DATE(YEAR(C$2),MONTH(C$2)+1,1))-MAX($A3,DATE(YEAR(C$2),MONTH(C$2),1)))

thanks again
with best regards
Firoz Khan

"Jacob Skaria" wrote:

Hi Firoz

--Suppose you have your data arranged in the below format. Please note that
the months displayed in cell C1, D1 etc; are entries in excel date formats
formatted to display as mmm-yy

Col A Col B Col C Col D Col E
Date In Date Out Jan-09 Feb-09 Mar-09
9-Jan-09 16-Mar-09 23 28 16

--Also note that with the above example there are 23 days inclusive of date-in

--The formula to be applied in cell C2 is given below. Copy the cells to the
right as required

=MAX(0,MIN($B3+1,DATE(YEAR(C$2),MONTH(C$2)+1,1))-MAX($A3,DATE(YEAR(C$2),MONTH(C$2),1)))


If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

Hi Jacob,
thanks for your reply
it seems my qstn was not clear enough
is it possible to get the result as below ?

Date In Date Out Jan Feb Mar
9-Jan-09 16-Mar-09 22 29 15
where Date In and Date Out is input data and Jan, Feb and Mar are results
is there any formula to get the days of every month for a given period of
time ?
regards
Firoz

"Jacob Skaria" wrote:

Hi Firoz

As in the below example; B1-A1 will do (and format the formula cell to
General)..

Col A Col B ColC
8/1/2009 8/9/2009 =B1-A1
8/1/2009 8/9/2009 =DATEDIF(A2,B2,"d")

If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

how do I calculate days of every month from a given period (start date and
end date)

Hi Firoz

Yes you can adjust the date fields (B2 and A2) to get the desired outcome.

=MAX(0,MIN($B2,DATE(YEAR(C$1),MONTH(C$1)+1,1))-MAX($A2+1,DATE(YEAR(C$1),MONTH(C$1),1)))

will give you 22,28,15

If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

Dear All,
Thank you SO much for your support

Jacob,
is it ok if I remove +1 to remove check in date ?
=MAX(0,MIN($B3,DATE(YEAR(C$2),MONTH(C$2)+1,1))-MAX($A3,DATE(YEAR(C$2),MONTH(C$2),1)))

thanks again
with best regards
Firoz Khan

"Jacob Skaria" wrote:

Hi Firoz

--Suppose you have your data arranged in the below format. Please note that
the months displayed in cell C1, D1 etc; are entries in excel date formats
formatted to display as mmm-yy

Col A Col B Col C Col D Col E
Date In Date Out Jan-09 Feb-09 Mar-09
9-Jan-09 16-Mar-09 23 28 16

--Also note that with the above example there are 23 days inclusive of date-in

--The formula to be applied in cell C2 is given below. Copy the cells to the
right as required

=MAX(0,MIN($B3+1,DATE(YEAR(C$2),MONTH(C$2)+1,1))-MAX($A3,DATE(YEAR(C$2),MONTH(C$2),1)))


If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

Hi Jacob,
thanks for your reply
it seems my qstn was not clear enough
is it possible to get the result as below ?

Date In Date Out Jan Feb Mar
9-Jan-09 16-Mar-09 22 29 15
where Date In and Date Out is input data and Jan, Feb and Mar are results
is there any formula to get the days of every month for a given period of
time ?
regards
Firoz

"Jacob Skaria" wrote:

Hi Firoz

As in the below example; B1-A1 will do (and format the formula cell to
General)..

Col A Col B ColC
8/1/2009 8/9/2009 =B1-A1
8/1/2009 8/9/2009 =DATEDIF(A2,B2,"d")

If this post helps click Yes
---------------
Jacob Skaria


"Firoz Khan" wrote:

how do I calculate days of every month from a given period (start date and
end date)