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

randaza1

New Member
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1PayeeDue Date Amount Pay On
2Bill 13/15/2021$ 15.002/15/2021
3Bill 22/26/2021$ 75.002/1/2021
4Bill 34/15/2021$ 28.503/15/2021
5Bill 43/16/2021$ 17.502/28/2021
6Bill 53/16/2021$ 200.002/28/2021
7Bill 63/16/2021$ 251.452/28/2021
8Bill 72/28/2021$ 700.002/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
ABCD
1Current Month3
2PayeeDue Date Amount Pay On
3Bill 13/15/2021$ 15.002/15/2021
4Bill 22/26/2021$ 75.002/1/2021
5Bill 34/15/2021$ 28.503/15/2021
6Bill 43/16/2021$ 17.502/28/2021
7Bill 53/16/2021$ 200.002/28/2021
8Bill 63/16/2021$ 251.452/28/2021
9Bill 72/28/2021$ 700.002/4/2021
Monthly Bills
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
You dont see where im going from the reply. You didnt try it then?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,656
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
consider either of the following
T202103a.xlsm
ABC
1Month3
2Year2021
3PayeeDue Date Amount
4Bill 115-Mar-2115.00
5Bill 416-Mar-2117.50
6Bill 516-Mar-21200.00
7Bill 616-Mar-21251.45
8
9MonthMar
10Year2021
11PayeeDue Date Amount
12Bill 115-Mar-2115.00
13Bill 416-Mar-2117.50
14Bill 516-Mar-21200.00
15Bill 616-Mar-21251.45
1d
Cell Formulas
RangeFormula
B4B4=DATE($B$2,$B$1,15)
B5:B7B5=DATE($B$2,$B$1,16)
B12B12=DATE($B$10,MONTH(1&$B$9),15)
B13:B15B13=DATE($B$10,MONTH(1&$B$9),16)
 

randaza1

New Member
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Sorry the way you wrote it back was confusing.
But, what happen to bill 2 and 3 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,252
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top