Calculating days between dates, 'unless'...

nicole.turner

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!

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!

✅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å 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?