Need Formula - Total # of "Working Days" Left

mkrobo
mkrobo
edited 12/09/19 in Formulas and Functions

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

Tags:
«1

Comments

  • Mike Wilday
    Mike 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 Wilday
    Mike 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 Wilday
    Mike Wilday ✭✭✭✭✭✭

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

  • Surfer_Jet
    Surfer_Jet ✭✭
    edited 05/15/23

    OK! Don't go anywhere. I tried this and looked around. No such luck.

    I am asking for the formula for "7 Days Remaining" in a Column.

    Initially, the number is "7", but every additional day, it slips to "6, 5, 4, 3, 2, 1, 0."

    Please see the attachment.

    Thank you!

    Mikey


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Surfer_Jet

    I hope you're well and safe!

    Not sure I follow! Please elaborate if this isn't what you're after.

    Try something like this.

    =NETDAYS(TODAY(), EndDate@row)

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Surfer_Jet
    Surfer_Jet ✭✭
    edited 05/15/23

    Wow! @Andrée Starå

    Thank you for the post. I tried different variations of your formula without success.

    First, ignore the "Duration" column.

    Here are 3 attachments.

    My objective is to establish a Start Date1 which is 05/14/23 in the first attachment. See the formula I did in Smartsheet-09. In Smartsheet-09, today is 05/15/23, and (for the moment) correctly displays "6."

    However, your formula is better than mine. See Smartsheet-10. I modified it a little bit, but it is still wrong.

    When I copy and paste it into the cell in Smartsheet-09, it should display "6." See Smartsheet-11.

    There are seven days between Start Date1 and Target Date.

    I would like the correct formula for subtracting the days left between Start Date1 and Target Date.

    Initially, the number is "7", but every additional day, it slips to "6, 5, 4, 3, 2, 1, 0."

    It works to a point in Smartsheet-09 but I am sure it is not the correct formula.

    I hope this explains it better.

    Cheers!

    Mikey


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Surfer_Jet

    The problem here is that your Start Date is static. @Andrée Starå's formula is using "Today" as the Start Date, but it sounds like you only want that if the Start Date is in the past.

    What you can do here is use a Nested IF statement to first check and see if the Start Date is in the future, and if it is, return something like "N/A". Then once Today is the same as the Start Date, that's when you can have the formula start ticking down. Once the Target End date is in the past, we'll want it to say 0.

    Try:

    =IF([Target Date]@row = "", "", IF([Target Date]@row <= TODAY(), 0, IF([Start Date]@row > TODAY(), "N/A", NETDAYS(TODAY(), [Target Date]@row)))


    I also note that your column says "working days" remaining. In this case, you'll want to use NETWORKDAYS instead of NETDAYS, so it excludes weekends.

    Cheers,

    Genevieve

  • Hello,

    Thanks!

    Unfortunately, it does not work.

    I am learning Smartsheet as fast as I can, but it is CONFUSING. The learning curve is STEEP, even though I am familiar with Excel.

    Where in here can I pay for someone to view my screen and get beyond this?

    Is this possible?

    Miguel

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Surfer_Jet

    Can you explain what's not working? Are you getting an error message or an incorrect result? Screen captures would be helpful again. 🙂

    If you're on a Business or Enterprise plan you can purchase Pro Support which includes 30-minute coaching sessions (called Pro Desk sessions) to get you up and running, as well as all the eLearning courses in the Smartsheet University. Here's more information on Pro Support.

    You can also view free Onboarding Webinars or Best Practice Webinars from our Help and Learning site, as well as register for a live webinar: https://help.smartsheet.com/webinars

    Cheers,

    Genevieve

  • Surfer_Jet
    Surfer_Jet ✭✭
    edited 05/18/23

    Dear Ms. Genevieve,

    Let's give it a shot.

    I ran up against a deadline due 1-week ago.

    I figured out everything else and it's loaded beyond my wildest dreams. Took a few months. :)

    One additional piece of information. When the Status is set to "X = Not Started, those columns go blank until Status column changes to "Y = Start."

    So, hopefully, this will do it.

    See attachment.

    Miguel

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Surfer_Jet

    My apologies! Thank you for this screen capture, it helped a lot: I've just clued in that your Start Date column name has a 1 after it! In this case we'll need to add that into the formula as well.

    =IF([Target Date]@row = "", "", IF([Target Date]@row <= TODAY(), 0, IF([Start Date1]@row > TODAY(), "N/A", NETDAYS(TODAY(), [Target Date]@row)))

    I would suggest building formulas directly in Smartsheet and clicking on the cells you want to reference so it auto-pulls in the correct column name.

    Let me know if this works now!

    Genevieve

  • Surfer_Jet
    Surfer_Jet ✭✭
    edited 05/18/23

    Hello Ms. @Genevieve P.

    oh my!

    I am the one that is sorry.

    I should have seen that "1" myself!

    btw, this formula, for me at least, is complex. Thanks!

    I am going to test it over the weekend.

    I am still going to schedule a 1/2 hour session as a Q&A.

    Once again, Thanks!

  • Ms. @Genevieve P. quickie, please. I submitted a salesperson to contact me for an hour of help. how long does it take for them to respond?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!