# Calculating Weeks out from a date

Options
✭✭✭✭
edited 12/09/19

I am trying to calculate weeks out from a set date, and figured out a way, but wondering if there is an easier way without having a helper column (Days Diff column). The below columns is what I have:

Weeks Out    Day Diff    Start Date

I have 2 formulas:

In the Day Diff column I have =NETDAYS([Start Date]1, [Set Date]\$109)

I then have a formula in the Weeks Out column that takes the day amount in the Day Diff column and divides by 7 to get the weeks out.

=[Day Diff]1 / 7

This works and gives me my weeks out, but is there a formula that can calculate the weeks out from the Start Date to the Set Date without using the Day Diff column?

Thank you,

Crystal

• ✭✭✭✭✭✭
Options

Not that I can think of. Looks like you nerded out on the right solution!!!

• ✭✭✭✭✭✭
Options

I take that back. You can combine those formulas into one!

=NETDAYS([Start Date]1, [Set Date]\$109) / 7

should do the trick.

• ✭✭✭✭
Options

Hi Mike,

I tried combining and it came up as invalid. This is what I have.

=NETDAYS([Start Date]1, [Set Date]\$109 / 7)

Any suggestions from here?

Thank you!

Crystal

• ✭✭✭✭✭✭
Options

Hi Crystal,

You're missing a parenthesis after 109.

Try this.

=NETDAYS([Start Date]1, [Set Date]\$109) / 7)

Did it work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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.

• ✭✭✭✭✭✭
edited 10/02/19
Options

This will still throw an error though because of the closing parenthesis at the end without an opening parenthesis.

=NETDAYS([Start Date]1, [Set Date]\$109) / 7

(Mike's solution)

• ✭✭✭✭✭✭
Options

Ah yes, I was moving to fast so I just saw the first error and then

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.

• ✭✭✭✭✭✭
Options

Hahaha. Happens to me rather frequently.

• ✭✭✭✭✭✭
Options

Haha. Me too!

• ✭✭✭✭
Options

Yay it worked! Thank you both!!

• ✭✭✭✭
Options

Okay, one more question for you...I got the formula to work, but noticed that it kicks it to the next week when I select a Friday date. Our weeks go from Sunday-Saturday, so is there any way to get it to the next week when it hits a Sunday rather than Friday?

• ✭✭✭✭
Options

Okay, I think I figured it out. I just added a - 2 after my [Start Date]1 and that seemed to do the trick. Here is what I got for the final formula.

=NETDAYS([Start Date]1 - 2, [Set Date]\$109) / 7

Thank you again!!

• ✭✭✭✭✭✭
Options

You're welcome! Glad you figured it out! Another solution might be to add Saturdays and Sundays as working days in the Admin Account settings, but perhaps not. Your solution seems easier!

• ✭✭✭✭✭✭
Options

Excellent!

Happy to help!

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.

• ✭✭✭
Options

Hey all, realise this is a bit of an older chain... but it seems a relevant spot!

I'm looking to calculate weeks out, but using the start date as today, rather than a fixed column date - possible?

here's what I had based on the work you had above... but the computer still says no!

=((NETDAYS(TODAY, [Live Date]) /7)