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.
|