O
Oldjay
I have a long column of birthdays formulated 8/12/05
I want to change all the dates to next year i.e. 8/12/06
oldjay
I want to change all the dates to next year i.e. 8/12/06
oldjay
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
saziz
considering your date 8/12/05 is written in a1
in b1 type this formula ---> =a1+365
you will get 8/12/06
copy it down until where you want
Syed
in b1 type this formula ---> =a1+365
you will get 8/12/06
copy it down until where you want
Syed
C
Chip Pearson
In an empty column adjacent to your dates, use the following
formula:
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
Copy this formula down as far as you need to go. Select the cells
with this formula, right-click on the right edge, and drag over
the original data. When you release the right-button, choose
"Copy Here As Values Only"
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
formula:
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
Copy this formula down as far as you need to go. Select the cells
with this formula, right-click on the right edge, and drag over
the original data. When you release the right-button, choose
"Copy Here As Values Only"
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
S
Steve
YourDate+365 fails in a leap year.
You might try the Edate() function.
=Edate(ReferenceToYourDate,12)
If Edate() doesn't work for you, read the reference in Help.
If you want to write a macro, DateSerial is the key:
Function AddAYear(Yourdate As Date) As Date
AddAYear = DateSerial(Year(Yourdate) + 1, Month(Yourdate), Day(Yourdate))
End Function
Steve
You might try the Edate() function.
=Edate(ReferenceToYourDate,12)
If Edate() doesn't work for you, read the reference in Help.
If you want to write a macro, DateSerial is the key:
Function AddAYear(Yourdate As Date) As Date
AddAYear = DateSerial(Year(Yourdate) + 1, Month(Yourdate), Day(Yourdate))
End Function
Steve
R
RB Smissaert
This will do it in VBA:
Function AddOneYear(daDate As Date) As Date
AddOneYear = DateSerial(Year(daDate) + 1, Month(daDate), Day(daDate))
End Function
Sub test()
Dim i As Long
For i = 1 To 100
Cells(i, 2) = Format(AddOneYear(Cells(i, 1)), "dd/mm/yy")
Next
End Sub
RBS
Function AddOneYear(daDate As Date) As Date
AddOneYear = DateSerial(Year(daDate) + 1, Month(daDate), Day(daDate))
End Function
Sub test()
Dim i As Long
For i = 1 To 100
Cells(i, 2) = Format(AddOneYear(Cells(i, 1)), "dd/mm/yy")
Next
End Sub
RBS
S
saziz
You are right Steve. It will fail in a Leap year
Thank you for pointing that out.
Sye
Thank you for pointing that out.
Sye
O
Oldjay
A little more detail. The birthdays are in the 4th column of a 8 column list.
I would like the macro to change them in place
oldjay
I would like the macro to change them in place
oldjay
N
Norman Jones
Hi Oldjay,
Try:
'=============>>
Sub AddOneYear()
Dim rng As Range
Dim rcell As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim LRow As Long
Dim CalcMode As Long
Const col As String = "D" '<<===== CHANGE
Set WB = ThisWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Birthday") '<<===== CHANGE
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
LRow = SH.Cells(Rows.Count, col).End(xlUp)
Set rng = SH.Range(col & 2).Resize(LRow - 1)
For Each rcell In rng.Cells
With rcell
If IsDate(.Value) Then
.Value = DateSerial(Year(.Value) + 1, _
Month(.Value), Day(.Value))
End If
End With
Next rcell
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=============
Change the sheet and workbook names to suit.
As written, the macro will add a year to all dates in column D and allows
for headers in row 1.
Try:
'=============>>
Sub AddOneYear()
Dim rng As Range
Dim rcell As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim LRow As Long
Dim CalcMode As Long
Const col As String = "D" '<<===== CHANGE
Set WB = ThisWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Birthday") '<<===== CHANGE
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
LRow = SH.Cells(Rows.Count, col).End(xlUp)
Set rng = SH.Range(col & 2).Resize(LRow - 1)
For Each rcell In rng.Cells
With rcell
If IsDate(.Value) Then
.Value = DateSerial(Year(.Value) + 1, _
Month(.Value), Day(.Value))
End If
End With
Next rcell
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=============
Change the sheet and workbook names to suit.
As written, the macro will add a year to all dates in column D and allows
for headers in row 1.
O
Oldjay
When I put in the name of the workbook I get an error saying "Expected: End
of statement" with Manor highlighted
Set WB = RIDGE MANOR COMMUNITY UNITED METHODIST CHURCH MEMBERSHIP LISTV1.XLS
'<<===== CHANGE
oldjay
of statement" with Manor highlighted
Set WB = RIDGE MANOR COMMUNITY UNITED METHODIST CHURCH MEMBERSHIP LISTV1.XLS
'<<===== CHANGE
oldjay
N
Norman Jones
Hi Oldjay,
If the code is to be housed in the RIDGE MANOR COMMUNITY UNITED METHODIST
CHURCH MEMBERSHIP LISTV1.XLS workbook, you can leave the original:
Otherwise, try:
Set WB = Workbooks("RIDGE MANOR " _
& "COMMUNITY UNITED METHODIST " _
& "CHURCH MEMBERSHIP LISTV1.XLS")
If the code is to be housed in the RIDGE MANOR COMMUNITY UNITED METHODIST
CHURCH MEMBERSHIP LISTV1.XLS workbook, you can leave the original:
Otherwise, try:
Set WB = Workbooks("RIDGE MANOR " _
& "COMMUNITY UNITED METHODIST " _
& "CHURCH MEMBERSHIP LISTV1.XLS")
O
Oldjay
Norman,
The code works great. Thanks
I have another column with Anniversaries that I also want to convert. I
tried to just change the Const col As String = "J" to Const col As String =
"K" at the botton of the sub and re-copy the code below This doesn't work. It
gives the error"Duplicate declaration in current scope"
oldjay
The code works great. Thanks
I have another column with Anniversaries that I also want to convert. I
tried to just change the Const col As String = "J" to Const col As String =
"K" at the botton of the sub and re-copy the code below This doesn't work. It
gives the error"Duplicate declaration in current scope"
oldjay
N
Norman Jones
Hi Oldjay,
Try replacing your code with:
'=============>>
Sub ChangeYear()
Call AddOneYear("J")
Call AddOneYear("K")
End Sub
'<<=============
'=============>>
Sub AddOneYear(col As String)
Dim rng As Range
Dim rcell As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim LRow As Long
Dim CalcMode As Long
Set WB = ThisWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Birthday") '<<===== CHANGE
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
LRow = SH.Cells(Rows.Count, col).End(xlUp)
Set rng = SH.Range(col & 2).Resize(LRow - 1)
For Each rcell In rng.Cells
With rcell
If IsDate(.Value) Then
.Value = DateSerial(Year(.Value) + 1, _
Month(.Value), Day(.Value))
End If
End With
Next rcell
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=============
Now instead of running the AddOneYear procedure directly, run the ChangeYear
procedure.
In the ChangeYear sub, change J and K to the date columns of interest.
Try replacing your code with:
'=============>>
Sub ChangeYear()
Call AddOneYear("J")
Call AddOneYear("K")
End Sub
'<<=============
'=============>>
Sub AddOneYear(col As String)
Dim rng As Range
Dim rcell As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim LRow As Long
Dim CalcMode As Long
Set WB = ThisWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Birthday") '<<===== CHANGE
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
LRow = SH.Cells(Rows.Count, col).End(xlUp)
Set rng = SH.Range(col & 2).Resize(LRow - 1)
For Each rcell In rng.Cells
With rcell
If IsDate(.Value) Then
.Value = DateSerial(Year(.Value) + 1, _
Month(.Value), Day(.Value))
End If
End With
Next rcell
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<=============
Now instead of running the AddOneYear procedure directly, run the ChangeYear
procedure.
In the ChangeYear sub, change J and K to the date columns of interest.
O
Oldjay
Everything works peachy. Thanks again
It amazes me that you guys can help so many people in such a short response
time
oldjay
It amazes me that you guys can help so many people in such a short response
time
oldjay
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.