using netdays to detemine number days from quote day to closed

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

Find more posts tagged with

Sheets

Conditional Formatting

Comments

Pepenav

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

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

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

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…