IF two dates exist count the average time between the dates and subtract from the Total Average
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.
Answers

Are you able to provide some screenshots for context?

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.

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!