ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Date + one year (https://www.excelbanter.com/excel-programming/348148-date-one-year.html)

Oldjay[_2_]
Date + one year
 
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

saziz[_34_]
Date + one year
 

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


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=493623

Chip Pearson
Date + one year
 
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



"Oldjay" wrote in message
...
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

Steve
Date + one year
 
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


"saziz" wrote in
message ...

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


--
saziz
------------------------------------------------------------------------
saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=493623

RB Smissaert
Date + one year
 
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


"Oldjay" wrote in message
...
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

saziz[_36_]
Date + one year
 

You are right Steve. It will fail in a Leap year
Thank you for pointing that out.
Sye

--
sazi
-----------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...nfo&userid=635
View this thread: http://www.excelforum.com/showthread.php?threadid=49362

Oldjay
Date + one year
 
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

"RB Smissaert" wrote:

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


"Oldjay" wrote in message
...
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

Norman Jones
Date + one year
 
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.

--
---
Regards,
Norman



"Oldjay" wrote in message
...
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

"RB Smissaert" wrote:

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


"Oldjay" wrote in message
...
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

Oldjay
Date + one year
 
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

"Norman Jones" wrote:

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.

--
---
Regards,
Norman



"Oldjay" wrote in message
...
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

"RB Smissaert" wrote:

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


"Oldjay" wrote in message
...
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

Norman Jones
Date + one year
 

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:

Set WB = ThisWorkbook

Otherwise, try:

Set WB = Workbooks("RIDGE MANOR " _
& "COMMUNITY UNITED METHODIST " _
& "CHURCH MEMBERSHIP LISTV1.XLS")


---
Regards,
Norman


"Oldjay" wrote in message
...
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

"Norman Jones" wrote:

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.

--
---
Regards,
Norman

Oldjay
Date + one year
 
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

"Norman Jones" wrote:


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:

Set WB = ThisWorkbook

Otherwise, try:

Set WB = Workbooks("RIDGE MANOR " _
& "COMMUNITY UNITED METHODIST " _
& "CHURCH MEMBERSHIP LISTV1.XLS")


---
Regards,
Norman


"Oldjay" wrote in message
...
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

"Norman Jones" wrote:

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.

--
---
Regards,
Norman

Norman Jones
Date + one year
 
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.


---
Regards,
Norman



"Oldjay" wrote in message
...
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

"Norman Jones" wrote:


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:

Set WB = ThisWorkbook

Otherwise, try:

Set WB = Workbooks("RIDGE MANOR " _
& "COMMUNITY UNITED METHODIST " _
& "CHURCH MEMBERSHIP LISTV1.XLS")


---
Regards,
Norman

Oldjay
Date + one year
 
Everything works peachy. Thanks again
It amazes me that you guys can help so many people in such a short response
time

oldjay

"Norman Jones" wrote:

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.


---
Regards,
Norman



"Oldjay" wrote in message
...
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

"Norman Jones" wrote:


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:

Set WB = ThisWorkbook

Otherwise, try:

Set WB = Workbooks("RIDGE MANOR " _
& "COMMUNITY UNITED METHODIST " _
& "CHURCH MEMBERSHIP LISTV1.XLS")


---
Regards,
Norman


All times are GMT +1. The time now is 05:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com