String as date
 

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...

String as date
 

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

On 7 Apr., 03:13, Steve wrote:
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...

String as date
 

Thanks, but using 033109 in A1, this sub returned '4//209

---
Steve

On Apr 6, 10:32*pm, Per Jessen wrote:
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

On 7 Apr., 03:13, Steve wrote:

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...

String as date
 

hi
after this line.....
MyDate = DateAdd("d", 1, MyDate)
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

"Steve" wrote:

Thanks, but using 033109 in A1, this sub returned '4//209

---
Steve

On Apr 6, 10:32 pm, Per Jessen wrote:
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

On 7 Apr., 03:13, Steve wrote:

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...

String as date
 

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