Update status to complete

Currently the “Days in House” column uses the Today function to show how many days it’s been since the application was received. I would like it so that when the status is changed to complete, the days in house shows complete as opposed to the date. What formula do I have to use?

Find more posts tagged with

IT & Operations

Comments

Rob Hagan

Hi Jason,

I reckon that this formula may be what you want for [Days in House]1:

=IF(Status1 = "Complete", Status1, NETWORKDAYS([Date Application Received]1, TODAY()))

You also may like to put your holidays into the NETWORKDAYS function if you wish to deal with real business days. If you want elapsed days rather than business days, then remove the NETWORKDAYS function and just make it the difference between the dates (plus 1 or not depending on how you consider acting on an application that you received today).

Cheers,

Rob.

Rob Hagan

Hi again Jason,

Also, if you have any reports that draw the Status data from that sheet then unless you open and save that sheet every day (to allow the formula to be re-evaluated because it has the TODAY() function in it) the report may show an "out of date" status.

Cheers,

Rob.

Jason Lee

Hi Rob,

Thank you for your input. I tried the formula but the under the "Days in House" column, it still shows as a number when the status is set to complete. I would like it so that once the status is complete, then it would stop counting the days and just show as "complete." Is there a formula that can do that?

J. Craig Williams

Jason,

Rob's formula show be in the [Days in House] column and row 1.

=IF(Status1 = "Complete", Status1, NETWORKDAYS([Date Application Received]1, TODAY()))

The 1's in the formula are the row number.

When you copy the formula to row 2, it will look like this:

=IF(Status2 = "Complete", Status2, NETWORKDAYS([Date Application Received]2, TODAY()))

Otherwise, it appears the formula should do what Rob provided.

I would have written this formula:

=IF(Status1 = "Complete", "Complete", NETWORKDAYS([Date Application Received]1, TODAY()))

unless there was a requirement to account for other statuses that would result in a blank [Days in House]

Craig