Specific date placed in date range

S0ccer7

Hello,

I am trying to do a sumifs and one of my conditions is based on date. My reference sheet has specific dates (Ex. 12/19/23) but my new sheet has date ranges by week (Ex. 12/18/23-12/24/23).

How can I pull the data with the specific date and place that data within my date range on the new sheet?

Thank you.

Paul Newcome

There's the issue. You have text strings that only look like dates. Try adding in two more helper rows (for a total of three) so that you have one for the year, one for the month, and one for the day. Then you can adjust your SUMIFS like so:

=IF(SUMIFS({Copy of Recon Sheet - Active Range 2}, {BCA}, $BCA@row, {Date}, DATE([Wk 45 11/6]3, [Wk 45 11/6]4, [Wk 45 11/6]5) <= @cell, ...............

Paul Newcome

Are you able to provide some screenshots for context?

S0ccer7

Hey Paul,

Here is my reference sheet with a specific date column.

Here is where I need the values, in this case 2" and 4" footage, to be logged in its respective date range.

What my sumifs currently looks like:

=IF(SUMIFS({Copy of Recon Sheet - Active Range 2}, {BCA}, $BCA@row, {Date}, [Wk 45 11/6]3 <= @cell, {Date}, [Wk 46 11/13]3 > @cell) = 0, " ", SUMIFS({Copy of Recon Sheet - Active Range 2}, {BCA}, $BCA@row, {Date}, [Wk 45 11/6]3 <= @cell, {Date}, [Wk 46 11/13]3 > @cell))

Things to note about formula:

• {Copy of Recon Sheet - Active Range 2} is the column for 2" PL
• {BCA} is a column on both sheets that is a unique identifier for each project since we have multiple on one sheet

And I do have this row at the top to use as a date reference in my formula

S0ccer7

https://community.smartsheet.com/discussion/comment/408622#Comment_408622

Hey Paul,

Here is my reference sheet with a specific date column.

Here is where I need the values, in this case 2" and 4" footage, to be logged in its respective date range.

What my sumifs currently looks like:

=IF(SUMIFS({Copy of Recon Sheet - Active Range 2}, {BCA}, $BCA@row, {Date}, [Wk 45 11/6]3 <= @cell, {Date}, [Wk 46 11/13]3 > @cell) = 0, " ", SUMIFS({Copy of Recon Sheet - Active Range 2}, {BCA}, $BCA@row, {Date}, [Wk 45 11/6]3 <= @cell, {Date}, [Wk 46 11/13]3 > @cell))

Things to note about formula:

• {Copy of Recon Sheet - Active Range 2} is the column for 2" PL
• {BCA} is a column on both sheets that is a unique identifier for each project since we have multiple on one sheet

And I do have this row at the top to use as a date reference in my formula

Paul Newcome

Is [Wk 45 11/6] a Date type column?

S0ccer7

https://community.smartsheet.com/discussion/comment/408869#Comment_408869

No sir.

Paul Newcome

There's the issue. You have text strings that only look like dates. Try adding in two more helper rows (for a total of three) so that you have one for the year, one for the month, and one for the day. Then you can adjust your SUMIFS like so:

=IF(SUMIFS({Copy of Recon Sheet - Active Range 2}, {BCA}, $BCA@row, {Date}, DATE([Wk 45 11/6]3, [Wk 45 11/6]4, [Wk 45 11/6]5) <= @cell, ...............