none
Strip time from date stamp RRS feed

  • Question

  • i need a vba script to strip Time from date stamp- require date only

    Column C for the example below,

    Example:

    4/07/2019 14:23
    4/07/2019 14:23
    4/07/2019 14:10
    4/07/2019 13:59
    4/07/2019 13:59
    4/07/2019 16:56
    4/07/2019 13:37
    4/07/2019 13:37
    4/07/2019 13:36


    • Edited by mctech2017 Thursday, July 4, 2019 12:20 PM
    Thursday, July 4, 2019 12:19 PM

All replies

  • Hello,

    Use the Format function to format the date time object in the way you need. 


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Thursday, July 4, 2019 12:21 PM
  • This isn't sufficient as i'm using a formula to calculate the number of days between two dates, therefore if the dates are as follows it calculates it as 1 day, which is incorrect.

    Date 1 = 4/07/2019

    Date 2 = 4/07/2019 14:23

    Thursday, July 4, 2019 12:32 PM
  • Try this:

    Sub StripTime()
        Dim r As Long
        Dim m As Long
        m = Range("C" & Rows.Count).End(xlUp).Row
        For r = 2 To m
            Range("C" & r).Value = Int(Range("C" & r).Value)
        Next r
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, July 4, 2019 12:41 PM