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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
When using functions for dates, such as DateAdd, the dates must be Date data types. If you have not already reviewed the spec sheet for the DateAdd function, here is a link to it.
DateAdd function (Visual Basic for Applications) | Microsoft Docs
Working with dates in Excel can be very unsettling at times because of their special rules and automatic formatting. the type mismatch indicates that your column A reference may not be seen as a date by VBA. If your "Totals" text is locate in column A The try changing this;
VBA Code:
For i = 6 To lRow
To this
VBA Code:
For i = 6 To lRow - 1
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,679
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If the following works for you, you can make column B values and then Copy and paste column C to A

You would then just change the Year and Month as required.

T202101b.xlsm
ABC
1Year 2021
2Month3
3
4
5Date
62-Jan-2122-Mar-21
73-Jan-2133-Mar-21
85-Jan-2155-Mar-21
95-Jan-2155-Mar-21
105-Jan-2155-Mar-21
11
60
Cell Formulas
RangeFormula
B6:B10B6=DAY(A6)
C6:C10C6=DATE(B$1,B$2,B6)
 

Kingchaos64

New Member
Joined
Jan 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
If the following works for you, you can make column B values and then Copy and paste column C to A

You would then just change the Year and Month as required.

T202101b.xlsm
ABC
1Year 2021
2Month3
3
4
5Date
62-Jan-2122-Mar-21
73-Jan-2133-Mar-21
85-Jan-2155-Mar-21
95-Jan-2155-Mar-21
105-Jan-2155-Mar-21
11
60
Cell Formulas
RangeFormula
B6:B10B6=DAY(A6)
C6:C10C6=DATE(B$1,B$2,B6)
Thanks Dave I think this would be to much as this worksheet template will be sent to multiple locations with multiple people. It needs to be super easy and straightforward to change the month. Since most people using this barely know how to turn on a computer lol.
 

Dave Patton

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

ADVERTISEMENT

They would just have to specify the month number or we could adjust the formula to work for say Jun or June
 

Dave Patton

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

Kingchaos64

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

ADVERTISEMENT

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
Oh ok that might work :). I'll try it when I get to work thank you
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Another way to avoid the type mismatch with dates is
VBA Code:
For i = 6 To lRow
    If IsDate(.Cells(i, 1).Value) Then
        .Range("A" & i).Value = DateAdd("m", 1, .Range("A" & i).Value)
    End If
Next i
Use an IF...Then statement to eliminate non date values.
 

Kingchaos64

New Member
Joined
Jan 15, 2021
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Another way to avoid the type mismatch with dates is
VBA Code:
For i = 6 To lRow
    If IsDate(.Cells(i, 1).Value) Then
        .Range("A" & i).Value = DateAdd("m", 1, .Range("A" & i).Value)
    End If
Next i
Use an IF...Then statement to eliminate non date values.
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,357
Messages
5,641,634
Members
417,229
Latest member
breekellyvicki

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