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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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")
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,158
Messages
5,509,500
Members
408,738
Latest member
mkarhoff

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top