VBA Problem

[Tranquil]

Hello all!

Small problem here in excel VBA:

I want to check how many minutes are passed within two dates.
Eg.:

First Date: 12.01.2004 12:40
Second Date: 13:01.2004 11:20

How many minutes are passed by this two Dates and Time? I now the DateDiff() Function, but it does not care of the time. There is also no "Time since 1900"-Function in VB. (Or I'm to silly to find it)

Anyone can help?

Cheers
Mike

[LarsG]

I did this in a cell:

Code: Select all

=(TIME(C6)-TIME(B6))+((MINUTT(C6)-MINUTT(B6))/100)

note: this is the Norwegian Excel, and TIME() is the HOUR() function, and MINUTT() is the MINUTE() function..

This probably doesn't give you a "correct" result, but it's there though.. hehe

[GedB]

The DateDiff function can be used with the interval 'n' for minutes:

Code: Select all

Sub main()

    Dim Date1 As Date
    Dim Date2 As Date
    Date1 = "01-Jan-2004 15:00:00"
    Date2 = "28-Feb-2004 08:00:00"
    
    MsgBox DateDiff("n", Date1, Date2)

End Sub

Just make Date 01-Jan-1900 if you want.

Full list of intervals are:

yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

Remember that VBAs Date Type actually identifies a time to the second and not to the day. The Date() function gives the current date with the time left at 00:00:00 while Now() gives the current date and time.

The following code demonstrates:

Code: Select all

Sub main()

    Dim Date1 As Date
    Dim Date2 As Date
    Date1 = Date
    Date2 = Now
    
    Msg = "Date() : " & Format(Date1, "dd-mmm-yyyy hh:mm:ss") & vbCr & _
        "Now() : " & Format(Date2, "dd-mmm-yyyy hh:mm:ss") & vbCr & _
        "seconds : " & DateDiff("s", Date1, Date2) & vbCr & _
        "minutes : " & DateDiff("n", Date1, Date2)
        
    MsgBox Msg
End Sub

At work I may be with VB, but I'm thinking of PB

[Tranquil]

Yepa, last version did it. Thanks both of you!!

I suggested that datediff() only works with date and not in combination with time. And I always searched for a method to count the minutes since 1900 to calculate it myselfe.

I must notice that in VB are many things a lot easier as in PB. Hm..... But it it a LOT OF slower.

Mike