F
Firoz Khan
how do I calculate days of every month from a given period (start date and
end date)
end date)
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jacob Skaria
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
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
F
Firoz Khan
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
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
F
Fred Smith
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),MONTH(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
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),MONTH(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
R
Ron Rosenfeld
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
R
Ron Rosenfeld
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),MONTH(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
T
T. Valko
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)
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)
R
Ron Rosenfeld
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
J
Jacob Skaria
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
--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
R
Ron Rosenfeld
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
F
Firoz Khan
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
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
J
Jacob Skaria
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
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