CODEWITHSUNDEEP
excelvba
Daquicker
Daquicker

Reputation: 525

What's wrong with this piece of VBA code that should return a number of days?

So I wrote this piece of code (it's actually part of a something bigger but this part gives me the problems) and I'm new to VBA so I can't figure out why this always returns 0 as the formula result. 

Function test(begindatum As Date, einddatum As Date)  
Dim Days1  
If begindatum < 1 / 9 / 1996 And begindatum > 31 / 7 / 1986 Then  
    If einddatum > 31 / 8 / 1996 Then  
        Days1 = DateDiff("d", 1 / 9 / 1996, begindatum)  
    Else: Days1 = DateDiff("d", einddatum, begindatum)  
    End If  
End If  
test = Days1  
End Function

I tested with various dates btw, all of them returned the output 0.
Would appreciate it if someone could point out where I went wrong.

Kindly regards,
Daquicker

Upvotes: 0

Views: 113

Answers (4)

iDevlop
iDevlop

Reputation: 25272

No spaces, proper # separator, and VBA always expects american (m/d/yy) format, so for september 1, use #9/1/1996#.

Upvotes: 0

Tiago Cardoso
Tiago Cardoso

Reputation: 2097

Just to share knowledge... you can also use the CDate function. It works pretty well and also accepts other date formats, as CDate("01/Sep/1996").

Pay attention, however. Handle date may become a pain if you'll use it under different regional settings (global apps, for instance).

Upvotes: 0

Jonathan M
Jonathan M

Reputation: 17451

You need to use DateSerial to convert all dates to integers, then compare them.

Upvotes: 2

Alex K.
Alex K.

Reputation: 175896

Your dates;

 1 / 9 / 1996

are mathematical expressions - 1 divided-by 9 divided by 1996 - which will be zero when coerced to an integer type.

for a literal date use:

 If begindatum < #1/9/1996# and ...

Upvotes: 3

Related Questions