Date/Time Value not being stored as Date/Time?

[Pascal]

Hi,

I have the following code from my Form which stores the Date & Time of an entry on to my sheet: -

Private Sub BookVehicle()

    Dim lngLast As Long
    
    '* Find Last Row.
    lngLast = Range("B65536").End(xlUp).Row + 1
    
    '* Select row.
    Cells(lngLast, 4).Select
     
    '* Add (Append) Entry to Sheet.
    With frmAdd
        Cells(lngLast, 2).Value = UCase(.cboRegistrations.Value)
        Cells(lngLast, 3).Value = UCase(.txtContractor.Text)
        Cells(lngLast, 4).Value = ActiveCell.Row - 10
        Cells(lngLast, 5).Value = UCase(.txtNo.Text)
        Cells(lngLast, 6).Value = UCase(.cboContact.Value)
        Cells(lngLast, 7).Value = UCase(.txtLocation.Text)
        Cells(lngLast, 8).Value = Format(Date, "dd/mm/yyyy") & "  " & Format(.dtpTimeIn.Value, "hh:mm:ss")
        Cells(lngLast, 9).Value = Format(.dtpTimeIn.Value, "hh:mm")
        '* If Phone field left blank treat as no Phone given.
        If .txtPigeon.Text = "" Then
            Cells(lngLast, 11).Value = "X"
        Else
            Cells(lngLast, 11).Value = .txtPigeon.Text
        End If
    End With
    
    Cells(lngLast, 9).NumberFormat = "hh:mm"
    
    '* Format entry.
    With Range(Cells(lngLast, 2), Cells(lngLast, 12))
        .Font.Name = "Tahoma"
        .Font.Size = 12
        .VerticalAlignment = xlCenter
    End With
    
    Range(Cells(lngLast, 4), Cells(lngLast, 5)).HorizontalAlignment = xlCenter
    Range(Cells(lngLast, 9), Cells(lngLast, 11)).HorizontalAlignment = xlCenter
    
    'Range(Cells(lngLast, 8), Cells(lngLast, 8)).NumberFormat = "dd/mm/yyyy hh:mm"
    
End Sub

After carrying out some bookings this is the data that has been stored: -

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L
10	Registration	Contractor	No.	No. of Persons	Contact	Location		Time In	Time Out	Phone	Notes
11	HK57 LFL	TSML	1	1	L REYNOLDS	SITE	15/08/2013 19:42:00	19:42		X
12	RK56 XFB	TSML	2	1	L REYNOLDS	SITE	15/08/2013 19:48:00	19:48		1
13	B11 VVY	BLUE SHIELD	3	2	L BARTLETT	ENGINEERS	15/08/2013 19:48:00	19:48		2
14	WV11 DXE	ANDY WATERS	4	1	G WILLIAMS	SITE	15/08/2013 19:49:00	19:49		3
Home

In column H it shows the Date & Time, however it's Left Aligned. Only if I select the cell, hit F2 and then enter does it Align it to the Right and is picked up correctly in my formulas, such as: -

=COUNTIFS(H11:H50,">"&O2,H11:H50,"<="&O3)

Below I've carried out the F2 & Enter on the last two rows to show you what I mean: -

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L
11	HK57 LFL	TSML	1	1	L REYNOLDS	SITE	15/08/2013 19:42:00	19:42		X
12	RK56 XFB	TSML	2	1	L REYNOLDS	SITE	15/08/2013 19:48:00	19:48		1
13	B11 VVY	BLUE SHIELD	3	2	L BARTLETT	ENGINEERS	15/08/2013 19:48	19:48		2
14	WV11 DXE	ANDY WATERS	4	1	G WILLIAMS	SITE	15/08/2013 19:49	19:49		3
Home

What can I do to correct this via my code?

Many Thanks & Regards

 

[Jonmo1]

This line

Cells(lngLast, 8).Value = Format(Date, "dd/mm/yyyy") & " " & Format(.dtpTimeIn.Value, "hh:mm:ss")
Is entering a TEXT string (the Format function returns TEXT)

Try changing that to just simply
Cells(lngLast, 8).Value = Date + .dtpTimeIn.Value

 

[Pascal]

This line

Cells(lngLast, 8).Value = Format(Date, "dd/mm/yyyy") & " " & Format(.dtpTimeIn.Value, "hh:mm:ss")
Is entering a TEXT string (the Format function returns TEXT)

Try changing that to just simply
Cells(lngLast, 8).Value = Date + .dtpTimeIn.Value

LOL

Many, many thanks for your time in replying!

I'd tried all sort of combinations apart from that, which worked.

Once again, thank you.

Regards

 

[Jonmo1]

Glad to help, thanks for the feedback.