String as date

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a 6 character string of numbers in a cell which I would like to
treat as a date, whereas I can add 1 to it in VBA and it would display
as the following day, and then assign that value to a variable. For
instance, 033109 would turn into 040109 by adding 1 to it in a
procedure. Is this possible to do in a macro?

Thanks...
 
Look at this:

Sub aaa()
DateString = Range("A1").Value
MyMonth = Left(DateString, 2)
MyDay = Mid(DateString, 3, 2)
MyYear = Right(DateString, 2)

MyDate = MyMonth & "/" & MyDay & "/" & MyYear
MyDate = DateAdd("d", 1, MyDate)
DateString = "'" & Left(MyDate, 2) & Mid(MyDate, 4, 2) & Right(MyDate,
2)
Range("A1") = DateString
End Sub

Regards,
Per
 
hi
after this line.....
MyDate = DateAdd("d", 1, MyDate)
Click to expand...
add this line
MyDate= Format(MyDate, "mm,dd,yy")

the unexpected results (i think) is from a DateAdd format change.
the line i added will put the format back to the original so that the rest
of the code can put in back on the sheet the way it came off.

Regards
FSt1
 
Hello,

Here is a function which works if called via worksheet or from within
VBA:
Function addone(s As String) As String
'Adds one day to date sring "mmddyy".
addone = Format(DateSerial(Right(s, 2), _
Left(s, 2), Mid(s, 3, 2) + 1), _
"MMDDYY")
End Function

Sub test()
Debug.Print "Day after tomorrow: " & _
addone("040709") 'Works
Debug.Print "Day after 31-Dec-2029: " & _
addone("123129") 'Beware 010130 will be 1-Jan-1930
End Sub

Please note that 123129 will be interpreted by Excel as 31-Dec-2029
but 010130 as 1-Jan-1930!

You might want to use a 4 char year presentation MMDDYYYY. Then change
the program to:

Function addone(s As String) As String
'Adds one day to date sring "mmddyyyy".
addone = Format(DateSerial(Right(s, 4), _
Left(s, 2), Mid(s, 3, 2) + 1), _
"MMDDYYYY")
End Function

Regards,
Bernd
 
Back
Top