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

Question

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?

If B/C are datetimes just pass them both as they are as arguments to datediff() — Alex K.
– Alex K., Commented Jun 20, 2017 at 11:48
Yes,the data contained from both columns are having the same date part. — georgian1990
– georgian1990, Commented Jun 20, 2017 at 11:50
@avb it has to be in a macro as the data for these meetings is extracted from outlook's calendar — georgian1990
– georgian1990, Commented Jun 20, 2017 at 11:54

Pᴇʜ · Accepted Answer · 2017-06-20 12:10:22Z

2

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

edited Jun 20, 2017 at 12:10

answered Jun 20, 2017 at 11:59

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary's Student · Accepted Answer · 2017-06-20 12:31:54Z

1

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

answered Jun 20, 2017 at 12:31

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

Darren Bartrup-Cook · Accepted Answer · 2017-06-20 12:48:16Z

0

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)

answered Jun 20, 2017 at 12:48

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related