Formula HELP!

Yessica

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!

 

KrisWalsh

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.

I just posted something that will help you with this:

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

Example Sheet: Hierarchy Formulas & Conditional Formatting

 

Hope this helps & Good Luck!  (:

Yessica

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? 

Yessica

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

On to the next one! 

Yessica

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. 

Thanks in advance! 

KrisWalsh

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.
2. At the bottom of your sheet, Click Alerts/Reminders
3. Setup a Reminder that sends an email to whoever owns the task, X days before their task is due. *see pic*

 

Yessica

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

Yessica

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

=[Due Date]1 - TODAY()