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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,625
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,123,451
Messages
5,601,727
Members
414,470
Latest member
glukemey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top