CODEWITHSUNDEEP
excelvbadatetime
Russ
Russ

Reputation: 7

Removing Timestamp from date (text format)

I have an Excel macro getting 1000+ records from database.

The dates in the data are fetched with timestamp and is in text format. I need to format the dates in DD-MON-YYYY format.

Input:
2016-04-03 00:00:00

Output:
03-Apr-2016

I tried:

  1. Casting all the date columns to date in the DB query - The macro keeps running for forever and excel stops responding.

  2. TRUNC(TO_DATE('PROJECT_START_DATE'),'YEAR') in query - Throws error

  3. INT(Cell) or DateTime(Cell) in excel - Throws error

Upvotes: 0

Views: 1228

Answers (1)

Storax
Storax

Reputation: 12167

If you really get the date in text format like 2016-04-03 00:00:00 as in your post then you could use the following function to get a date without a timestamp

Function cvtDt(inp As String) As Date
' inp is a string like 2016-04-03 00:00:00
    On Error GoTo EH

    cvtDt = CDate(Left(inp, 10))
    Exit Function

EH:
    cvtDt = 0
End Function

The following sub then writes your example data to a cell in a sheet with the format you mentioned in your post.

Sub OutToXL()

    Dim val As Date
    val = cvtDt("2016-04-03 00:00:00")

    With Range("A1")
        .NumberFormat = "dd-mmm-yyyy"
        .Value2 = val
    End With

End Sub

INT should also work unless you have white spaces at the end of your string. In this case it will throw an error.

Upvotes: 0

Related Questions