String as 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...
 
P

Per Jessen

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
 
F

FSt1

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
 
B

Bernd P

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
 
Top