Advance a column of dates by a month

Kingchaos64

New Member
Joined
Jan 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
I have a table with A5 as my date header and A6:A500 that have Januarys dates in them. I have all the cells formatted to Date (01/15/21). Multiple cells in the A Column have the same date. At the end of each month the workbook is to be saved and a new one is to be started. In the new sheet the days are going to be the same it's just the month that need to change. I need a formula or VBA code that will change the all the dates and advance them one month. I have this VBA


Dim ws As Worksheet
Dim lRow As Long, i As Long

'~~> Change this to the relevant sheet
Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row

For i = 6 To lRow
.Range("A" & i).Value = DateAdd("m", 1, .Range("A" & i).Value)
Next i
End With
End Sub

When I run this it will change the month but I get an error code 13 Type Mismatch. Also it doesn't account for the different days of the month 29th, 30th,31st it just leaves those dates and then advancing to March it doesn't do anything with them.
Any ideas.
Also my table has a total row. With word total in the A Column I don't think that's causing a problem but just want to give all the facts
 

Kingchaos64

New Member
Joined
Jan 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Either of

T202101b.xlsm
ABC
1Year 2021
2Month4
3
4
5Date
62-Apr-212=DATE(B$1,B$2,B6)
73-Apr-213
85-Apr-215
95-Apr-215
105-Apr-215
60
Cell Formulas
RangeFormula
C6C6=gf(A6)
A6:A10A6=DATE(B$1,B$2,B6)


T202101b.xlsm
ABC
1Year 2021
2MonthJune
3
4
5Date
62-Jun-212=DATE(B$1,nMonth,B6)
73-Jan-213
85-Jan-215
95-Jan-215
105-Jan-215
4d
Cell Formulas
RangeFormula
C6C6=gf(A6)
A6:A10A6=DATE(B$1,nMonth,B6)


The named formula is nMonth =MONTH(1&LEFT('4d'!B1048573,3)) It is assigned to the sheet named 4d
Dave, I can't get this to work. I'm sure I'm just putting one of the formulas in wrong space. Where does the =MONTH (1..... go?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Thank you very much. The -1 in your previous comment worked such a simple fix. I might change it to this code just to avoid any issues in the future
You're welcome, If this satisfies your issue, please mark the post you consider best soloution.
Regards, JLG
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,625
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T202101b.xlsm
AB
1Year 2021
2MonthJan
3
4
5Date
62-Jan-212
73-Jan-213
85-Jan-215
95-Jan-215
105-Jan-215
11
4d
Cell Formulas
RangeFormula
A6A6=DATE(B$1,MONTH(1&LEFT($B$2,3)),B6)
A7:A10A7=DATE(B$1,nMonth,B7)


Dave, I can't get this to work. I'm sure I'm just putting one of the formulas in wrong space.
Where does the =MONTH (1..... go?

You do not have to use the named range but I wanted to make the formula look as simple as possible.
Use Formulas Name Manager new name nMonth = =MONTH(1&LEFT('4d'!$B$2,3))
The name can be for the workbook or a particular sheet; my example is on sheet 4d.
The formula works for full month names or short names Jan or January
 

Kingchaos64

New Member
Joined
Jan 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
You would think there would be a Vba code out there that would add one month to dates with days 1 through 28.
Then with days 29 and 30 only update if a certain cell has January, March, April, May, June, July, August, September, October, November, December. Then day 31 would only update if that certain cell had January, March, May, July, August, October, December
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,625
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

T202101b.xlsm
AB
142-Jan-212-Feb-21
153-Jan-213-Feb-21
165-Jan-215-Feb-21
175-Jan-215-Feb-21
185-Jan-215-Feb-21
1931-Jan-2128-Feb-21
20
4d
Cell Formulas
RangeFormula
B14:B19B14=EDATE(A14,1)


What do you want to do with Jan 29, 30, and 31 for Feb. I did not amend the formula to show blank for dates > 28 for most February dates
With this approach
- convert B14:B19 to Values with VBA or functions
- cut the range and paste it to A2
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You would think there would be a Vba code out there that would add one month to dates with days 1 through 28.
Then with days 29 and 30 only update if a certain cell has January, March, April, May, June, July, August, September, October, November, December. Then day 31 would only update if that certain cell had January, March, May, July, August, October, December
The DateAdd function in VBA is supposed allow for the variation in the number of days per month.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,625
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T202101b.xlsm
AB
142-Jan-212-Feb-21
153-Jan-213-Feb-21
165-Jan-215-Feb-21
175-Jan-215-Feb-21
185-Jan-215-Feb-21
1931-Jan-21 
4d
Cell Formulas
RangeFormula
B14:B19B14=IF(DAY(A14)>DAY(EDATE(A14,1)),"",EDATE(A14,1))


I didn't check the formula for later months.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,126,916
Messages
5,621,602
Members
415,847
Latest member
AlpinoHirsch

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