Autocomplete date range
Hi All,
This is likely something quite straight forward that I'm over looking, but hoping someone may be able to advise - Still relativly new to smartsheets.
I have a smartsheet, in which each row reports based on week numbers, such as the below formula.
=COUNTIFS({Enquiry Tracker (V1) Range 4}, <=DATE(2020, 9, 6), {Enquiry Tracker (V1) Range 4}, >=DATE(2020, 8, 31))
Is there a way that I can extend this down the column and to auto-complete the next date range, such as below?... If I fill out the first 4 or 5, and try to select all and drag down, it just repeats the sequence for the first 5 at the moment.
Week number 36
=COUNTIFS({Enquiry Tracker (V1) Range 4}, <=DATE(2020, 9, 6), {Enquiry Tracker (V1) Range 4}, >=DATE(2020, 8, 31))
Week number 37
=COUNTIFS({Enquiry Tracker (V1) Range 4}, <=DATE(2020, 9, 14), {Enquiry Tracker (V1) Range 4}, >=DATE(2020, 9, 7))
Week number 38
=COUNTIFS({Enquiry Tracker (V1) Range 4}, <=DATE(2020, 9, 22), {Enquiry Tracker (V1) Range 4}, >=DATE(2020, 9, 15))
Etc…
Screenshot also attached – manually typing these all out has been taking quite some time, hoping there may be an easier way!
Thanks in advanced, Glen.
Best Answers
-
Assuming that Week 1 start in 2020 was 12/31/2019 replace your formula with this:
=COUNTIFS({Enquiry Tracker (V1) Range 4}, <=(DATE(2019, 12, 31) + ([Week Number]@row - 1) * 7+7), {Enquiry Tracker (V1) Range 4}, >=(DATE(2019, 12, 31) + ([Week Number]@row - 1) * 7))
If this works for you, copy the formula into all cells in the column and it should work based on week # value.
Better yet convert the formula to a column formula (newest Smartsheet feature) and you won't even have to copy it.
I hope this works or you.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
Just a quick follow up - I've been reading more about @row formulas , and decided to insert two additional sequential columns;
"Week number start date" and "Week number end date"
Its now works well as below.
=COUNTIFS({Enquiry Tracker (V1) Range 4}, <=[Week number end date]@row, {Enquiry Tracker (V1) Range 4}, >=[Week number start date]@row)
Formula now works well, and I have applied the same principle to all columns.
Thanks for the advice! Saved me a good day of data entry!!
Cheers, Glen
Answers
-
Assuming that Week 1 start in 2020 was 12/31/2019 replace your formula with this:
=COUNTIFS({Enquiry Tracker (V1) Range 4}, <=(DATE(2019, 12, 31) + ([Week Number]@row - 1) * 7+7), {Enquiry Tracker (V1) Range 4}, >=(DATE(2019, 12, 31) + ([Week Number]@row - 1) * 7))
If this works for you, copy the formula into all cells in the column and it should work based on week # value.
Better yet convert the formula to a column formula (newest Smartsheet feature) and you won't even have to copy it.
I hope this works or you.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
Thanks for your response!
The sheet doesnt actually start until week 36, which is row 1 (when the company adapted smartsheet).
- I changed the formula to the below, based on the date at the start of the sheet.
=COUNTIFS({Enquiry Tracker (V1) Range 4}, <=(DATE(2020, 9, 6) + ([Week Number]@row - 1) * 7 + 7), {Enquiry Tracker (V1) Range 4}, >=(DATE(2020, 8, 31) + ([Week Number]@row - 1) * 7))
When copying the formula to the cell below, the formula remains the same, as does the date.
When selecting the cell it does now link to the "week number" column for the appropriate date, but doesnt have any bearing on the result.
Is there somehting else that Im missing?
Cheers!
-
Just a quick follow up - I've been reading more about @row formulas , and decided to insert two additional sequential columns;
"Week number start date" and "Week number end date"
Its now works well as below.
=COUNTIFS({Enquiry Tracker (V1) Range 4}, <=[Week number end date]@row, {Enquiry Tracker (V1) Range 4}, >=[Week number start date]@row)
Formula now works well, and I have applied the same principle to all columns.
Thanks for the advice! Saved me a good day of data entry!!
Cheers, Glen
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!