formatting user input

[deviation]

I have an input macro that reuest a user to enter a date..

I am using the input to run update queries for multiple sources..

my question is how can I change the format for the different sources in the macro versus having the user enter the input different time different ways

I currently have the user entering a date a mmddyy (010105)

this opens datatables in multple databases named the same as the input(ie 010105)

now I need to change the format of that so that it reflects as 01/01/05 to auto update a pivot table.. I currently have everthing i need.. i just need to convert the 010105 to 01/01/05.

I tried to use format(x, "mm/dd/yy"), but since I am not collecting the user input as a date originally it gives some real funky results (010105 = 8/31/1927)..

I imagine something like len, right, left might do the trick.. but as far as how to write that out.. I'm lost

Thanks in advance

 

[cfree36]

this should work:

=LEFT(C7,2)&"/"&RIGHT(LEFT(C7,4),2)&"/"&RIGHT(C7,2)

change C7 tp the cell that contains 010105

 

[deviation]

The user input is never entered into a cell.. would this hold true for the variable?

 

[cfree36]

You can modify this code... seems to work

Sub InputDate()
Dim MyDate As String
MyDate = Application.InputBox(prompt, "What Date Would You Like?", "010105")
Range("A1").Value = Left(MyDate, 2) & "/" & Right(Left(MyDate, 4), 2) & "/" & _
Right(MyDate, 2)
End Sub

This code is putting the user entry of 010105 into cell A1 as text and inserts the '/' where necessary.

 

[Norie]

How are you getting the user input?

 

[deviation]

x = Application.InputBox()

here is what I have

Function getdate() As String
    Dim dt As Date
    Dim strTemp As String
    Dim x
       
    x = Application.InputBox("Please enter the data of reporting you wish to use (format mmddyy)", "Select Date", x)
    
    dt = Format(Left(x, 2) & "/" & Right(Left(x, 4), 2) & "/" & Right(x, 2))
    'old code 
    'dt = Date - 1
    
    'response trap
    MsgBox ("date as " & dt)
    
    strTemp = "[Date].[All Date].["
    
    strTemp = strTemp & Year(dt) & "].[Qtr 0" & Format(dt, "q yyyy") _
        & "].[" & Format(dt, "mmm yyyy") & "].[Week " & Format(dt, "ww yyyy") _
        & "].[" & Format(dt, "mm/dd/yyyy") & "]"
    
    getdate = strTemp

   
End Function

Private Sub ChangePTDate()

    Sheets("APR").Select
    ActiveSheet.PivotTables("PivotTable3").PivotFields("[Date]").AddPageItem _
        getdate, True

   
End Sub

Now the problem I have is the application.inputbox described here is a second input box. the first one is in another macro.. but I have yet to figure out how to make this work with one input box....

when I tried without the function it did not change the date in the pivot table, it only added the date so that the pivot table was reflecting multiple days of data.

the formatting is correct now using the

Format(Left(x, 2) & "/" & Right(Left(x, 4), 2) & "/" & Right(x, 2))

but I would really love to get rid of the second inputbox, either by passing the variable to the function of using the ChangePTDate with out the function..

[/code]

 

[deviation]

.:: bump

 

[deviation]

Found my problem

I omitted the True from the statement..

in case anyone is looking for something similar here it is

    x = Application.InputBox("What date do you want look at (format mmddyy), "Select Date", x)

    dt = Format(Left(x, 2) & "/" & Right(Left(x, 4), 2) & "/" & Right(x, 2))
   
    strTemp = "[Date].[All Date].["

    strTemp = strTemp & Year(dt) & "].[Qtr 0" & Format(dt, "q yyyy") _
        & "].[" & Format(dt, "mmm yyyy") & "].[Week " & Format(dt, "ww yyyy") _
        & "].[" & Format(dt, "mm/dd/yyyy") & "]"

    y = strTemp

    Sheets("APR").Select
    ActiveSheet.PivotTables("PivotTable3").PivotFields("[Date]").AddPageItem _
    y, True

[/code]