#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Formula HELP!

Options
edited 12/09/19

Need Help! I need a few of formulas.

(1) Formula to automatically populate a due date (2 weeks after start date).

(2) Formula to calculate duration between two date columns.

(3) Total parent rows (projects) and total based on parent color.

I'm at the end of my wits!

Thanks everyone!

• ✭✭✭✭✭
edited 06/16/17
Options

Hi Yessica!

Best Practice: Keep your own version of the Smartsheet Formulas Template and create a "My Favorite Formulas" section. You'll find yourself going back to it over and over again.

(1) Formula to automatically populate a due date (2 weeks after start date).

=\$[Start Date]1 + 14                       ? Will land on any day of the week

=WORKDAY(\$[Start Date]1, 9)       ? Will only land on M-F - Only counts working days

(2) Formula to calculate duration between two date columns.

=NETDAYS([Start Date]1, [Due Date]1)               ? Counts every day of the week

=NETWORKDAYS([Start Date]1, [Due Date]1)    ? Only counts work days

(3) Total parent rows (projects) and total based on parent color.

Using Hierarchy Formulas & Conditional Formatting to Color Code Rows in a Sheet

Example Sheet: Hierarchy Formulas & Conditional Formatting

Hope this helps & Good Luck!  (:

• Options

Kris you rock! Thank you for the response. The first formula worked perfectly. Yay!

I'm still having trouble with calculating duration.

Here's what I entered:

=NETDAYS([First Drawing Submittal]1, [Last Submittal To/From Client]1)

And I am getting this response

#INVALID DATA TYPE

I am entering this formula in the first cell under the duration column, is that correct?

• Options

I think I got it. I renamed the column taking out the back slash and it worked.

On to the next one!

• Options

Still working on the last option when a new new came up. I need help with a formula that will calculate the duration between TODAY and DUE DATE.

• ✭✭✭✭✭
Options

Assuming you want to know how may days until an item is due...

Column Name: Days until Due Date  |  Column Type: Text/Number

=[Due Date]1 - TODAY()

I would use conditional formatting to change the color to red if it is less than zero and the task is NOT complete.

You might want to take a look at Reminders, too.  They are very handy.

1. Make the Assigned To/Owner column a Contact Column.
3. Setup a Reminder that sends an email to whoever owns the task, X days before their task is due. *see pic*

• Options

That is exactly what I need! We're building Smartsheet to meet our custom needs.

• Options

Kris, how can I get the formula to show the time left in weeks not days?

=[Due Date]1 - TODAY()

This discussion has been closed.