Dates between 2 dates, return value

[Michael A]

Hello,

I have a series of tasks occurring over several different days. I'm wanting to calculate a value for each of the days, if that day is between certain start and finish dates. However, the results are only displaying values on the task start and finish dates instead of also showing a value on each of the days between the start and finish dates. If a day is not between the start and finish dates it is to display 00.0

=IF(AND(V$15>=$I20, V$15<=$J20),V20*$T20,"00.0")

In the above:
V$15 is the date of the month
$I20 is the start date of the task
$J20 is the finish date of the task
V20 is a number I want to multiply by a preassigned value ($T20).

Recommendations are greatly appreciated.

Michael

 

[Aladin Akyurek]

Are you copying the formula down or to the right?

 

[Michael A]

I'm copying both down and to the right.
Tasks are listed down.
Dates are listed to the right.

 

[Aladin Akyurek]

I'm copying both down and to the right.
Tasks are listed down.
Dates are listed to the right.

In which cell did you enter this formula:

=IF(AND(V$15>=$I20, V$15<=$J20),V20*$T20,"00.0")

and what needs it to become if it is copied down and to the right respectively?

 

[Michael A]

This formula is in cell W20. This formula is repeated every 2nd cell to the right and essentially every cell down for 90 tasks.

Start date is June 8 2011.
Finish date is August 1 2011.

Thanks,

Michael

 

[yogeshmaney]

chief,wud u be able to provide a snapshot, and required/ expected results.

 

[Michael A]

I've got a print screen shot but unable to attach it or the file.
Can you email me at xxx and I'll send it to you?
Thanks,
Michael

 

[yogeshmaney]

kindly put it on any file sharing site

 

[Michael A]

Here is the link for it at 4shared.com
http://www.4shared.com/document/CYQqW__X/Junin_Construction_Schedule_Ju.html

What I need is to enter productive time in the yellow highlighted boxes of row 14 (any value between 0 and 1). If the task is to occur between the dates on row 15, I'm hoping it will calculate the % complete in the respective columns W, Y, AA, AC...

Thanks,

Michael

 

[yogeshmaney]

Chief, Its just a formula feed error.

In col v,x,z....etc. both condition compare are for start date not finish date.

Just change I to J ....i.e. in cell V23

Use
IF(AND(V$15>=$I23,V$15<=$J23),V$14,"00.0") instead of
IF(AND(V$15>=$I23,V$15<=$I23),V$14,"00.0")


But I have a small question....

What if U overshoot the baseline finish?
Why the Task deliverables are not tracked individually.
Will the Gantt descript Planned v/s Actuals