work out number of minutes between two date plus times in VBA

[th081]

Hi All,

Can someone help me with how to work out the number of minutes between two dates and time in VBA. I am using DateDiff but it seems to truncate the time part and ignore it.

I have two arrays one starts at 01/04/2021 with the next value increasing by 30mins so being 01/04/2021 00:30 etc, the second array has a imported spreadsheet date and time, both are declared as variants because for the second one i am assigning the whole range to the array.

This is my sub:

Sub Declaration_Compare2(x As Integer, y As Integer)
p = x
L = y
Dim nMins As Long
Dim dt1 As Date
Dim dt2 As Date



dt1 = DateValue(Effective_DateTime_F(p, 1))
dt2 = DateValue(MyArraydatestart(L))

nMins = DateDiff("n", dt1, dt2)



End Sub

the Effective_DateTime_F(p, 1) value is 31/03/21 22:59:00 as imported from the spreadsheet
the MyArraydatestart(L) value is 01/04/21 as held in the array

i was expecting nMins to be the difference in minutes between the date and times but it gives 1440 i.e just one day, it does not take the time into account. Can i get it to take the time into account or is there another function to use?

Regards

Taz

 

[Rick Rothstein]

Is there a time value associated with your 2nd date or will it always be midnight (0:00)?

 

[th081]

Hi Rick,

There is a time associated with the second date, so the next value in that array is 01/04/21 00:30