Populating months from start and end date

D

David900ss

New Member
Joined
Jun 25, 2008
Messages
2
I am having trouble working out a methood of populating the months (or fractions of) from a start and end date. I can manage each of the 3 senarios (fraction of month at begining, whole month and fraction of month at the end ) using =IF ect but not one statement to cover it all (do not seem to get my head around the nesting required) of should this be done in programing code? there upto 36 period columns and upto 10,000 rows! I was planing of using a macro to then paste the statement down and across all populated "active from" and "Active to rows"​

see example below​

J11=fraction of April 2007
K11=whole of month
L11=Fraction of June 2007​

Can anyone help? (please!!!)​



<CENTER></CENTER><CENTER><CENTER><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left>Microsoft Excel - Book2</TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 12.0 : OS = </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" colSpan="8" bgColor="#d4d0c8"><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</TD><TD vAlign=center align=right><FORM name=formCb605117><INPUT onclick='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);' type=button value="Copy Formula" name=btCb942116></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" colSpan="8" bgColor="white"><TABLE border=0><TBODY><TR><FORM name=formFb202339><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION value="=IF(AND($H11<K8,$I11>K9),1,($I11-$H11)/(K9-K8))" selected>K11</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value="=IF(AND($H11<K8,$I11>K9),1,($I11-$H11)/(K9-K8))" name=txbFb150492></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" width="2%" align="middle">



</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>H</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>I</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>J</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>K</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>L</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>M</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align="middle"><CENTER>N</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" width="2%" align="middle"><CENTER>8</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Period from</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">01/04/2007</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">01/05/2007</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">01/06/2007</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">01/07/2007</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">01/08/2007</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" width="2%" align="middle"><CENTER>9</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Period to</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">30/04/2007</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">31/05/2007</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">30/06/2007</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">31/07/2007</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">31/08/2007</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" width="2%" align="middle"><CENTER>10</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Active from</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Active To</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" width="2%" align="middle"><CENTER>11</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">20/04/07</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">15/06/07</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">0.330</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">0.5</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan="8"><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left>Sheet1</TD><TD></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>
</CENTER>
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
does this work for you ?

EDIT: Mr E is refusing to let me post full formula...

Code: 
=IF(AND($H11<=J$8,$I11>=J$9),1,IF($H11>J$8,(DAY(J$9)-DAY($H11))/DAY(J$9),DAY($I11)/DAY(J$9)))
 
Last edited:
Upvote 0
Can't post remainder of formula...

test if active from > end of month or active to < beginning of month return 0.

EDIT: have PM'd formula to you as the HTML Maker on this post means I can't post here.
 
Last edited:
Upvote 0
Wow it works! i have been fiddling about with this for quite a while now and did not look like i was going to get it!

Many thanks Lasw10!

Here is the full formula (adjusted slightly to post results on the same line) and broken up so it will post (reassemble with 3 parts with no spaces!)

=ROUND(IF(OR($H11>J$9,$I11<J$8),0,IF(AND($H11<=J$8,$I11>=J$9)


,1,IF($H11>J$8,((DAY(J$9)-DAY($H11))/DAY(J$9))-IF($I11<J$9,< p>

(DAY(J$9)-DAY($I11))/DAY(J$9),0),DAY($I11)/DAY(J$9)))),3)

<J$9,(DAY(J$9)-DAY($I11)) p DAY(J$9),0),DAY($I11) DAY(J$9)))),3)<><J$9,(DAY(J$9)-DAY($I11)) p DAY(J$9),0),DAY($I11) DAY(J$9)))),3)< statement!)<J$9,(DAY(J$9)-DAY($I11)) whole post to space (added DAY(J$9)))),3) DAY(J$9)))),3)<J$9,(DAY(J$9)-DAY($I11))>
David
<J$9,(DAY(J$9)-DAY($I11)) p DAY(J$9),0),DAY($I11) DAY(J$9)))),3)<>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
429
Micron
M
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
834
gvsumich
G
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
318
VBAEXCELNew
V
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
349
esben
E
E
  • Question
Create mails with table - standard header specific row
Replies
1
Views
947
esben
E

Forum statistics

Threads
1,214,987
Messages
6,122,618
Members
449,092
Latest member
amyap

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top