Need Formula - Total # of "Working Days" Left

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

Find more posts tagged with

Formulas

Comments

Mike Wilday

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

mkrobo

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

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

mkrobo

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

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

unknown

This content has been removed.

Smartsheet-08.png

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!

unknown

This content has been removed.

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

unknown

This content has been removed.

Genevieve P.

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

unknown

This content has been removed.

Smartsheet-12.png

Genevieve P.

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

unknown

This content has been removed.

unknown

This content has been removed.

Sign in to join the conversation!

Quick Links

Trending discussions

Multi Select
I have a multi select drowndown with users. (for example: Bill, Joe, Ted, John, and Tom) are selected. I am trying to look in another sheet to tell me if these people have submitted a status for project 123. (Sheet 2 is a list of status's submitted: Project | Submitted by | Status 123 | Bill | Green 123 | Joe | Green 123 |…
Auto Fill Date When Column Checked
I want my date column to auto fill when I check the column Verification and Closure. What formula do I use? I want to ensure that the date doesn't change as time goes on.
Employee Reviews - Pulling & Averaging Category Scores
Hi all, thanks in advance for your assistance! I am working on building a new employee review process for my company and am caught up on one of the final steps. Here's where I am at: I have created an employee review form that individual managers will complete Each employee will have 3-5 reviewers The form submissions will…