Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date(Year,Month,Day) not returnign correct date | Excel Discussion (Misc queries) | |||
Determine year over year date for comparison | Excel Worksheet Functions | |||
Sorting a date by month, date and then year. | Excel Discussion (Misc queries) | |||
Year-to-date year to date formula | Excel Worksheet Functions | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) |