Need Formula - Total # of "Working Days" Left

03/20/18 Edited 12/09/19

Hello and thanks in advance for your help! I would like to automatically calculate the total # of "working days" left (as of today) by referencing the Due Date. I did find a formula for total number of all days left "=[Due Date]23 - TODAY()" but I haven't been able to find one that just calculates working days. Here is some background so you know what I am trying to do:

I have a column denoting the estimated # of hours left on a given task (row) that I adjust manually as I go. I want to be able to divide that by # of days left to give me the number of hours that I will need to dedicate to that task each day "as of today" to make sure I make my due date.

Here's the formula that I am using to get the number of hours I will need to work each day as of today to reach my end date goal:

=[Est Hours Left]3 / ([# Days Left]3 * 8) * 8

Without the formula I am requesting here, I will have to go into each task each day and do a manual adjustment and I am hoping to automate that piece.

Thanks again for your help!

Mike

Popular Tags:

Comments

  • Mike WildayMike Wilday ✭✭✭✭✭
    edited 03/20/18

    Hi Mike, 

    =netWorkdays([Due Date]23, Today())

    Will give you the difference between those two dates. You may need to swap today with the due date to get a positive or negative number depending on what you are looking for. 

    https://help.smartsheet.com/function/networkdays

  • Hi Mike and thanks for your help!

    I used this "=netWorkdays([Due Date]23, Today())" and I did get a negative number.

    And then I tried swapping "Today" with "Due Date" and I got an error.

    Can you please provide the proper format using "Due Date" instead of "Today" as I must be doing something wrong.

    Thanks again!

    Mike

  • Mike WildayMike Wilday ✭✭✭✭✭

    =netWorkdays(Today(),[Due Date]23) Should do the trick. 

  • Hi Mike,

    This works just as I need it to and I can't thank you enough for taking the time to help me out.

    I'm new to SmartSheet so you'll probably see me here again as I build what I need and learn how to utilize this system.

    Best!

    Mike

  • Mike WildayMike Wilday ✭✭✭✭✭

    You're welcome! I'm glad I could be of help.

Sign In or Register to comment.