3 years of data, twice a year

I'm not sure this is even possible, but we want to display 3 years of data at year-end and then at mid-year. So, for the current data, we want the following: Dataset 1 = 7/1/2017-6/30/2020, Dataset 2 = 1/1/2018-12/31/2020. Ideally, this would be rolling, but if we have to manually update, that's fine too - I'm just not sure what formula to use for the dates.

Thanks!

Best Answers

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

    You could do that, but then you would be updating the formula every year. To make it more dynamic, you can use a YEAR(TODAY()) set to automate the years.


    [RFP Due Date]:[RFP Due Date], AND(@cell >= DATE(YEAR(TODAY()) - 3, 1, 1), @cell <= DATE(YEAR(TODAY()) - 1, 12, 31))

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

    That should work. If you wanted to do a little testing, you could enter the DATE portion into a date type column just to be sure it is in fact displaying the correct dates.


    That's generally how I build out most of my formulas too. I test each piece separately and use cell references to each piece in the final formula. It allows me to see which pieces need tweaked and which are working as they should. Then I can go through and replace each cell reference in the final formula with the formula that is in the corresponding cell. If you grab everything except for the "=" at the beginning, it also really helps with making sure all of your parenthesis are in place.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!