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 yearend and then at midyear. So, for the current data, we want the following: Dataset 1 = 7/1/20176/30/2020, Dataset 2 = 1/1/201812/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

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

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

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

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?

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?

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

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

Oh perfect! So then for the midyear 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))

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.

Thank you so much!

Help Article Resources
Categories
Check out the Formula Handbook template!