using netdays to detemine number days from quote day to closed

Pepenav · 07/09/19

I am using the following formula (gained from a previous community entry, thanks guys) =IFERROR(NETDAYS([Construction quote date]@row, TODAY()), 0)

it is calculating the number days between a quote date column and today's date.

however I want to fix the number of days when the actual close date is entered into a cell column called 'Actual Close date' or similar creating a permanent record thereafter of the number of days for a specific job row from quote date to close date.

I can't seem to fine a problem that is similar and just not quite sure how to set out to solve.

I have included screen shots

The Number days to sell is showing 121 ( between 12/3/19 and todays date) I have however added the actual sell date 12/6/19 in the actual close date column, and in that circumstance I would like that sell days to change to the difference betwee 12/3/19 and 12/6/19.

any suggestions ?

Ok reading further clearly the 'today" formula wont work as a long term solution so I need to create another column but would welcome suggestions

quote date.PNG

Pepenav · 07/10/19

Ok I have come up with this attempted workaround but it doesn't

New column pending or complete

"Pending" if actual close date is blank ( is working) although I need to add the otther side of the argument if there is a date then "complete"

=IF(OR([Actual TDL Con close date]1 = ””, “pending”), IF[Actual TDL Con close date]1 = “date” , “completed”))

The first part to pending works as a standalone, but what I am doing to join them is missing something?

days column to count days between quote day and todays date if pending and used the date shown in actual close date if above column is completed.

Suggestions ?

Paul Newcome · 07/10/19

WE can actually build off of your original IFERROR formula to specify that if there is a date in the [Actual Close Date] column to use that, otherwise use TODAY(). We put this in your NETDAYS function and your second date will automatically be populated based on whether or not there is an actual close date.

.

Original:

=IFERROR(NETDAYS([Construction quote date]@row, TODAY()), 0)

.

Modified:

=IFERROR(NETDAYS([Construction quote date]@row, IF(ISDATE([Actual Close Date]@row), [Actual Close Date]@row, TODAY())), 0)

Mike Wilday · 07/10/19

=(The joy and complexity of nesting formulas.( Sigh. ))

Paul Newcome · 07/10/19

Haha. I tend to break things down into as small of pieces as I possibly can then put them all together following cell references in the other pieces once everything is working.

I also use this to see where I can simplify because I tend to overthink the initial solution.

using netdays to detemine number days from quote day to closed

Comments

Categories