Autocomplete date range

Glen Urquhart

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.

 

Ramzi K

@Glen Urquhart

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

Glen Urquhart

@Ramzi

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

Ramzi K

@Glen Urquhart

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

Glen Urquhart

@Ramzi

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!

Glen Urquhart

@Ramzi

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