Period Calendar

[MrRJ]

Hi,
I am trying to find the best way to give me a solution. I have a period
calendar, most period calendars, each period has two separate months that
intersect. I like to know the percentage of days for Month A in a period vs
Month B in the same period.
Here is the data.
Period Start End Days
0109 12/28/08 01/24/09 28
0209 01/25/09 02/21/09 28
0309 02/22/09 03/21/09 28
0409 03/22/09 04/18/09 28

In Period 0109, for December there are 4 days or 14% and January there are
24 days or 86%.
Is there a way of computing this for me?

Thanks,
MrRJ

 

[Roger Govier]

Hi

In cell F2
=(DATE(YEAR(B2),MONTH(B2)+1,0)-B2+1)/28
this will give the result of 14.29% if you format the cell as %
In G2
=1-F2
Copy down as required.

If you want it to show 14.00% then either use
=ROUND((DATE(YEAR(B2),MONTH(B2)+1,0)-B2+1)/28,2)
or just set the cell format to be % with 0 places.

 

[Roger Govier]

Hi

In cell F2
=(DATE(YEAR(B2),MONTH(B2)+1,0)-B2+1)/28
this will give the result of 14.29% if you format the cell as %
In G2
=1-F2
Copy down as required.

If you want it to show 14.00% then either use
=ROUND((DATE(YEAR(B2),MONTH(B2)+1,0)-B2+1)/28,2)
or just set the cell format to be % with 0 places.

 

[MrRJ]

Thanks Roger. That works! Dates always seem to give me trouble when coming
up with formulas.

MrRJ.

 

[MrRJ]

Thanks Roger. That works! Dates always seem to give me trouble when coming
up with formulas.

MrRJ.