Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking down days between dates
Given two dates (e.g. November 7, 2004 and January 16, 2005) I am trying to
identify how many days are in November, December, and January. The dates represent meter readings and I am trying to breakdown how much of the overall consumption can be assigned to each individual month. In the example above 7 days in November, 31 in December, and 16 in January. Thanks for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking down days between dates
Hi there,
I have built something for you which works. I am sure there is a more sophisticated way but keep it just like this and it works fine. By the way there were 24 days for you to count in November not 7. My sheet looks like this: ------------------------------------------------- Col A Col B Col C Row 1 Month 07-Nov-04 15-Jan-06 Row 2 Nov-04 24 1 Row 3 Dec-04 31 2 Row 4 Jan-05 31 3 Row 5 Feb-05 28 4 Row 6 Mar-05 31 5 Row 7 Apr-05 30 6 Row 8 May-05 31 7 Row 9 Jun-05 30 8 Row 10 Jul-05 31 9 Row 11 Aug-05 31 10 Row 12 Sep-05 30 11 Row 13 Oct-05 31 12 Row 14 Nov-05 30 13 Row 15 Dec-05 31 14 Row 16 Jan-06 17 15 Row 17 Feb-06 0 16 Row 18 Mar-06 0 17 Row 19 Apr-06 0 18 Row 20 May-06 0 19 ------------------------------------------------- Column A is formatted to Date format mmm-yy Columns B + C are formatted to number, no decimal places Cells B1 and C1 are formatted to Date format dd-mmm-yy The formula in cell A2 is =DATE(YEAR($B$1),MONTH($B$1)-1+C2,1) The formula in cell B2 is =IF($C$1DATE(YEAR($B$1),MONTH($B$1)+$C2,1),DATE(Y EAR($B$1),MONTH($B$1)+$C2,1)-SUM($B$1:B1),IF(DATE(YEAR($C$1),MONTH($C$1)+1,1)=D ATE(YEAR($B$1),MONTH($B$1)+$C2,1),DATE(YEAR($B$1), MONTH($B$1)+$C2,1)-$C$1,0)) The formula in cell C2 is =row()-1 Then extend the formulae down as far as you need to go. Hope that sorts you out. -- Allllen "Veritec" wrote: Given two dates (e.g. November 7, 2004 and January 16, 2005) I am trying to identify how many days are in November, December, and January. The dates represent meter readings and I am trying to breakdown how much of the overall consumption can be assigned to each individual month. In the example above 7 days in November, 31 in December, and 16 in January. Thanks for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking down days between dates
With Nov 7 (date A) in A1 and Jan 16 (date B) in A2; assuming there are
always three months. Last day of month of date A is given by =DATE(YEAR(A1),MONTH(A1)+1,0) in B1 Last day of next month is given by =DATE(YEAR(A1),MONTH(A1)+2,0) in B2 Days in first month: =DATEDIF(A1,B1,"d") [23] Day in second month =DATEDIF(B1,B2,"d") [31] Day in third month =DATEDIF(B2,A2,"d") [16] --- total [70] best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Veritec" wrote in message ... Given two dates (e.g. November 7, 2004 and January 16, 2005) I am trying to identify how many days are in November, December, and January. The dates represent meter readings and I am trying to breakdown how much of the overall consumption can be assigned to each individual month. In the example above 7 days in November, 31 in December, and 16 in January. Thanks for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking down days between dates
Here's another one:
A2 = start date = 11/7/2004 B2 = end date = 1/16/2005 D1 = header = Month/Year E1 = header = Days Formula in D2: =IF(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1)<B$2,TEXT(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1),"mmmm yyyy"),"") Formula in E2: =IF(MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1))<B$2,MIN(DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:2)-1,0),B$2)-MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROWS($1:1)-1,1))+1,"") Select both D2 and E2 and copy down until you get blanks. The output will look like this: ......................D......................E 1...........Month/Year............Days 2...........November 2004.......24 3...........December 2004.......31 4...........January 2005...........16 5.............................................. Biff "Veritec" wrote in message ... Given two dates (e.g. November 7, 2004 and January 16, 2005) I am trying to identify how many days are in November, December, and January. The dates represent meter readings and I am trying to breakdown how much of the overall consumption can be assigned to each individual month. In the example above 7 days in November, 31 in December, and 16 in January. Thanks for your help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Breaking down days between dates
"Allllen" wrote: Hi there, I have built something for you which works. I am sure there is a more sophisticated way but keep it just like this and it works fine. By the way there were 24 days for you to count in November not 7. My sheet looks like this: ------------------------------------------------- Col A Col B Col C Row 1 Month 07-Nov-04 15-Jan-06 Row 2 Nov-04 24 1 Row 3 Dec-04 31 2 Row 4 Jan-05 31 3 Row 5 Feb-05 28 4 Row 6 Mar-05 31 5 Row 7 Apr-05 30 6 Row 8 May-05 31 7 Row 9 Jun-05 30 8 Row 10 Jul-05 31 9 Row 11 Aug-05 31 10 Row 12 Sep-05 30 11 Row 13 Oct-05 31 12 Row 14 Nov-05 30 13 Row 15 Dec-05 31 14 Row 16 Jan-06 17 15 Row 17 Feb-06 0 16 Row 18 Mar-06 0 17 Row 19 Apr-06 0 18 Row 20 May-06 0 19 ------------------------------------------------- Column A is formatted to Date format mmm-yy Columns B + C are formatted to number, no decimal places Cells B1 and C1 are formatted to Date format dd-mmm-yy The formula in cell A2 is =DATE(YEAR($B$1),MONTH($B$1)-1+C2,1) The formula in cell B2 is =IF($C$1DATE(YEAR($B$1),MONTH($B$1)+$C2,1),DATE(Y EAR($B$1),MONTH($B$1)+$C2,1)-SUM($B$1:B1),IF(DATE(YEAR($C$1),MONTH($C$1)+1,1)=D ATE(YEAR($B$1),MONTH($B$1)+$C2,1),DATE(YEAR($B$1), MONTH($B$1)+$C2,1)-$C$1,0)) The formula in cell C2 is =row()-1 Then extend the formulae down as far as you need to go. Hope that sorts you out. -- Allllen "Veritec" wrote: Given two dates (e.g. November 7, 2004 and January 16, 2005) I am trying to identify how many days are in November, December, and January. The dates represent meter readings and I am trying to breakdown how much of the overall consumption can be assigned to each individual month. In the example above 7 days in November, 31 in December, and 16 in January. Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
duration days | Excel Discussion (Misc queries) | |||
Repete Dates at a value of 28 days, 56 & 84 | Excel Worksheet Functions | |||
need help with formula | Excel Discussion (Misc queries) | |||
convert dates to number of days | Excel Worksheet Functions | |||
Brainteaser about Days Between Dates | Excel Discussion (Misc queries) |