looking for a fast way to auto update the year on this formula to 2023

New Contributor

I do my monthly inventory bookkeeping in excel and at the end of the year I am left with formulas that pertain to only that year and have been going in and manually changing each date to reflect the current year. Since they're populated by month, I have to change 12 tabs x 10 categories and it's tedious.

Is there a way to auto update the tabs to reflect the current year?

Or possibly update just the first tab (JAN) manually and then do a simpler copy/paste that will auto populate the correct dates for the remaining 11 months?

Here's the formula

=SUMIFS(materials!$B:$B,materials!$A:$A,">12/31/2021",materials!$A:$A,"<2/1/2022")

 

For use in 2023 it would need to be

=SUMIFS(materials!$B:$B,materials!$A:$A,">12/31/2022",materials!$A:$A,"<2/1/2023")

 

Any guidance is appreciated.

2 Replies

@junebuganddarlin

Press Ctrl+H to activate the Replace dialog.

If necessary, click 'Options >>'.

Set Within to Workbook, and make sure that 'Look in' is set to Formulas, and that 'Match entire cell contents' is not ticked.

Enter 2022 in the 'Find what' box and 2023 in the 'Replace with' box.

Click 'Replace All'.

Next, enter 2021 in the 'Find what' box and 2022 in the 'Replace with' box.

Click 'Replace All'.

That should do it.