What is the best way to pull data for the current year from another sheet?
I have a sheet that pulls monthly data for items corrected and not corrected. The formula I use works great, but I only want data in the corrected column to use current year data. Here is the current formula: =countifs({Hospital Month Corrected}, "3", {Hospital DateCreated Corrected}, Month(@cell)=3)
This gives me the number of items corrected in March.
Best Answers
-
You can use the TODAY function to pull in the current year.
=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
-
@David011 You shouldn't have needed the DATE function. I use my example very frequently on the [Created Date] and [Modified Date] type columns without issue.
-
Paul, Thank you for your response. The formula you provided did work after testing. I included it here again for reference.
=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
Answers
-
Hi David, I am not the best with formulas, but i was doing something very similar, and here's what I used - not sure if it will help you, but you could give it a shot 😊
=countifs({Hospital Month Corrected}, "3", {Hospital DateCreated Corrected}, Month(@cell)=3, {Hospital DateCreated Corrected}, YEAR(@cell) = 2025)
Cayla Davis | Technology Strategy and Optimization Manager
Halifax, Nova Scotia, Canada -
Cayla,
Thank you for your response. The addition to the formula works perfectly.
Is there an alternative method, perhaps using helper columns in the source sheet, that would eliminate the need to update the year in the formula annually? This adjustment is necessary for 25 sites, each with 12 months of data.
-
You can use the TODAY function to pull in the current year.
=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
-
Thank you for your response, Paul. I did try a formula similar to the one you listed. My issue is that my column uses Created Date, which includes the time of day. I did find a work around with the formula listed below.
=COUNTIFS({LGO Month Corrected}, "3", {LGO DateCreated Corrected}, MONTH(DATE(YEAR(@cell ), MONTH(@cell ), DAY(@cell ))) = 3, {LGO DateCreated Corrected}, YEAR(DATE(YEAR(@cell ), MONTH(@cell ), DAY(@cell ))) = YEAR(TODAY()))@cell
-
@David011 You shouldn't have needed the DATE function. I use my example very frequently on the [Created Date] and [Modified Date] type columns without issue.
-
Paul, Thank you for your response. The formula you provided did work after testing. I included it here again for reference.
=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
Help Article Resources
Categories
Check out the Formula Handbook template!