location and date count

Options

Alrighty

Need some help with a formula that will search a location and division for specifics and then look at the newest date from that location and division and give me a count since that date. the first two columns are static but the data sheet is updated by forms weekly. this is a shot of the metric sheet for the dashboard. i need it to find the most recent recordable date and give me a count on how many days it has been.


Best Answer

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

    Ok. You are going to want to start with a MAX/COLLECT to pull the most recent date for your location/division combo.

    =MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Location Column}, @cell = Location@row, {Other Sheet Division Column}, @cell = Division@row))


    Once we subtract that from TODAY(), we will know how many days it has been since the last submission for that particular location/division.

    =TODAY() - MAX(COLLECT({Other Sheet Date Column}, {Other Sheet Location Column}, @cell = Location@row, {Other Sheet Division Column}, @cell = Division@row))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!