I've used some of your tutorials to put together a nice Gantt chart (thank you!), but I'm trying to do something that I haven't quite figured out yet.
I have it set up with multiple sets of dates, so:
PHASE 1: Planned Start, Planned End, Actual Start, Actual End
PHASE 2: Planned Start, Planned End, Actual Start, Actual End
And I have different conditional formats set up so that I have the visualization of the Plan (Phase 1 green, Phase 2 orange) and then I have another set of conditional formatting for visualization of the Actuals. The problem that I'm having is that some of the Actuals are ongoing and don't yet have end dates, so what I'm ending up with is just the Actual Start being formatted in those instances (similar to attached, first row doesn't have an end date, second row does have an end date). What I would like to do is to write a formula such that if Actual End has a value, use that value, but if the Actual End is blank, use Today(). I want the Actual Line to go from the Actual Start until today's date if there is no end date listed. I've tried several things and haven't been able to get it to work. Thoughts on how to do this?
As an example, one of the formulas that I'm using for the Planned dates is:
=AND($C15<>"",MEDIAN($C15,$D15,M$13)=M$13)
Where column C is the Planned Start, D is the Planned End, and then row 13 are the dates included in the plan.
I have it set up with multiple sets of dates, so:
PHASE 1: Planned Start, Planned End, Actual Start, Actual End
PHASE 2: Planned Start, Planned End, Actual Start, Actual End
And I have different conditional formats set up so that I have the visualization of the Plan (Phase 1 green, Phase 2 orange) and then I have another set of conditional formatting for visualization of the Actuals. The problem that I'm having is that some of the Actuals are ongoing and don't yet have end dates, so what I'm ending up with is just the Actual Start being formatted in those instances (similar to attached, first row doesn't have an end date, second row does have an end date). What I would like to do is to write a formula such that if Actual End has a value, use that value, but if the Actual End is blank, use Today(). I want the Actual Line to go from the Actual Start until today's date if there is no end date listed. I've tried several things and haven't been able to get it to work. Thoughts on how to do this?
As an example, one of the formulas that I'm using for the Planned dates is:
=AND($C15<>"",MEDIAN($C15,$D15,M$13)=M$13)
Where column C is the Planned Start, D is the Planned End, and then row 13 are the dates included in the plan.