Home |
Search |
Today's Posts |
#1
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
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
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find free sharware to include calendar pop or use calendar in cell | Excel Discussion (Misc queries) | |||
how do i export excel calendar info to outlook calendar? | Excel Discussion (Misc queries) | |||
excel calendar - list of names displayed on calendar | Excel Worksheet Functions | |||
Period to Period percentage change? | Excel Discussion (Misc queries) | |||
import calendar items from excel into outlook calendar | Excel Discussion (Misc queries) |