CODEWITHSUNDEEP
vbaexceldatetimetime
bigbryan
bigbryan

Reputation: 431

How to subtract Time in Excel VBA?

How do I subtract Time in Excel VBA? I tried to subtract these two values, but I'm getting this value "2.1527777777778E-02" instead. What does this mean?

Timein = 12/7/16 12:00:00 AM

Timeout = 12/7/16 12:30:00 AM

Here's a sample of my code. Thanks in advance.

Dim Total as Double
Dim Timein as Date
Dim Timeout as Date

Total = TimeValue(Timeout) - TimeValue(Timein)
'Result Total=2.1527777777778E-02

Upvotes: 0

Views: 26123

Answers (5)

cicorp
cicorp

Reputation: 1

Multiply 2.1527777777778E-02 x 24 x 60 x 60 = 1860 seconds for 24 hours x 60 minutes x 60 seconds

Upvotes: -1

CLR
CLR

Reputation: 12289

2.1527777777778E-02 is using a method of displaying a value called Scientific Notation. In your case, it means 2.1527777777778 times 10 to the power of -2. Or you could think of the E-02 part as meaning shift the decimal point two places to the left (left because it's negative). So: 0.021527777777778.

Excel treats time (both dates and times of day) as whole days so half a day (12 hours) would be represented as 0.5.

So the result itself represents the decimal fraction of an entire day, if you want this in minutes for example, you would multiply the value by 1440 (24hr x 60min make a day) which would give you 31mins.

In your example, you're finding the difference between 12:00 and 12:30 so you should actually be getting a result of 2.08333333333333E-02 which if multiplied by 1440 would give you 30mins.

Excel gives you tools to find the difference between two points in time though that take all that complex math(s) away - DateDiff and @holger has given you everything you need there to write your own code.

Upvotes: 3

Holger
Holger

Reputation: 330

You can use the DateDiff Function to get the difference in year/days/seconds or whatever.

Here the example for minutes.

Dim Timein As Date
Dim Timeout As Date

Timein = "12/7/16 12:00:00 AM"
Timeout = "12/7/16 12:30:00 AM"
Debug.Print DateDiff("n", Timein, Timeout)

Output:

30

Interval Explanation

yyyy Year

q Quarter

m Month

y Day of year

d Day

w Weekday

ww Week

h Hour

n Minute

s Second

Upvotes: 12

YowE3K
YowE3K

Reputation: 23994

What you have done is perfectly correct, as can be seen by the following code:

Sub test()
    Dim Total As Double
    Dim Timein As Date
    Dim Timeout As Date
    Timein = CDate(Range("A1").Value)
    Timeout = CDate(Range("A2").Value)

    Total = TimeValue(Timeout) - TimeValue(Timein)

    Debug.Print Total
    Debug.Print Format(Total, "hh:mm:ss")

    Range("A3").NumberFormat = "hh:mm:ss"
    Range("A3").Value = Total

    Debug.Print "Number of hours = " & Total * 24
End Sub

As mentioned by vacip in a comment, Date/Time variables are stored in VBA in "days" so, for example, Now for me is the number 42867.7513310185.

Upvotes: 3

Ben
Ben

Reputation: 11

You have defined the total as double, which returns you number. you need to dim the total as date so that returns you a time.

Dim Total as Date
Dim Timein as Date
Dim Timeout as Date

Total = TimeValue(Timeout) - TimeValue(Timein)

Upvotes: 0

Related Questions