# Autocomplete date range

Options
✭✭✭✭

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!

• ✭✭✭✭
Options

"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

• ✭✭✭✭
Options

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!

• ✭✭✭✭
Options

"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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!