Calculating Weeks out from a date

Crystal Panning

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

Mike Wilday

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

Mike Wilday

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

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

should do the trick. 

Crystal Panning

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

Andrée Starå

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

Paul Newcome

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)

Andrée Starå

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

Paul Newcome

Hahaha. Happens to me rather frequently.

Mike Wilday

Haha. Me too! 

Crystal Panning

Yay it worked! Thank you both!!

Crystal Panning

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? 

Crystal Panning

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!!

Mike Wilday

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!  

Andrée Starå

Excellent!

Happy to help!

Anna Keeton

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)

Thanks in advance, Anna

RebekahD

Hey @Anna Keeton,

I'm sure you've resolved the issue by now, but in case someone is reading this thread looking for answers I thought I'd share that your end date would need a row assigned. Like this if you want it to be a column formula. I hope you figure it out!

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