Need help calculating estimated completion date for production while excluding weekends

Taylor Mauzy

Hello Smartsheet Community!

I have a project I am working on for work, and I am struggling with a formula to calculate the expected date of completion for our production orders based on a few criteria. A few things are missing that are confidential, but I have attached a picture of a practice spreadsheet:

Please note: a lot of this is me playing around trying to make it work, so it's a little messy. The two entries highlighted in yellow are in the column I am trying to make work. What I need to do is determine when our order SHOULD finish up, based on the 'Expected Days Until Completion' column which is currently set to this:

=(([Work Order QTY]@row - [Current FG Units]@row) / [Planned QTY/Hour]@row) / [Hours Per Day]@row

The dates are off right now, but, the way I need it to work is I need to make a formula that calculates the expected date of completion, excluding weekends, by using the expected days until completion (or some other column(s) which would work more intuitively) to let me know what the estimated completion date is, which will go in the highlighted column.

I currently have this formula in that column, but it doesn't exclude weekends:

=TODAY() + [Expected Days Until Completion]@row

I have also tried using a second formula that kicks out 'incorrect argument set' which is probably due to the fact that the 'end_date' is not actually a date:

=TODAY() + NETWORKDAY(TODAY(), NETWORKDAYS([Expected Days Until Completion]@row))

If someone could help me work this out, I would really appreciate it! I have already figured out how to pull this data into a secondary sheet with Index/Match and Index/Collect, but I am struggling with this and it should be simple. I think I am making it harder than it needs to be. Thank you in advance for your time!!!

JamesB

@Taylor Mauzy

I believe this will work based on your ask.

In your Expected Days to Completion, you need to use a round formula to make it a whole number.

=Round([Work Order QTY]@row - [Current FG Units]@row) / [Planned QTY/Hour]@row) / [Hours Per Day]@row,0)

Then in your Est Completion Date, you should be able to use

=WORKDAY([Scheduled FPA]@row, [Expected Days Until Completion]@row)

JamesB

@Taylor Mauzy

I believe this will work based on your ask.

In your Expected Days to Completion, you need to use a round formula to make it a whole number.

=Round([Work Order QTY]@row - [Current FG Units]@row) / [Planned QTY/Hour]@row) / [Hours Per Day]@row,0)

Then in your Est Completion Date, you should be able to use

=WORKDAY([Scheduled FPA]@row, [Expected Days Until Completion]@row)

Taylor Mauzy

Hello @JamesB,

Thank you for taking the time to reply. I tried what you showed above and was able to get it working, however, rounding the days will lead to overestimation/underestimation since rounding ends up meaning more/less depending on the value. If I can find a way to use hours, as opposed to days, then I will be able to achieve exactly what I set out to do. Do you have any thoughts for how to do this using hours? I will wait to see if I can any other answers from you or others, but I will make sure to close out the question once I figure it out.

JamesB

@Taylor Mauzy Wouldn't you then just take your Expected Days Until Completion and multiply it by the number of working hours in the day?

=([Expected Days Until Completion]@row*8)

Taylor Mauzy

@JamesB I tried that and a few other things, but when it's in hours, it adds those as days. I think that is probably a limitation of the WORKDAY formula. I tried looking at NETWORKDAY(S) and that appears to have the same issue. I will have to try and think of a creative way to solve this using other formulas, but I will give you the answer based off your first response. It has gotten me very close to where I am needing to be, I just need to find a way around the rounding leading to the days being short/long. Thank you for your help!