Change only the MM in MM/DD/YYYY for due dates for household Creditor bills

randaza1

New Member
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Hello all
Each month I sit down and work on my excel spread sheet for house hold bills.
I always save the sheets (I am just ocd I guess) each time I save the file as such:
January 2021 - February 2021
February 2021 - March 2021 .... and on and on.
When a new month of bills starts I simply open the last spreadsheet "Save As" and simply change the file name. This makes it consist.
But I am trying to figure out how to change JUST the month in each spreadsheet.
Here is an example:
1612479065391.png

Lets say these are all paid, I will now move to the next month, thus
B2 would need to change to 3/27/2021
B3 would need to change to 3/25/2021
B4 would need to change to 3/15/2021
B5 would need to change to 4/27/2021
B6 would need to change to 4/27/2021
B7 would need to change to 4/27/2021
I have tried to just add 30 days, but the problem is it wont be consistent as Feb may have 28 to 29 days, other months may have 30 or 31 so it will begin skewing the correct results.
I am not apposed to inserting a new column to the LEFT of A if that needs to be done, but I just cant figure it out. Also, I hear VBA? is a possibility, but I have never used VBA before.

Thanks
Tony
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

randaza1

New Member
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
removed due to error in replying to JTAKW
 

randaza1

New Member
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Hi,

Would the EDATE function help?

Book3.xlsx
BC
12/27/20213/27/2021
22/25/20213/25/2021
31/31/20212/28/2021
42/15/20213/15/2021
52/28/20213/28/2021
63/1/20214/1/2021
73/5/20214/5/2021
83/20/20214/20/2021
93/31/20214/30/2021
Sheet733
Cell Formulas
RangeFormula
C1:C9C1=EDATE(B1,1)
jtakw -
You are definitely on the right path as far as the month changing and the date remaining. I even like that the 1-31-2021's new date kicks to the 28.
But in this scenario, I would manually need to change column B to what is showing in Column C when the next month batch of bills comes around. ( Does that make sense?)

I was thinking (when I mentioned column) I could put the numeral month (Jan 1, Feb 2, Mar 3, April 4 ..... Dec 12) and the formula would change to the next due MM.
Like maybe this:

1612482122664.png


Where B2 would be the numeric representation of the current month I am in, and the due date would simply change to the next month. (remember I always open my previous month and do a SAVE AS with a new file name.
That's where I heard that VBA may work, but I have no clue how to write it. I do like the =EDATE(B1,1) formula, but I don't think it will work for this situation.

Tony
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,804

ADVERTISEMENT

Step 1: i would add the first day of the current month (eg 03/01/2021) at the top of the dates column and make it into a named range (eg CurrMonth).

Step 2: change your due dates to a formula like this: =Datevalue(Month(CurrMonth)&"/01/27"). Obviously, the first time you set this up will take a little manual input such as changing the actual day each month for each subsequent bill. I know, there is a little extra work here to set this up but it makes the monthly process easier and quicker in the long run.

Step 2a: change the formula above to =Datevalue(Month(CurrMonth)+1&"/01/27") when you get to your bills to be paid next month.

Step 3: if you want to go even further have a name for these files that is standard and where only the month changes in the name. something like "Bills 20210301. Using the yyyymmdd format in the title will make sorting them easier and will return them in month order. From this, you can insert a formula on your bills page that strips the date part out of the title so that when you save as the new month, the formula picks up the new date. then, your named range (step 1 above) references the value returned by this formula. Hey presto, simply by saving your new file, you have already updated it for the coming month.
 

randaza1

New Member
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Step 1: i would add the first day of the current month (eg 03/01/2021) at the top of the dates column and make it into a named range (eg CurrMonth).

Step 2: change your due dates to a formula like this: =Datevalue(Month(CurrMonth)&"/01/27"). Obviously, the first time you set this up will take a little manual input such as changing the actual day each month for each subsequent bill. I know, there is a little extra work here to set this up but it makes the monthly process easier and quicker in the long run.

Step 2a: change the formula above to =Datevalue(Month(CurrMonth)+1&"/01/27") when you get to your bills to be paid next month.

Step 3: if you want to go even further have a name for these files that is standard and where only the month changes in the name. something like "Bills 20210301. Using the yyyymmdd format in the title will make sorting them easier and will return them in month order. From this, you can insert a formula on your bills page that strips the date part out of the title so that when you save as the new month, the formula picks up the new date. then, your named range (step 1 above) references the value returned by this formula. Hey presto, simply by saving your new file, you have already updated it for the coming month.
AJM-
Thank you for your input, but i must be missing something, each time i put the formula in i get the #NAME? ergo, its not working :(

T
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,804
Hi randazza. sorry for the late response. the #Name error is triggered if the name (in the example above, CurrMonth) is not defined in your spreadsheet. Did you set up the named range?
1612832310258.png


1612832481141.png


1612832540722.png
 

randaza1

New Member
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Thanks everyone, I will need to work on a different method for now
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,242
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