how many days between two date ranges

[gaihitretzii]

Hi,

i have the following questions

1. in A1 and B1 i have a date range
A1= 01/04/2004
B1= 27/042004
how do i calculate accurately the full days between those 2 dates. if I do B1-A1 i get 26 day, instead of 27 which should be the accurate number. The only way i see this is doing (B2-A1)+1 to adjust?

2. how do i calculate how many days in a range between 2 dates are also covered in another range with a different start and end date. Not sure I am explaining that clearly so here is an example:

date: dd/mm/yyyy
Date range 1:
A1= 01/04/2004
B1= 27/04/2004

Date Range 2:
A2= 28/04/2004
B2= 16/05/2004

Date range 3
D1= 05/04/2004
E1= 25/01/2005

What is the formula that will tell me how many days from Data range 3 are covered by Date ranges 1 and 2?

 

[al_b_cnu]

Hi,

Q1: 26 IS the correct answer isnt it?

Q2: How about:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">01/04/2004</td><td style="text-align: right;;">27/04/2004</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">28/04/2004</td><td style="text-align: right;;">16/05/2004</td><td style="text-align: right;;">18</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">05/04/2004</td><td style="text-align: right;;">25/01/2005</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=IF(<font color="Blue">$B$3<A1,0,IF(<font color="Red">$A$3>B1,0,MIN(<font color="Green">B1,$B$3</font>)-MAX(<font color="Green">A1,$A$3</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">$B$3<A2,0,IF(<font color="Red">$A$3>B2,0,MIN(<font color="Green">B2,$B$3</font>)-MAX(<font color="Green">A2,$A$3</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Welcome to the Board BTW

 

[gaihitretzii]

Hi,

thanks for the reply.

on Q1. no it should be 27 days. 26 doesn't account for the full days in the range. I am using this to calculate something involving the interest rate that the national bank gives every month, or whenever there is change in the interest rate. so if the interest rate is 12% for the period 01 April 2004 till 27 April 2004 - that is 27 days where you have to use 12% as interest rate. I just don't know what formula to use, so that I don't have to resort to using the '+1' .

q2. You have cracked it. but again its calculating it 1 day short. because it doesn't adjust for full days but only the number of days inbetween the 2 dates

 

[al_b_cnu]

Hi,

I'm afraid you'll have to use the '+1' method.

 

[gaihitretzii]

the problem with using +1 is that it skews the results so that in order to continue with the calculations and summing correctly the interest rate its going to show me the wrong amount as i will have cells with a product when they should have been blank and not affecting the result.

I will try and upload the worksheet in a few minutes to illustrate what i am trying to achieve as an end result

 

[gaihitretzii]

http://www.mediafire.com/?a375fktnpx8nc4w

this is where you can find the spreadsheet. as you can see I have included the total with the +1 adjustment - which gets me the wrong result and then a column with what i should be getting as the right number

p.s. the only way to fix it which i found is to add an additional line where the number of days is displayed and than use an if>1 to show result otherwise to show 0, but this is forcing me into going with an extra row per record which defeats the purpose

 

[gaihitretzii]

found a fix, though not a pretty one. basically i take the end date of a previous period and use it as a start date for following period. this way i don't have to do the +1.. so far works great. will see how it will do with 3000 records