Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
how do I calculate days of every month from a given period (start date and
end date) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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) |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Days Calculation
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
days calculation | Excel Discussion (Misc queries) | |||
days calculation | New Users to Excel | |||
How to calculation no. of days (only working days) between two dat | Excel Discussion (Misc queries) | |||
Please Help With Days Elapsed And Days Remaining Calculation | Excel Worksheet Functions | |||
cALCULATION OF DAYS | Excel Worksheet Functions |