Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Date string to date format | Excel Discussion (Misc queries) | |||
Search and Collect Date from Cell Containing a String with a Date | Excel Programming | |||
VBA convert day and date from text string to Excel date | Excel Programming | |||
changing a string date into a 'date' | Excel Programming | |||
Converting a string date into a Excel Date | Excel Programming |