Formula Modification

[wpeggott]

I am building a work tracker that will tell me how many days it takes to complete a particular project. I want my tracker to count the days it takes for a project to be completed based on the date it opened and the date it closed. I used this formula to automatically count the days for me: =IF(B2="","",TODAY()-B2)

When I went to input dates for older projects I already received, the formula did not work anymore. Do I need a separate counter for the older projects versus the current counter for new projects? If not, how can I capture older dates with my current formula?

 

[svendiamond]

Not sure exactly what you mean. Older projects that you already received? In what way were they not working? Do you mean because they were already completed so you just want the completed date minus the started date?

 

[wpeggott]

I want to enter the actual days I received projects (some of which were in the last two weeks) on my spreadsheet. Then I want to enter the actual days it took for me to complete the projects. I want my formula to add up the amount of days it took for me to complete the respective projects from the date I actually received the project to the date I actually completed the project. Currently, when I add projects from last week into my spreadsheet, with their respective received and completed dates, instead of the cell showing me the number of days it actually took to complete a project, it shows the number of days between date I received the project and today.

 

[Brian from Maui]

If you have start and end dates, it's just =enddate-startdate.

Today() is volatile and changes everyday. Your present formula gives the amount of days the project is running and will increase by one everyday.

 

[wpeggott]

yes, because I want to be able to count how many days a project is open if it does not have an end date attached to it yet.

 

[Brian from Maui]

yes, because I want to be able to count how many days a project is open if it does not have an end date attached to it yet.

B2 houses start date
C2 houses end date

=IF(C2="",TODAY()-B2,C2-B2)

If both B2 and C2 are blank, what should be displayed?

 

[wpeggott]

The cell should be blank. My original issue was when I used my first formula, the cell that counted the days would display the number of days starting from 1900 or something like that. It defeats the purpose of what I am trying to accomplish because ultimately what I would like is for the day counting cell to be empty when no dates are entered. Once the received date is entered into the spreadsheet, the counting cell starts to count the number of days a project is open. So after 6 days the cell will turn yellow. After 9 days it will turn orange, and after 13 days it will turn red. Once I enter the end date, the cell will remain whatever color it was when I completed the task. When I open my spreadsheet I can see two things: 1. how many days current projects have been projects open and 2. what the average amount of time it takes to complete projects (do projects tend to be complete in less than six days or less than 13 etc).

I hope this makes more sense of what I am wanting to do. Thank you in advance.

 

[Brian from Maui]

Sorry, didn't see your response. Try,

=IF(AND(B2="",C2=""),"",IF(C2="",TODAY()-B2,C2-B2))

As far as cells changing color, use Conditional Formatting

 

[wpeggott]

It did not work, I received a Circular Reference Warningmessage. When I entered the formula a 0 automatically populated and when Ientered an originally received date, the spreadsheet did not calculate thedays.

 

[Brian from Maui]

What cell is the formula in? It shouldn't be in your start/end dates.