Duration in a given time range

[fangki79]

Dear Chandoo,


I have a problem in calculating the number of days.

How do I calculate the number of days as the recognized duration only between 1 May 10 until 30 Jun 11?


Start Finish Dur Recognized Dur. (1-May-10 until 30-Jun-11)


1-Apr-10 12-Jan-11 286 257

25-Sep-10 28-Aug-11 321 279


Thank you very much for helping me..

 

[Mike86]

This will work:


=IF($E$3>B6,B6,$E$3)-IF($D$3>A6,$D$3,A6)


I put the "01May10" Date in D3 and "30Jun11" in E3. The "Recognized Dur" values from this equation are 256 and 278. Not sure why the one day difference.


This equation doesn't consider the situation where the start and finish dates are both outside of range, so you may need to add another if condition.

 

[mukeshsaraf]

Dear Chandoo,


can you help me to split date from the below strings.


13 mar 10 fhjgiyihb

mar 10 byjbknjhj


Thanks in advance.

 

[Mike86]

mukeshsaraf,


You should start a fresh thread with your question. However, as long as the year is always "10" this will work:


=LEFT(a1,SEARCH("10 ",a1)+1)


This will extract the text characters for the date.


You can covert the text string to a date value using the DateValue function. The "mar 10" example will convert to 10-Mar-2011, so you would need to add something like "01" in from of the "mar" if the day is missing.