IF two dates exist count the average time between the dates and subtract from the Total Average

Joe Bartlow

Hello,

I am working on a complex problem and I could use some guidance. We have a dozen or so milestones that we track. So far I have calculated the average time between the milestones and the overall average time from start to finish. Now I want to make a calculation that tells me from my current date what is the estimated amount of time until project completion. Below is an example of what I am trying to accomplish.

Total average time is 12 months start to finish.

Average time from start to milestone one is 1 month.

Average time from milestone one to milestone two is 1 month

Average time from milestone two to milestone three is 1 month.

etc. adding up to 12 months. (remember these numbers can fluctuate.

I need an equation/formula that looks to see if the dates exist from milestone to milestone. If the dates exist and they are today or in the past, I need to subtract the average time between the milestones from the total average. This should give me the average time to completion on a project without estimated completion dates.

Any help would be greatly appreciated.

Paul Newcome

Are you able to provide some screenshots for context?

Joe Bartlow

First column counts days between milestones. Second column is the average number of days between the milestones. Total average days when you add up all of my averages is 562 Days.

In this case, the average number of days between operational kick off is 111 days. What I need is a formula that looks at both dates, operational kickoff and Construction start, sees both dates exist. both dates need to be either today or in the past. If these conditions are met, I need to subtract that average, in this case 111 days, from the total average days of 562.

My goal is to create an active model that as milestones are reach the average amount of time decreases telling you the approximate number of days left to completion. This is being done because I don't have an accurate end date but I want an estimation.

MichaelTCA

Hello @Joe Bartlow

The NETDAYS function will help. When using the function, the "net days" of today would be 1. So if we don't want to include today, we will need to subtract 1. ISDATE will check if the value for the operation kickoff or construction start dates have a value (exists) and are actually a date to be used in the NETDAYS function.

=IF(AND(ISDATE(Operational date)=TRUE,ISDATE(Construction date)=TRUE),IF(AND(NETDAYS(TODAY(),Operational Date)<=1,NETDAYS(TODAY(),Construction Date)<=1),Total Average - Average days,""),"")

Both dates need to exist, both dates need to be today or in the past, and the average will subtract from the total. Otherwise, blank.