Date Being Pasted USA Style When I Want UK Style From Input Box- Excel 2007

[salfordchameleon]

I'm using an input box in a do loop to ask for a date to be input UK style in the format DD/MM/YYYY.
I have the cell where it will be pasted to formated as Date Type *14/03/2001.
However when say, 11/02/2013 is typed into the input box, 02/11/2013 is pasted into the required cell.

the loop I am using is a simple one as I'm still learning :-
Do
WEEK = InputBox(Prompt:="Please Enter Current Week" & vbCr & "In The Format DD/MM/YYY")
If WEEK Like "??/??/????" Then
Exit Do
Else
MsgBox "Input Date In The DD/MM/YYYY Format"
End If
Loop

Then I use the following to paste

Range("B10").Value = WEEK

Any help please.

Thanks.

 

[JLGWhiz]

You could replace this:

Code:
Do
WEEK = InputBox(Prompt:="Please Enter Current Week" & vbCr & "In The Format DD/MM/YYY")
If WEEK Like "??/??/????" Then
Exit Do
Else
MsgBox "Input Date In The DD/MM/YYYY Format"
End If
Loop
Code:

With this:

Code:
RETRY:
WEEK = InputBox(Prompt:="Please Enter Current Week" & vbCr & "In The Format DD/MM/YYY")
If InStr(WEEK, "/") = 0 Then
MsgBox "Invalid Date"
GoTo RETRY:
End If
On Error GoTo HNDL:
WEEK = Format(WEEK, "DD/MM/YYYY")
HNDL:
If Err.Number <> 0 Then
Err.Clear
MsgBox "You must enter a valid date."
GoTo RETRY:
End If
Code;

 

[salfordchameleon]

cheers for that JLGWHIZ, but i'm still having the problem of when I paste my UK style entered date, say, 11/02/2013, it pastes as US style date 02/11/2013 even though the cell i'm pasting to is formated as UK style date.

 

[Norie]

Try this.

    Do
        WEEK = InputBox(Prompt:="Please Enter Current Week" & vbCr & "In The Format DD/MM/YYY")
        If WEEK Like "??/??/????" Then
            Range("B10").Value = DateValue(WEEK)
            Exit Do
        Else
            MsgBox "Input Date In The DD/MM/YYYY Format"
        End If
    Loop

 

[salfordchameleon]

thanks for the reply people.

using both examples, i am still getting the date pasted into cell B10 in US date format. ie, I enter 11/02/2013 (11th February 2013), excel then pastes in B10 02/11/2013 (2nd November 2013). Cell B10 is formated as the Date *14/03/2001 option under Format Cells Date option (right click mouse format cells option).

sorry to be a pain on this.

 

[Norie]

Where's the rest of the code?

When are you formatting the cell?

If you put this formula in an empty cell, after you've run the code, what do you get?

=ISNUMBER(B10)

Any difference if you use CDate instead of DateValue?

 

[salfordchameleon]

'Where's the rest of the code.' that got me to re look at teh code.

after inputing your code above Norie, then looking down the coding I noticed I still had 'Range("B10").Value = WEEK' left in my coding.
when I had taken this out, your above example worked perfectly.

sorry about that, my error.

Big thanks to Norie and JLGWhiz for taking time out to help solve this for me.