Calculating Weeks out from a date

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
Answers
-
Not that I can think of. Looks like you nerded out on the right solution!!!Β
-
I take that back. You can combine those formulas into one!Β
=NETDAYS([Start Date]1, [Set Date]$109) / 7
should do the trick.Β
-
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
-
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.
-
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)
-
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.
-
-
Haha. Me too!Β
-
Yay it worked! Thank you both!!
-
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?Β
-
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!!
-
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!
Β
-
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.
-
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
-
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)
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 204 Use Cases
- 515 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives