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!

Yessica
Yessica
edited 12/09/19 in Archived 2017 Posts

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!

 

Comments

  • KrisWalsh
    KrisWalsh ✭✭✭✭✭
    edited 06/16/17

    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.  smiley

     

    (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!  (:

  • 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? 

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

    On to the next one! 

  • 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
    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*

     

    Reminders.png

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

  • 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.