Date Split

[zwaz]

Hi

I am new to this forum but have been reading it for a while. I have a question and was wondering if someone could help me.

I have a spreadsheet with startdates in one col and end dates in another and an amount relating to each of these start and end dates. The amount needs to be allocated to a period (month) and the problem that i have is that in some start and end dates, there is more than one month.
I could ofcourse do this manualy but i have about 9000 lines that need to be allocated so any help will be greatly appreciated. Many Thanks in advance

Example

<table x:str="" style="border-collapse: collapse; width: 105pt;" border="0" cellpadding="0" cellspacing="0" width="140"><col style="width: 52pt;" width="69"><col> <tbody><tr><td class="xl22" style="height: 12.75pt; width: 52pt;" x:num="39608" align="right" height="17" width="69">09/06/2008

</td> <td class="xl22" style="width: 53pt;" x:num="39629" align="right" width="71">30/06/2008

</td> </tr></tbody><col style="width: 53pt;" width="71"><table x:str="" style="border-collapse: collapse; width: 233pt;" border="0" cellpadding="0" cellspacing="0" width="311"> <col style="width: 101pt;" width="135"> <col style="width: 53pt;" width="71"> <col style="width: 42pt;" width="56"> <col style="width: 37pt;" width="49"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; width: 101pt;" height="17" width="135">Start Date</td> <td class="xl27" style="border-left: medium none; width: 53pt;" width="71">End Date</td> <td class="xl28" style="border-left: medium none; width: 42pt;" width="56">Amount</td> <td class="xl29" style="border-left: medium none; width: 37pt;" width="49">Period</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 101pt;" x:num="39600" height="17" width="135">01/06/2008</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 53pt;" x:num="39614" width="71">15/06/2008</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">100</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 101pt;" x:num="39580" height="17" width="135">12/05/2008</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 53pt;" x:num="39648" width="71">19/07/2008</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">375</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 101pt;" x:num="39580" height="17" width="135">12/05/2008</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 53pt;" x:num="39648" width="71">19/07/2008</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="" align="right">33</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 37pt;" width="49"> </td> </tr> </tbody></table></table><table x:str="" style="border-collapse: collapse; width: 105pt;" border="0" cellpadding="0" cellspacing="0" width="140"><col style="width: 52pt;" width="69"><col> <tbody><tr><td class="xl22" style="height: 12.75pt; width: 52pt;" x:num="39608" align="right" height="17" width="69">09/06/2008
</td> <td class="xl22" style="width: 53pt;" x:num="39629" align="right" width="71">30/06/2008
</td> </tr></tbody><col style="width: 53pt;" width="71"><tr style="height: 12.75pt;" height="17"></tr></table>

 

[xld]

Do you mean

=(MIN(B2,--"2008-06-30")-MAX(A2,--"2008-06-09"))*C2/(--"2008-06-30"-"2008-06-09"+1)

 

[vba_newby]

if this is a fixed width column and it looks like it is

Data > Text to Columns... Choose fixed width

put a line where you what the date to split - just click after the year

make sure you change the destination in the next step to save your raw data. in my example i copied your date to cell A1 and made my destination B1 and C1 by selecting them and this worked would great

done!

 

[zwaz]

Hi

Many Thanks for your replies - much appreciated

the answer that i was looking for is that for the first line, the amount allocated to June is £100, for the second line the amount for May is 110 (375/68 days x 20 days for May), £165 for June (375/68 x 30 days for June) and £99 for July ( 18 days)

If there is an easier way of getting this then please let me know


Thankyou once again