[RESOLVED] converting custom date/time value

I'd appreciate some help on this, as I'm stuck.

I've have an excel workbook that uses macros to pull data from a company database. Almost all records have a date/time value associated with it.

Most date/time values use the format dd-mmm-yy hh:mm:ss (10-Dec-05 08:50:25)

My problem is that some tables are storing records in a similar format, but the values come out as 10-Dec-05 08:50:25.000
I'm trying to find records by more/less than queries, but it won't work with those zero values on the end.

How do I get rid of those three zeros on the end so that my macro recognises it as a date/time value?

Cheers,
Carl

Through VBA, use the following code.

VB Code:

Range("A1").NumberFormat = "dd-mmm-yy hh:mm:ss"

This will set the number format of A1 cell to "dd-mmm-yy hh:mm:ss" format.

If you do not want to use VBA then,
Select the cells. Go to Format->Cells. Select the Number tab then Select custom and then enter type as "dd-mmm-yy hh:mm:ss".

Or, you can do one more thing. If you pull the data from database using vb/vba code, then you can use the format function to change the format of the date and time to your format. Let's say,

VB Code:

strDateTime="10-Dec-05 08:50:25.000"
Msgbox Format(strDateTime,"dd-mmm-yy hh:mm:ss")

Thanks but i'd already tried that and got Type Mismatch.

Here's what I'm doing

VB Code:

Set rstTemp = qdfTemp.OpenRecordset()
strDateTime = rstTemp.Fields("Time & Date")
MsgBox (strDateTime)   'gives me 10-Dec-05 08:50:25.000
Msgbox Format(strDateTime,"dd-mmm-yy hh:mm:ss")   'gives me Type Mismatch

I think I have to strip off the trailing zeros before trying to format as a Date & Time. I haven't been able to do that.

Cheers,
Carl

This should help:

VB Code:

Option Explicit
 
Private Sub Form_Load()
Dim x As Integer
Dim strdatetime As String
strdatetime = "10-Dec-05 08:50:25.000"
MsgBox (strdatetime)   'gives me 10-Dec-05 08:50:25.000
x = InStrRev(strdatetime, ".") - 1
If x > 0 Then
 strdatetime = Left(strdatetime, x)
End If
MsgBox Format(strdatetime, "dd-mmm-yy hh:mm:ss")
End Sub

Thanks very much. That was just was I needed.

Cheers,
Carl