3 years of data, twice a year

Anna Deschenes

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!

Paul Newcome

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

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.

Paul Newcome

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

Anna Deschenes

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?

Paul Newcome

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?

Anna Deschenes

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

Paul Newcome

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

Anna Deschenes

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

Paul Newcome

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.

Anna Deschenes

Thank you so much!

Paul Newcome

Happy to help. 👍️