Printable View
Hi guys. I have some dates in random order in a ss. Is there a way to change
the dates to a new date? Example. 16 July 06 to 16 Aug 06. The 16 July 06
date will be in a number of cells. To many to change all the dates one at a time.:banghead:
Again thanks for your time and help
Max
Will not Find and Replace do that??
lenze
say cell A1 has 16 July 2006, put this formula in B1 (or wherever):
=date(year(A1), month(A1)+1, day(A1))
make sure the cell with that formula is formatted for a date
Hi Compariniaa,
What about Decmber?
For a VBA solution, based on your idea
[VBA]Sub UpDate()
Dim Dy As Long, Mh As Long, Yr As Long
For Each cel In Selection
Dy = Day(cel)
Mh = (Month(cel) Mod 12) + 1
If (Month(cel) Mod 12) = 0 Then
Yr = Year(cel) + 1
Else
Yr = Year(cel)
End If
cel.Value = DateValue(Dy & "/" & Mh & "/" & Yr)
Next
End Sub[/VBA]
mdmackillop, you brought up a very good point, one which I had not considered. But I thought I'd try it out, and Excel automatically brings it back to January
what does the line
[vba]Mh = (Month(cel) Mod 12) +1[/vba] do? I guess what I mean is, what's the "Mod 12" part?
You're right. I didn't try it, and I couldn't see how the year would increment, but it does!
X Mod Y gives the remainder when X is divided by Y, so 12 goes to 0 +1 (January).
Sorry if I come across as dense, but I don't fully grasp the concept/use of X Mod Y. Could I bother you for a little more of an explanation?
Mod Operator Example
This example uses the Mod operator to divide two numbers and return only the remainder. If either number is a floating-point number, it is first rounded to an integer.
[vba]
Dim MyResult
MyResult = 10 Mod 5 ' Returns 0.
MyResult = 10 Mod 3 ' Returns 1.
MyResult = 12 Mod 4.3 ' Returns 0.
MyResult = 12.6 Mod 5 ' Returns 3.
[/vba]
In this case, using your methodology did not produce the correct result in VBA, so I had to identify December and reset the Month to 1 and increment the year. 12 Mod 12 = 0 so this became my test.
Other uses: Suppose you want to enter the numbers 1 to 7 in a column repeatedly for 5000 rows[vba]For i = 0 To 5000
Cells(i + 1, 1) = (i Mod 7) + 1
Next
[/vba]
ahh..ok, now I understand. Thanks MD