Need Formula - Total # of "Working Days" Left

mkrobo

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

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.

Surfer_Jet

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

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!

Surfer_Jet

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

Smartsheet-09.png

Smartsheet-10.png

Smartsheet-11.png

Genevieve P.

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

Surfer_Jet

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.

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

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. :)

Smartsheet-12.png

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.

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

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!

Surfer_Jet

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?