Page 1 of 1
VBA Problem
Posted: Tue Mar 16, 2004 11:22 am
by 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
Posted: Tue Mar 16, 2004 11:36 am
by 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
Posted: Tue Mar 16, 2004 12:01 pm
by 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
Posted: Tue Mar 16, 2004 3:31 pm
by 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