Excel 365 - saving the same style spreadsheet each month, but changing only the MM of the due date

[randaza1]

Hello,
Every month I use the "same" spreadsheet to pay bills. When I am setting up lets say bills that are due in Feb and March I would open the previous month, and then immediatly save it with a new file name. In this case Feb - March, then March - April, and so on.

With that being said, as you see in the chart below, I will begin another new file name for March to April.
All I really want to do is have the month change only in the B column. As you know bill due dates seldom change.
So for example the Due Date of 3/15/2021 for March to April will actually need to change to 4/15/2021.
Is there a way to do this?

test bills.xls
	A	B	C	D
1	Payee	Due Date	Amount	Pay On
2	Bill 1	3/15/2021	$ 15.00	2/15/2021
3	Bill 2	2/26/2021	$ 75.00	2/1/2021
4	Bill 3	4/15/2021	$ 28.50	3/15/2021
5	Bill 4	3/16/2021	$ 17.50	2/28/2021
6	Bill 5	3/16/2021	$ 200.00	2/28/2021
7	Bill 6	3/16/2021	$ 251.45	2/28/2021
8	Bill 7	2/28/2021	$ 700.00	2/4/2021
Monthly Bills

My thought process to this would be maybe something like this, were I just add a new row on top and add "Current Month" and then in B1 type in the numeral month: 1 = Jan, 2=Feb, 3=March, etc.. and requesting the spreadsheet to change b3 from 3/15/2021 to 4/15/2021
change b4 from 2/26/2021 to 3/26/21
and so on... your expertise will be greatly appreciated.
-Tony

test bills.xls
	A	B	C	D
1	Current Month	3
2	Payee	Due Date	Amount	Pay On
3	Bill 1	3/15/2021	$ 15.00	2/15/2021
4	Bill 2	2/26/2021	$ 75.00	2/1/2021
5	Bill 3	4/15/2021	$ 28.50	3/15/2021
6	Bill 4	3/16/2021	$ 17.50	2/28/2021
7	Bill 5	3/16/2021	$ 200.00	2/28/2021
8	Bill 6	3/16/2021	$ 251.45	2/28/2021
9	Bill 7	2/28/2021	$ 700.00	2/4/2021
Monthly Bills

 

[steve the fish]

Using your example with a bill due on the 15th of the month you can use the DATE function. You may want to create a current year cell also. eg

=DATE(D1,B1,15)

With the month you want in B1 and the year in D1. Then you just need to change B1 and D1 and your dates change.

 

[randaza1]

Using your example with a bill due on the 15th of the month you can use the DATE function. You may want to create a current year cell also. eg

=DATE(D1,B1,15)

With the month you want in B1 and the year in D1. Then you just need to change B1 and D1 and your dates change.

I kind of see where your going, but I do not want to split the mm/dd/yyyy into different cells. But thanks for your input.

Tony

 

[steve the fish]

You dont see where im going from the reply. You didnt try it then?

 

[Dave Patton]

consider either of the following

T202103a.xlsm
	A	B	C
1	Month	3
2	Year	2021
3	Payee	Due Date	Amount
4	Bill 1	15-Mar-21	15.00
5	Bill 4	16-Mar-21	17.50
6	Bill 5	16-Mar-21	200.00
7	Bill 6	16-Mar-21	251.45
8
9	Month	Mar
10	Year	2021
11	Payee	Due Date	Amount
12	Bill 1	15-Mar-21	15.00
13	Bill 4	16-Mar-21	17.50
14	Bill 5	16-Mar-21	200.00
15	Bill 6	16-Mar-21	251.45
1d

Cell Formulas
Range	Formula
B4	B4	=DATE($B$2,$B$1,15)
B5:B7	B5	=DATE($B$2,$B$1,16)
B12	B12	=DATE($B$10,MONTH(1&$B$9),15)
B13:B15	B13	=DATE($B$10,MONTH(1&$B$9),16)

 

[randaza1]

Sorry the way you wrote it back was confusing.
But, what happen to bill 2 and 3 ?