CODEWITHSUNDEEP
exceldatetimevba
georgian1990
georgian1990

Reputation: 9

VBA macro - extract time from date & time excel cell - VBA

I have to columns B and C which are containing data under the format mm/dd/yyyy hh:mm - which represents the start (column B) and the end dates (column C) of some meetings.

I'm trying to create a VBA macro that can extract from both of the columns only the time under the format hh:mm and then calculate the difference between the two timings in order to find out the duration of each meeting (the result will be displayed on column D).

Can you please advised what can be done into here?

Upvotes: 0

Views: 8634

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

With 19/06/2017 13:30:00 in A1 and 20/06/2017 13:39:00 in A2.
The formula =A2-A1 given the custom number format of [h]:mm:ss returns 24:09:00.
The square brackets around the h tell Excel to show times over 24 hours.
Without the square brackets it will show the time as 00:09:00. This is because it is calculating as 1 day and 9 minutes, but is not showing the count of days.

To get just the time from a date you can either just format as a time which will keep the dart part of your date/time but display just the time.
Or, you can remove the integer of the date/time to get just the time - the date part will show as 00/01/1900 in this case. =A1-INT(A1)

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Give this a try:

Sub difff()
    Dim i As Long, N As Long

    N = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 1 To N
        Cells(i, "D") = Cells(i, "C") - Cells(i, "B")
    Next i

    Range("D:D").NumberFormat = "hh:mm"
End Sub

enter image description here

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57673

In column D use the formula 

=(C:C-B:B)*24

to get the difference in hours.

Or instead just use the below formula in column D 

=C:C-B:B

and format column D as time to get the difference in a format like hh:mm

you can add this formula to a desired range with VBA if necessary like

With Worksheets("MySheet").Range("D1:D5").Formula = "=(C:C-B:B)*24"

or time formatted like

With Worksheets("MySheet").Range("D1:D5")
    .Formula = "=C:C-B:B"
    .NumberFormat = "hh:mm"
End With

Upvotes: 2

Related Questions