CODEWITHSUNDEEP
excelvba
deadc0der7
deadc0der7

Reputation: 311

getting wrong value when dealing with time in vba excel

I'm trying to do some calculations on my excel sheet where I'm using macros , i'm dealing with time values.

 h2 = rg.Cells(i, 5) 'time value
 h1 = rg.Cells(i, 4) 'time value
 h3 = (TimeValue("23:59") - h1) + (h2 - TimeValue("23:59"))

the problem is for instance when h1="20:00" and h2="07:55" h3 should be 11:55 but it gives 12:05

BTW H1 is entrance time and H2 is time out so h2 is in the next day and i'm trying to calculate the deference of time.

enter image description here

regards

Upvotes: 0

Views: 324

Answers (2)

CLR
CLR

Reputation: 12254

Because H2, as you point out, is a day after H1, you need to factor that in to your calculation.

So to calculate the difference between H1 and H2, 24 hours should be added to H2 first.

  h2  +  1dy
07:55 + 24:00 = 31:55

                  v
                         -h1
                31:55 - 20:00 = 11:55

A single day presented as time is 1 - as time is measured as a decimal fraction of a day.

So among many other ways of doing it, to get the above, your formula should be:

h3 = (h2 + 1) - h1

Upvotes: 1

user11156232
user11156232

Reputation:

In fact the correct answer is minus 11:05:00 but Excel doesn't handle negative time. Adding one day to minus 11:05::00 equals positive 12:05:00.

Upvotes: 0

Related Questions