Counting days in month

[jamesjohn]

How can I separate days into different months and count them ?

If I have a date of arrival and a date of departure:

17/05/2006 to 22/06/2006

how can I count the numberof days in May and the number of days in Jun
and have them separated ?

Thanks in advance

 

[Roger Govier]

Hi James
Assuming you have the Analysis Toolpak loaded (Tools>Addins>Analysis
Toolpak), then you can use the Eomonth() function.
With Start date in A1, End Date in B1
days in May =EOMONTH(A1,0)-A1+1
days in June =B1-EOMONTH(A1,0)

Format the cells containing the formula as General.
Both formulae assume that you are counting the whole of the start date
and the whole of the end date
--
Regards

Roger Govier


"jamesjohn" <[email protected]>
wrote in message
news:[email protected]...

 

[Biff]

Hi!

Try this

A2 = 17/05/2006
B2 = 22/06/2006

D1 = header = Month
E1 = header = Days

Enter this formula in D2:

=IF(DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1)<B$2,TEXT(DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1),"mmmm"),"")

Enter this formula in E2:

=IF(MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1))<B$2,MIN(DATE(YEAR(A$2),MONTH(A$2)+ROW()-1,0),B$2)-MAX(A$2,DATE(YEAR(A$2),MONTH(A$2)+ROW()-2,1))+1,"")

Select both D2 and E2 and copy down until you get blanks.

Biff

 

[jamesjohn]

Thanks very much !

It all seems so easy afterwards.

Now I have another one:

If I have different tarifs for different dates, how can I count ho
many days are in each tarif for different start and end dates ?

Arrive 08/07 and Leave 13/08...how many days at tarif 5 and how man
days at tarif 6?


Start End Tarif
01/03/2006 13/04/2006 1
13/04/2006 17/04/2006 2
18/04/2006 16/06/2006 3
17/06/2006 07/07/2006 4
08/07/2006 04/08/2006 5
05/08/2006 25/08/2006 6
26/08/2006 08/09/2006 7
09/09/2006 28/10/2006 8

Driving me MAD

 

[Roger Govier]

Hi James
Assuming you have the Analysis Toolpak loaded (Tools>Addins>Analysis
Toolpak), then you can use the Eomonth() function.
With Start date in A1, End Date in B1
days in tarrif5 =EOMONTH(A1,0)-A1+1
days in tarrif6 =B1-EOMONTH(A1,0)

Format the cells containing the formula as General.
Both formulae assume that you are counting the whole of the start date
and the whole of the end date


--
Regards

Roger Govier


"jamesjohn" <[email protected]>
wrote in message
news:[email protected]...

 

[jamesjohn]

Roger,

I haven't made myself clear, because you have sent me the same formul
as for finding how many days in each month.

The table in my post is the start and end dates of the tarifs.

You will see that some tarifs only last a couple of weeks and are i
the same month and other tarifs take in dates from 3 different months.

I need to calculate how many days of each month are in each differen
tarif.

Hope this makes sense

 

[Roger Govier]

Hi James

I set up the end dates of your tariff periods and the period number in
cells A2:B9 and named this Periods.
Your full range of dates with start and end dates for each period, I
copied to cells D2:E9 having adjusted the value in cell D3 to be
14/04/06 not 13/04/06
In C2 I entered the following formula
=E2-D2+1 and copied down through C3:C9
I then named range B2:E9 as Tariffs

I used F2 to Enter Start Date and G2 to enter End Date
In F1 I entered the formula
=VLOOKUP(F2,Periods,2)+1 and copied across to G1

In Cell H2 I entered the following formula

=IF(AND($F1<B2,$G1>B2),VLOOKUP(B2,tariff,2,0),
IF($F1=B2,VLOOKUP($F1,tariff,2,0)-($F2-VLOOKUP($F1,tariff,3,0)),
IF($G1=B2,VLOOKUP($G1,tariff,2,0)-(VLOOKUP($G1,tariff,4,0)-$G2)+1,0)))

and copied down through cells H3:H9

Then inputting start and end dates to F2 and G2 gives the number of days
falling into each tariff period.


--
Regards

Roger Govier


"jamesjohn" <[email protected]>
wrote in message
news:[email protected]...