Problem with "dd-mm-yy hh:mm" and "mm-dd-yy hh:mm"

VeKa27

New Member
Joined
Sep 11, 2015
Messages
45
Hi Guys,

I am struggeling with an anoying problem. I have made a userform in an Excelsheet. This form has 2 textboxes (one for date and one for time). The Date is allready filled in with the date of today. The user has to fill in the time and after hitting 'enter' the form saves automatictly date and time in the active cell on the excelsheet.
The form works fine but i am having a date problem. When the date is under 12, excel sees it as a month.
So if i use 15/03/2019 and 15:00, Excel writes down "15/03/2019 15:00"
If i use 10/03/2019 and 15:00, Excel writes down "03/10/2019 15:00"
Any suggestions?
I want to put an example document as attachment but i can't (don't see the option??). So this is the code for the textbox with the Time.

Hope for solution..
Thanks in advance

Code:
Private Sub TextBoxTime_Afterupdate()
Dim tString As String
Dim sDate As Date
On Error GoTo ErrMsg


With TextBoxTime
    If InStr(1, .Value, ":", vbTextCompare) = 0 Then
        tString = Format(.Value, "0000")
         tString = Left(tString, 2) & ":" & Right(tString, 2)
        TextBoxTime.Value = Format(TimeValue(tString), "hh:mm")
     Else
        .Value = Format(.Value, "hh:mm")
                
     End If
     
    sDate = CDate(TextBoxDate.Value)
    ActiveCell.Value = DateSerial(Year(sDate), Month(sDate), Day(sDate)) & " " & Format(TimeValue(TextBoxTime.Value), "hh:mm")
    
'***** I tried these also but same effect
    'ActiveCell.Value = Me.TextBoxDate.Value & "  " & Me.TextBoxTime.Value
    'ActiveCell.Value = DateValue(TextBoxDate.Value) & " " & TimeValue(TextBoxTime.Value)
    'ActiveCell.Value = TextBoxDate.Value
    'ActiveCell.NumberFormat = CDate("dd-mm-yy hh:mm")
         
End With
Exit Sub


ErrMsg:
 MsgBox "Wrong Input"
 TextBoxTime.Value = ""
 
End Sub
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,412
Try:
Code:
        sDate = CDate(TextBoxDate.Value)
        ActiveCell.NumberFormat = "dd-mm-yy hh:mm"
        ActiveCell.Value = sDate + TimeValue(TextBoxTime.Value)
 

VeKa27

New Member
Joined
Sep 11, 2015
Messages
45
Hi John_w,
I was supprised to see that my thankfull answer wasn't registred long time ago.. suppose something went wrong..
So again, 1000-times thank you for your solution. It worked and solved my problem ;)
Cheers,
 

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,991
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top