Breaking down days between dates

[Veritec]

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.

 

[Allllen]

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$1>DATE(YEAR($B$1),MONTH($B$1)+$C2,1),DATE(YEAR($B$1),MONTH($B$1)+$C2,1)-SUM($B$1:B1),IF(DATE(YEAR($C$1),MONTH($C$1)+1,1)=DATE(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.

 

[Bernard Liengme]

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

 

[Biff]

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]

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$1>DATE(YEAR($B$1),MONTH($B$1)+$C2,1),DATE(YEAR($B$1),MONTH($B$1)+$C2,1)-SUM($B$1:B1),IF(DATE(YEAR($C$1),MONTH($C$1)+1,1)=DATE(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.