Calculating days between dates, 'unless'...

Hi folks,

I have a date column for 'Suggested Risk Assessment Review', and a date column for 'Planned Review Date'. I need a formula to count the days between these (i.e. how overdue are they), UNLESS the 'done' checkbox is ticked.

How do I calculate this?

Thanks!

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi @nicole.turner

I hope you're well and safe!

Try something like this.

=
IF(Done@row = 1, "", 
IFERROR(NETDAYS([Suggested Risk Assessment Review]@row, [Planned Review Date]@row), ""))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

nicole.turner

Hi @Andrée Starå

Unfortunately this is coming up as #UNPARSEABLE, even when I refer to the columns with their column number OR their column name. Any ideas?

Nicole

Andrée Starå

@nicole.turner

Strange!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Andrée Starå

@nicole.turner

Please take a look at the sheet now and see if it works as expected or not.

Did it work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

nicole.turner

@Andrée Starå thank you!! This one worked. Appreciate it!

=IF([FRA Done?]@row = 1, "", IFERROR(NETDAYS([Date Review Planned]@row, [Date for Suggested Review]@row), ""))

Andrée Starå

@nicole.turner

Excellent!

You're more than welcome!

nicole.turner

@Andrée Starå out of curiosity, how would I calculate this exact same thing, only instead of the difference between a suggested date and a planned date, to calculate the difference between the suggested date and 'Today'?

Andrée Starå

@nicole.turner

I've added the below formula to your comment column on row 4 in your sheet.

=IF([FRA Done?]@row = 1, "", IFERROR(NETDAYS([Date for Suggested Review]@row, TODAY()), ""))

Did it work as expected?

Sign in to join the conversation!

Quick Links

Trending discussions

Best formula to link data in another cell
Hi all, I wanted to know what the easiest way would be to link data from one sheet to another. I have working cross-reference formulas, cell-link, and even just using miscellaneous formulas to reference them, just as proof of concept. I was wondering, what is the difference between using Index-Match to pull in data, versus…
Pulling in data from 1 sheet to another isn't working with any function.
PMO Template - I want to pull in the Project Name Column from the Project Metadata sheet into my custom Attendance Sheet > Project Name Column. I've tried VLOOKUP, INDEX MATCH, JOIN COLLECT, I've changed parenthesis to brackets. I am new to Smartsheets and can't get anything to work. I've watch videos, followed community…
Help With If Count If to Identify Unique Values in a Column
I'm trying to make a helper column to identify if a new/unique project code has been entered in a column so that I know when a new project has been created. I've been trying to set up this formula and plan to then set up an automation to be notified when "New Project Code" has been entered into a cell. It seems to be ok on…