Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text String to Date
I have been reading many many posts regarding this topic, but of course none
seems to be exactly what I am looking to do. So.. I have an InputBox getting a string and it has been doing what I want. I want the user to give me a 6 digit date that I incorporate into a filename later on. FileDate = InputBox("Enter the 6 digit date of file" & vbLf & "(Example: 031208)", "Date of SCR Data") BUT at the same time I'd like the input to go into one of the cells as a date "m/d/yy". What I have been getting is a date "6/10/85" which I understand is the date equivalent for day #31208. What I'd like to see is "03/12/08" or "3/12/08". Any help is appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text String to Date
On Apr 10, 1:32 pm, Bigfoot17
wrote: I have been reading many many posts regarding this topic, but of course none seems to be exactly what I am looking to do. So.. I have an InputBox getting a string and it has been doing what I want. I want the user to give me a 6 digit date that I incorporate into a filename later on. FileDate = InputBox("Enter the 6 digit date of file" & vbLf & "(Example: 031208)", "Date of SCR Data") BUT at the same time I'd like the input to go into one of the cells as a date "m/d/yy". What I have been getting is a date "6/10/85" which I understand is the date equivalent for day #31208. What I'd like to see is "03/12/08" or "3/12/08". Any help is appreciated. Hi, do this helps?: Sub InputDate() Dim FileDate As String, dDate As Date FileDate = InputBox("Enter the 6 digit date of file" & vbLf & "(Example:031208)", "Date of SCR Data") If Len(FileDate) < 6 Then MsgBox "invalid date" Exit Sub End If dDate = DateSerial(Mid(FileDate, 5, 2), Mid(FileDate, 1, 2), Mid(FileDate, 3, 2)) With Range("c5") .Value = dDate .NumberFormat = "m/dd/yy" End With End Sub HTH Jaime Vasquez |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text String to Date
format the number to insert the slashes
=Format(031208,"00/00/00") "Bigfoot17" wrote: I have been reading many many posts regarding this topic, but of course none seems to be exactly what I am looking to do. So.. I have an InputBox getting a string and it has been doing what I want. I want the user to give me a 6 digit date that I incorporate into a filename later on. FileDate = InputBox("Enter the 6 digit date of file" & vbLf & "(Example: 031208)", "Date of SCR Data") BUT at the same time I'd like the input to go into one of the cells as a date "m/d/yy". What I have been getting is a date "6/10/85" which I understand is the date equivalent for day #31208. What I'd like to see is "03/12/08" or "3/12/08". Any help is appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text String to Date
Thank you this was very helpful. You not only showed me how to resolve this
problem but tightened the code by dealing with invalid input. Thanks. "JaimeVasquez" wrote: On Apr 10, 1:32 pm, Bigfoot17 wrote: I have been reading many many posts regarding this topic, but of course none seems to be exactly what I am looking to do. So.. I have an InputBox getting a string and it has been doing what I want. I want the user to give me a 6 digit date that I incorporate into a filename later on. FileDate = InputBox("Enter the 6 digit date of file" & vbLf & "(Example: 031208)", "Date of SCR Data") BUT at the same time I'd like the input to go into one of the cells as a date "m/d/yy". What I have been getting is a date "6/10/85" which I understand is the date equivalent for day #31208. What I'd like to see is "03/12/08" or "3/12/08". Any help is appreciated. Hi, do this helps?: Sub InputDate() Dim FileDate As String, dDate As Date FileDate = InputBox("Enter the 6 digit date of file" & vbLf & "(Example:031208)", "Date of SCR Data") If Len(FileDate) < 6 Then MsgBox "invalid date" Exit Sub End If dDate = DateSerial(Mid(FileDate, 5, 2), Mid(FileDate, 1, 2), Mid(FileDate, 3, 2)) With Range("c5") .Value = dDate .NumberFormat = "m/dd/yy" End With End Sub HTH Jaime Vasquez |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate Date from a Text String | Excel Worksheet Functions | |||
Date in Text String | Excel Worksheet Functions | |||
VBA convert day and date from text string to Excel date | Excel Programming | |||
How do i parse a text string with a date? | Excel Programming | |||
Help !, to find last 'text' date in text string | Excel Programming |