VBA Date Time arithmetic

xblzbl

New Member
Joined
Jul 22, 2010
Messages
4
Hello,
I am struggling with date/time calculation in VBA.

1. Can I add/subtract dates as in the next example:
Dim vStart As Date, vEnd As Date, vHours As Date
dim vHours1 as integer

vStart="20:00"
vEnd = "23:00"
vHours=vEnd-vStart

vHours gives me "03:00:00" which is correct to me. Is is simple to use, write and understand.
So why should I use vHours1 = DateDiff("n", vStart, vEnd)?
vHours1=180 (minutes) which also correct


2.
Dim vStart As Date, vEnd As Date, vHours As Date
dim vHours1 as integer
dim var1 as double, var2 as double, var3 as double

vStart="01:00"
vEnd="01:00"
vHours=vStart-vEnd
var1 = vstart
var2=vend
var3=var2-var1

vHours gives me "00:00"
in fact it is not (sometimes, not all the time !!!!)

var1=4,1666667E-02
var2=4,1666667E-02
var3=2,081668171E-17 (sometimes var3=0 !!)

So var1 <> var2 and so vStart<>vEnd !!!!!

This means that if I compare vStart and vEnd as in
if vStart=Vend then ......
sometimes they are equal and sometimes they are not

Question is :
How should I should compare times
if vStart=Vend then ......
if vStart>Vend then ......
etc.

Thanks for your help.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

xblzbl

New Member
Joined
Jul 22, 2010
Messages
4
Thank You, I'll try your suggestion.
Any idea why 2 variables with the time are not equal?
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Not sure, could be related to the limited precision of Excel (and computers in general). 15 or 16 digits is the maximum. It might be safer when dealing with times to compare times directly, but rather to subtract times and check for a difference that is smaller than some tolerance.

E.g.

instead of checking
Var1 = Var2

check
(Var1-Var2) < Timevalue ("00:00:01")
 

Watch MrExcel Video

Forum statistics

Threads
1,133,717
Messages
5,660,469
Members
418,580
Latest member
IrishDave2137

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top