|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 13th, 2009, 09:44 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Period Calendar
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 
|
|
#2
May 13th, 2009, 10:42 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Period Calendar
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. -- Regards Roger Govier "MrRJ" wrote in message ... 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
|
|
#3
May 14th, 2009, 06:59 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Period Calendar
Thanks Roger. That works! Dates always seem to give me trouble when coming
up with formulas. MrRJ. "Roger Govier" wrote: 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. -- Regards Roger Govier "MrRJ" wrote in message ... 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
