Excel Non VBA change year

[CheepBeerSux]

Hi,

This is my first post so I hope I explain myself correctly. I am building a "notes due" database by the following rules. A person is admitted on a date, then a nursing assessment note is due every 7 days for 8 weeks. Following that they are monthly. The problem is that the time increments are based on the admission date, but some patient's notes end up being years ago in the sequence and only one year is on the spreadsheet. I would like to make the admission dates that are over 365 days auto update to this year. i.e. 10-23-1998 would become 10-23-2022 so I'm not looking at dates that say 1999. No formulas are associated with the admission dates, those cells are open to programming. Thank you in advance.

Weekly Monthly Notes Due2.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	Admission	Nursing Part 1	Nursing Part 2	Note 1	Note 2	Note 3	Note 4	Note 5	Note 6	Note 7	Note 8
2	12/8/2019	12/8/2019	12/9/2019	12/15/2019	12/22/2019	12/29/2019	1/5/2020	1/12/2020	1/19/2020	1/26/2020	2/2/2020
3	5/20/2022	5/20/2022	5/21/2022	5/27/2022	6/3/2022	6/10/2022	6/17/2022	6/24/2022	7/1/2022	7/8/2022	7/15/2022
4	7/7/2022	7/7/2022	7/8/2022	7/14/2022	7/21/2022	7/28/2022	8/4/2022	8/11/2022	8/18/2022	8/25/2022	9/1/2022
5	3/24/2006	3/24/2006	3/25/2006	3/31/2006	4/7/2006	4/14/2006	4/21/2006	4/28/2006	5/5/2006	5/12/2006	5/19/2006
6	1/5/2017	1/5/2017	1/6/2017	1/12/2017	1/19/2017	1/26/2017	2/2/2017	2/9/2017	2/16/2017	2/23/2017	3/2/2017
7	9/7/2021	9/7/2021	9/8/2021	9/14/2021	9/21/2021	9/28/2021	10/5/2021	10/12/2021	10/19/2021	10/26/2021	11/2/2021
8	2/24/2022	2/24/2022	2/25/2022	3/3/2022	3/10/2022	3/17/2022	3/24/2022	3/31/2022	4/7/2022	4/14/2022	4/21/2022
9	11/8/2017	11/8/2017	11/9/2017	11/15/2017	11/22/2017	11/29/2017	12/6/2017	12/13/2017	12/20/2017	12/27/2017	1/3/2018
10	12/1/2020	12/1/2020	12/2/2020	12/8/2020	12/15/2020	12/22/2020	12/29/2020	1/5/2021	1/12/2021	1/19/2021	1/26/2021
11	2/10/2022	2/10/2022	2/11/2022	2/17/2022	2/24/2022	3/3/2022	3/10/2022	3/17/2022	3/24/2022	3/31/2022	4/7/2022
12	5/25/2021	5/25/2021	5/26/2021	6/1/2021	6/8/2021	6/15/2021	6/22/2021	6/29/2021	7/6/2021	7/13/2021	7/20/2021
13	4/13/2017	4/13/2017	4/14/2017	4/20/2017	4/27/2017	5/4/2017	5/11/2017	5/18/2017	5/25/2017	6/1/2017	6/8/2017
14	10/1/2009	10/1/2009	10/2/2009	10/8/2009	10/15/2009	10/22/2009	10/29/2009	11/5/2009	11/12/2009	11/19/2009	11/26/2009
15	10/30/2018	10/30/2018	10/31/2018	11/6/2018	11/13/2018	11/20/2018	11/27/2018	12/4/2018	12/11/2018	12/18/2018	12/25/2018
16	3/5/2020	3/5/2020	3/6/2020	3/12/2020	3/19/2020	3/26/2020	4/2/2020	4/9/2020	4/16/2020	4/23/2020	4/30/2020
17	2/12/2013	2/12/2013	2/13/2013	2/19/2013	2/26/2013	3/5/2013	3/12/2013	3/19/2013	3/26/2013	4/2/2013	4/9/2013
18	2/13/2018	2/13/2018	2/14/2018	2/20/2018	2/27/2018	3/6/2018	3/13/2018	3/20/2018	3/27/2018	4/3/2018	4/10/2018
19	4/9/2014	4/9/2014	4/10/2014	4/16/2014	4/23/2014	4/30/2014	5/7/2014	5/14/2014	5/21/2014	5/28/2014	6/4/2014
20	12/29/2021	12/29/2021	12/30/2021	1/5/2022	1/12/2022	1/19/2022	1/26/2022	2/2/2022	2/9/2022	2/16/2022	2/23/2022
21	12/2/2016	12/2/2016	12/3/2016	12/9/2016	12/16/2016	12/23/2016	12/30/2016	1/6/2017	1/13/2017	1/20/2017	1/27/2017
22	5/19/2022	5/19/2022	5/20/2022	5/26/2022	6/2/2022	6/9/2022	6/16/2022	6/23/2022	6/30/2022	7/7/2022	7/14/2022
23	6/16/2022	6/16/2022	6/17/2022	6/23/2022	6/30/2022	7/7/2022	7/14/2022	7/21/2022	7/28/2022	8/4/2022	8/11/2022
24	1/16/2020	1/16/2020	1/17/2020	1/23/2020	1/30/2020	2/6/2020	2/13/2020	2/20/2020	2/27/2020	3/5/2020	3/12/2020
25	8/25/2010	8/25/2010	8/26/2010	9/1/2010	9/8/2010	9/15/2010	9/22/2010	9/29/2010	10/6/2010	10/13/2010	10/20/2010
26	7/19/2022	7/19/2022	7/20/2022	7/26/2022	8/2/2022	8/9/2022	8/16/2022	8/23/2022	8/30/2022	9/6/2022	9/13/2022
27	6/24/2022	6/24/2022	6/25/2022	7/1/2022	7/8/2022	7/15/2022	7/22/2022	7/29/2022	8/5/2022	8/12/2022	8/19/2022
28	8/5/2022	8/5/2022	8/6/2022	8/12/2022	8/19/2022	8/26/2022	9/2/2022	9/9/2022	9/16/2022	9/23/2022	9/30/2022
29
30
31
32
33			Today + 2d		This week			Today
34			ALL PAST NOTES SHOULD BE COMMENTED AS COMPLETED OR ADDRESSED
35
GTS

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A28:O28,Q28:XFD28	Cell Value	=TODAY()	text	NO
A28:O28,Q28:XFD28	Dates Occurring	this week	text	NO
A28:O28,Q28:XFD28	Cell Value	between TODAY() and TODAY()+2	text	NO
29:29	Cell Value	=TODAY()	text	NO
29:29	Dates Occurring	this week	text	NO
29:29	Cell Value	between TODAY() and TODAY()+2	text	NO
B30:G32	Cell Value	=TODAY()	text	NO
B30:G32	Dates Occurring	this week	text	NO
B30:G32	Cell Value	between TODAY() and TODAY()+2	text	NO
E33:J33,D33:D34	Cell Value	=TODAY()	text	NO
E33:J33,D33:D34	Dates Occurring	this week	text	NO
E33:J33,D33:D34	Cell Value	between TODAY() and TODAY()+2	text	NO
Q1:XFD1,A37,A33:C34,K33:N33,K32:M32,A38:N1048576,C37:N37,A35:N36,L34:N34,Q2:AC27,AE2:XFD27,A1:N27,A30:A32,H30:N31,AE30:XFD1048576,Q30:AC1048576	Cell Value	=TODAY()	text	NO
Q1:XFD1,A37,A33:C34,K33:N33,K32:M32,A38:N1048576,C37:N37,A35:N36,L34:N34,Q2:AC27,AE2:XFD27,A1:N27,A30:A32,H30:N31,AE30:XFD1048576,Q30:AC1048576	Dates Occurring	this week	text	NO
Q1:XFD1,A37,A33:C34,K33:N33,K32:M32,A38:N1048576,C37:N37,A35:N36,L34:N34,Q2:AC27,AE2:XFD27,A1:N27,A30:A32,H30:N31,AE30:XFD1048576,Q30:AC1048576	Cell Value	between TODAY() and TODAY()+2	text	NO

 

[SanjayGMusafir]

You can a helper column after Column B to change year as below. I have changed dates to number to create uniformity among different ways of writing dates.

You check formula in C2, if that works for you.

Book1
	B	C
1	Admission	Year Changed
2	43,689	44,785
3	44,701	44,701
4	44,749	44,749
5	38,800	44,644
6	42,856	44,682
7	44,386	44,751
8	44,616	44,616
9	42,958	44,784
10	43,842	44,573
11	44,836	44,836
12	44,341	44,706
13	42,838	44,664
14	39,823	44,571
15	43,403	44,864
16	43,954	44,684
17	41,610	44,897
18	43,144	44,605
19	41,886	44,808
20	44,559	44,924
21	42,412	44,604
22	44,700	44,700
23	44,608	44,608
24	43,846	44,577
25	40,415	44,798
26	44,761	44,761
27	44,736	44,736
28	44,689	44,689
Sheet1

Cell Formulas
Range	Formula
C2:C28	C2	=DATE(2022,MONTH(B2),DAY(B2))

Book1
	B	C
1	Admission	Year Changed
2	8/12/19	8/12/22
3	5/20/22	5/20/22
4	7/7/22	7/7/22
5	3/24/06	3/24/22
6	5/1/17	5/1/22
7	7/9/21	7/9/22
8	2/24/22	2/24/22
9	8/11/17	8/11/22
10	1/12/20	1/12/22
11	10/2/22	10/2/22
12	5/25/21	5/25/22
13	4/13/17	4/13/22
14	1/10/09	1/10/22
15	10/30/18	10/30/22
16	5/3/20	5/3/22
17	12/2/13	12/2/22
18	2/13/18	2/13/22
19	9/4/14	9/4/22
20	12/29/21	12/29/22
21	2/12/16	2/12/22
22	5/19/22	5/19/22
23	2/16/22	2/16/22
24	1/16/20	1/16/22
25	8/25/10	8/25/22
26	7/19/22	7/19/22
27	6/24/22	6/24/22
28	5/8/22	5/8/22
Sheet1

Cell Formulas
Range	Formula
C2:C28	C2	=DATE(2022,MONTH(B2),DAY(B2))

 

[CheepBeerSux]

Thank you for your help SanJay! That works just fine for me.