Specific date placed in date range

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.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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, ...............

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for context?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • S0ccer7
    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
    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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, ...............

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!