Splitting values across dates

[sraybard]

Hello,
I was hoping to get some guidance on dealing with some data in Excel/Access.
I have a collection of data on antibiotics for each floor in a hospital by dates and a variable called days of antibiotics - as below.
For situations like the second record - I need to be able to split the days of antibiotics between the months and make it attributable to that month, for example that second record should be split into CTX 10/29/2009 to 10/31/2009 Days of Antibiotics = 3 and CTX 11/1/2009 to 11/4/2009 Days of Antibiotics = 4.
Is there a way to do this in Excel (or Access)?
Many thanks for any insights!

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION></CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Unit</TH><TH bgColor=#c0c0c0 borderColor=#000000>Antibiotic</TH><TH bgColor=#c0c0c0 borderColor=#000000>Start</TH><TH bgColor=#c0c0c0 borderColor=#000000>Stop</TH><TH bgColor=#c0c0c0 borderColor=#000000>Days of Antibiotics</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>2
2
</TD><TD borderColor=#d0d7e5>CTX
CTX
</TD><TD borderColor=#d0d7e5 align=right>10/28/2009
10/29/2009
</TD><TD borderColor=#d0d7e5 align=right>10/29/2009
11/4/2009
</TD><TD borderColor=#d0d7e5 align=right>1.00
7.00
</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

 

[shift-del]

Hello

You data is inconsistent. If I add the number of days to the start date, I get 11/5/2009 instead of 11/4/2009 for the second record.

 

[sraybard]

The first day of therapy is considered day 1, that's why it adds up to 7.
thanks!
s.

 

[shift-del]

The first day of therapy is considered day 1, that's why it adds up to 7.

Then the stop date for the first record should be 10/28/2009 instead of 10/29/2009.

 

[sraybard]

Sorry - you're correct - the first record should read days of therapy = 2
thanks again,
s.

 

[shift-del]

Hello

<table valign="middle" colspan="9" style="font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:bold; font-style:normal; " border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="92,25pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody><tr style="background-color:#FAFAFA"><td colspan="9" align="center">Worksheet 'Tabelle1'</td></tr><tr style="background-color:#cacaca"><td>
</td><td align="center">A</td><td align="center">B</td><td align="center">C</td><td align="center">D</td><td align="center">E</td><td align="center">F</td><td align="center">G</td><td align="center">H</td></tr><tr><td style="background-color:#cacaca" align="center">1</td><td align="left">Unit</td><td align="left">Antibiotic</td><td align="left">Start</td><td align="left">Stop</td><td align="left">Days of Antibiotics</td><td align="right">
</td><td align="left">1. month</td><td align="left">2. month</td></tr><tr><td style="background-color:#cacaca" align="center">2</td><td style="font-weight:normal; " align="right">2</td><td style="font-weight:normal; " align="left">CTX</td><td style="font-weight:normal; " align="right">28.10.2009</td><td style="font-weight:normal; " align="right">29.10.2009</td><td style="font-weight:normal; " align="right">2</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">0</td><td style="font-weight:normal; " align="right">2</td></tr><tr><td style="background-color:#cacaca" align="center">3</td><td style="font-weight:normal; " align="right">2</td><td style="font-weight:normal; " align="left">CTX</td><td style="font-weight:normal; " align="right">29.10.2009</td><td style="font-weight:normal; " align="right">04.11.2009</td><td style="font-weight:normal; " align="right">7</td><td style="font-weight:normal; " align="right">
</td><td style="font-weight:normal; " align="right">3</td><td style="font-weight:normal; " align="right">4</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color:#eeaaaa"><td>Cell</td><td>Formula</td></tr><tr><td>D2</td><td>=C2+E2-1</td></tr><tr><td>G2</td><td>=IF((MONTH(C2)=MONTH(D2)),D2,EOMONTH(C2,0))-C2+1</td></tr><tr><td>H2</td><td>=E2-G2</td></tr></tbody></table><table style="font-family:Arial; font-size:7pt"><tbody><tr><td style="color:#333333">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>

 

[sraybard]

thank you!
is there a way to automate it so that it copies all the data in that row if it needs to split the values across dates? ie. for the CTX across date example:
Unit Antibiotic Start Stop Days of Therapy
2 CTX 29.10.2009 31.10.2009 3
2 CTX 01.11.2009 04.11.2009 4