# 3 years of data, twice a year

Options

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!

• ✭✭✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

You would start your dates with

Data Set 2:

=DATE(YEAR(TODAY()) - 1, 12, 31)

Data Set 1:

=DATE(YEAR(TODAY()) - 1, 06, 30)

Then for each you would want to reference that cell and subtract 1. Dragfilling will quickly populate the rest of the column.

=[Date Column]1 - 1

• Options

Thank you! I just realized I was super unclear about the end goal here...LOL. I'm basically doing several different COUNTIFS. For the Data Set 2, I've just done each year separately and then added those to get the totals. Here's one of the formulas:

=COUNTIFS([Contract Type]:[Contract Type], = "Actuary of Record", [New or Incumbent]:[New or Incumbent], = "New", Status:Status, OR(@cell = "Win", @cell = "Win_Contract Negotiations", @cell = "Subcontracting with Prime_Win"), [RFP Due Date]:[RFP Due Date], IFERROR(YEAR(@cell), 0) = 2018)

How can I modify to pull each of the data sets at once instead of separately?

• ✭✭✭✭✭✭
Options

Do you mean for each of the different years, or do you mean incorporating something into the above formula to automatically update the rolling start/end dates based on today's date?

• Options

For each of the different years.

Would I just do this for the date portion: [RFP Due Date]:[RFP Due Date], AND(@cell >= DATE(2018, 1, 1), @cell <= DATE(2020, 12, 31))

• ✭✭✭✭✭✭
Options

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))

• edited 01/19/21
Options

Oh perfect! So then for the mid-year formula I would do this? [RFP Due Date]:[RFP Due Date], AND(@cell >= DATE(YEAR(TODAY()) - 4, 7, 1), @cell <= DATE(YEAR(TODAY()) - 1, 6, 30))

• ✭✭✭✭✭✭
Options

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.

• Options

Thank you so much!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!