CODEWITHSUNDEEP
excelvba
Amit Shah
Amit Shah

Reputation: 13

Excel VBA: Convert Now Output to be just time portion in general formatting

This should be really easy, but it is bugging me in VBA. I want to capture the time component as of running code/script/function, but want to store output as only a decimal that captures the time piece of date and time.

Current Code:

Cells(iRow, iCol + 1).Value = Now - (Now Mod 1)

However it returns in this format: 6/13/2021 9:24:40 PM I want it to return: 0.8921296296

Thanks in advance!

Upvotes: 1

Views: 260

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

A Small Time Study

  • The following is showing that CDbl(Time) and BigBen's Now - Date are not equal (for this case they are 'equal enough'). The TimeTest procedure kind of proves how actually only 5 decimals are needed to have distinct values for each second of a day.
Option Explicit

Sub TimeDebugPrint()
    Debug.Print Format(Now - Date, "hh:mm:ss")
    Debug.Print "Time", , Time
    Debug.Print "CDbl(Time)", , CDbl(Time)
    Debug.Print "Round(CDbl(Time), 5)", Round(CDbl(Time), 5)
    Debug.Print "Now - Date", , Now - Date
    Debug.Print "Round(Now - Date, 5)", Round(Now - Date, 5)
    Debug.Print "CDate(Round(Now - Date, 5))", CDate(Round(Now - Date, 5))
    Debug.Print "CDate(Now - Date)", CDate(Now - Date)
    Debug.Print "Int(Now)", , Int(Now)
    Debug.Print "Date", , Date
    Debug.Print "Int(Now) = Date", Int(Now) = Date
End Sub

Sub TimeTest()
    
    Const dAddress As String = "A1"
    Const rCount As Long = 86400 ' seconds in a day
    Const Decimals As Long = 5
    
    Dim Data(1 To rCount, 1 To 6) As Variant
    
    Dim curDate As Date
    Dim r As Long
    
    For r = 1 To rCount
        curDate = r / rCount
        Data(r, 1) = curDate
        Data(r, 2) = r / rCount ' In the General format, 9 decimals are shown...
        Data(r, 3) = CDate(Data(r, 2))
        '... only 5 are enough to show distinct values for each second
        ' of the day.
        Data(r, 4) = Round(r / rCount, Decimals)
        Data(r, 5) = CDate(Data(r, 4))
        ' VBA doesn't consider the values in column 5 being equal
        ' to the values in columns 1 and 3 (milliseconds!?).
        'Data(r, 6) = Data(r, 1) = Data(r, 3) ' True
        'Data(r, 6) = Data(r, 1) = Data(r, 5) ' False
        'Data(r, 6) = Data(r, 3) = Data(r, 5) ' False
    Next r
    
    With Workbooks.Add
        With .Worksheets(1).Range(dAddress).Resize(rCount, UBound(Data, 2))
            Union(.Columns(1), .Columns(3), .Columns(5)) _
                .NumberFormat = "hh:mm:ss"
            .Value = Data
            ' Test if the values in the date (time) columns 3 and 5 are equal.
            .Columns(6).Formula = "=" & .Cells(3).Address(0, 0) _
                & "=" & Cells(5).Address(0, 0)
            .Columns(6).Value = .Columns(6).Value
            .EntireColumn.AutoFit
        End With
        .Saved = True ' for easy closing
    End With

End Sub

Upvotes: 0

Gustav
Gustav

Reputation: 55806

The direct method is to prefer:

Cells(iRow, iCol + 1).Value = Time

Upvotes: 2

Related Questions