I have date in the format 06-Aug-03. The day in all the dates in the file has to be changed to 01.Is there a way to code this in VB to automatically check the date and have the day changed to 01 if the day is <> 01.
Thanks in advance
Regards
I have date in the format 06-Aug-03. The day in all the dates in the file has to be changed to 01.Is there a way to code this in VB to automatically check the date and have the day changed to 01 if the day is <> 01.
Thanks in advance
Regards
Re: Date
The issue may revolve around VBA "knowing" whether a cell has a date in it or some other number. (Excel stores dates as numbers with each integer representing a day since 1900 and teh fractional part the "h:m:s" part of a day.
If you can tell VBA which cells to convert then something like the following, which changes the date in cell A1 to the first of the same month and year, should work
You also can do it with a formula (in a different cell). To convert cell A1 use:
Note: I believe the Analysis ToolPack add-in needs to be loade to use the End of Month (EOMONTH) function.
Re: Date
Hi,
Since you gave no clue as to _where_ you want to look this is a simple loop on row one to 100, column 1. It will replace those pesky dates, however.
Sub ChngDate()
Dim v
Dim nv As Date
Dim r As Long, c As Long, i As Long
'Set row and column variables
r = 1
c = 1
'Do 100 times? Rows 1 to 100 ...
For i = 1 To 100
'Get value
v = Cells(r, c).Value
'If is valid date change to "01"
If IsDate(v) Then
v = "01" & Right(v, 8)
nv = v
Cells(r, c).Value = nv
End If
'Else check next row
r = r + 1
Next
End Sub
Display More
Cheers,
dr
Re: Date
Thanks a lot for the help
Regards
Geetha
Don’t have an account yet? Register yourself now and be a part of our community!