Period Calendar

  • Thread starter Thread starter MrRJ
  • Start date Start date
M

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
 
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.
 
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.
 
Thanks Roger. That works! Dates always seem to give me trouble when coming
up with formulas.

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

MrRJ.
 
Back
Top