Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Year in formatted Date
I would like to change to year portion from 2009 to 2010 in multiple cells.
The cells are formatted as Date with *3/14/2001 selected for k = 1 to 5 Sheets("Sheet1").Cells(k,2)=Date if Date.year = 2009 then Date.year=2010 next k Date.year is incorrect. How do I change and save the date? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Year in formatted Date
Sub dural()
Dim d As Date, s As String For i = 1 To 30 d = Cells(i, 1).Value m = Month(d) dt = Day(d) s = m & "/" & dt & "/" & "2010" Cells(i, 1).Value = DateValue(s) Next End Sub -- Gary''s Student - gsnu200909 "Philosophaie" wrote: I would like to change to year portion from 2009 to 2010 in multiple cells. The cells are formatted as Date with *3/14/2001 selected for k = 1 to 5 Sheets("Sheet1").Cells(k,2)=Date if Date.year = 2009 then Date.year=2010 next k Date.year is incorrect. How do I change and save the date? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Year in formatted Date
If you're really populating those cells with the date a year from today...
Dim NewDate as date newdate = dateserial(year(date)+1,month(date),day(date)) with worksheets("sheet1").cells(1,2).resize(5,1) .numberformat = "mm/dd/yyyy" .value = newdate end with It'll work for most dates--not sure what you want to happen if you run this on Feb 29 of a leap year. Philosophaie wrote: I would like to change to year portion from 2009 to 2010 in multiple cells. The cells are formatted as Date with *3/14/2001 selected for k = 1 to 5 Sheets("Sheet1").Cells(k,2)=Date if Date.year = 2009 then Date.year=2010 next k Date.year is incorrect. How do I change and save the date? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Year in formatted Date
It is a little unclear from your code exactly what you are trying to do
(Date is a built in function in VB), but assuming you have a real date reference of some kind, just put it in where I show ThisYearsDate and the DateAdd function (as structured) will add one year to that date. I show the output being assigned to a variable named NextYearsDate, but you can output it anywhere you need to (back into a cell, in a MsgBox, etc.)... NextYearsDate = DateAdd("YYYY", 1, ThisYearsDate) -- Rick (MVP - Excel) "Philosophaie" wrote in message ... I would like to change to year portion from 2009 to 2010 in multiple cells. The cells are formatted as Date with *3/14/2001 selected for k = 1 to 5 Sheets("Sheet1").Cells(k,2)=Date if Date.year = 2009 then Date.year=2010 next k Date.year is incorrect. How do I change and save the date? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Year in formatted Date
I am wanting to read the year from a cell who is formatted as a date and has
a date in it. If the year is not 2010 then I want to change it to 2010. "Rick Rothstein" wrote: It is a little unclear from your code exactly what you are trying to do (Date is a built in function in VB), but assuming you have a real date reference of some kind, just put it in where I show ThisYearsDate and the DateAdd function (as structured) will add one year to that date. I show the output being assigned to a variable named NextYearsDate, but you can output it anywhere you need to (back into a cell, in a MsgBox, etc.)... NextYearsDate = DateAdd("YYYY", 1, ThisYearsDate) -- Rick (MVP - Excel) "Philosophaie" wrote in message ... I would like to change to year portion from 2009 to 2010 in multiple cells. The cells are formatted as Date with *3/14/2001 selected for k = 1 to 5 Sheets("Sheet1").Cells(k,2)=Date if Date.year = 2009 then Date.year=2010 next k Date.year is incorrect. How do I change and save the date? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the Year in formatted Date
Try it this way...
With Sheets("Sheet1") For K = 1 To 5 With .Cells(K, 2) .Value = DateSerial(2010, Month(.Value), Day(.Value)) .NumberFormat = "m/d/yyyy" End With Next End With -- Rick (MVP - Excel) "Philosophaie" wrote in message ... I would like to change to year portion from 2009 to 2010 in multiple cells. The cells are formatted as Date with *3/14/2001 selected for k = 1 to 5 Sheets("Sheet1").Cells(k,2)=Date if Date.year = 2009 then Date.year=2010 next k Date.year is incorrect. How do I change and save the date? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
column formatted to general - cells keep changing to date? | New Users to Excel | |||
changing date formatted as general number into date format | Excel Programming | |||
changing the year of a date | Excel Worksheet Functions | |||
how do i add 1 year to a date formatted cell? | Excel Worksheet Functions | |||
Changing the year only in a date | Excel Programming |