date difference function

[penfold1992]

I would like to check if one date is before another date

the date formats i have is currently
DD:MM:YY HH:MM

I could use CDate but this only checks to see if the day is different rather then the minute.

for example when i put these two dates in...
CDate(03/12/2012 16:15) - CDate(03/12/2012 12:13:00)
gives me an error because they are on the same day yet is a few hours after the first date...

is there any function that will do this better?

 

[Galaxiom]

DateDiff() function

 

[Alansidman]

If the time is irrelevant then you could use the Int function in front of the date field to give you only the date serial value and then run your comparison.

Alan

 

[pr2-eugin]

Use DateDiff(), The function takes in two dates and the difference factor may be date/month/year/ any part of the Date and Time variable..

DateDiff("d",Date(),Date())[COLOR=Green][B] 'will give 0 so they are same date[/B][/COLOR]
DateDiff("d",#12/05/2012#,Date()) [COLOR=Green][B]'will give -1 [/B][/COLOR]
DateDiff("d",Date(),#12/05/2012#)[B][COLOR=Green] 'will give 1 [/COLOR][/B]

 

[penfold1992]

nDate2Diff = TestDates(strDateComp, strAuthourisedDateTime)
 
If (nDate2Diff >= 0) Then
MsgBox ("Completion date can not be before Authorised Date.")
strDateComp = Null
strDateComplete = "NULL"
InsertFlag = False
End If

Function TestDates(pDate1 As Date, pDate2 As Date) As Long
   TestDates = DateDiff("n", pDate1, pDate2)
End Function

this appears to work.... unless the difference is high. sending the "Comp" date forward by a month or back by a month gives me an error "Overflow"

 

[pr2-eugin]

'n' denotes minutes.. so if you calculate difference of minutes in between dates.. what you are doing in the background is.. No. Of DaysDifference * 24 Hours in a Day * 60 minutes in an Hour.. So if it is between today and yesterday same time.. 1*24*60 = 1440..

If they are a month apart.. 30*24*60 = 43200 will be a overflow if the nDate2Diff is declared as Int, but will escape if it is Long.. but Long has its limitations too..

 

[pr2-eugin]

What you need to do is calculate it in steps, if DateDiff with Days as a difference parameter is >0 then you can simple pop the message no way.. if it is equal to 0 then you can find the minutes difference.. Makes sense??

If (nDate2Diff > 0) Then
    MsgBox ("Completion date can not be before Authorised Date.")
    strDateComp = Null
    strDateComplete = "NULL"
    InsertFlag = False
[COLOR=Red]ElseIf nDate2Diff = 0 Then
    newDate2Diff = TestDates(strDateComp, strAuthourisedDateTime, "n")[/COLOR]
End If
[B][COLOR=Teal]'==================================================================================[/COLOR][/B]
Function TestDates(pDate1 As Date, pDate2 As Date[COLOR=Red], diffFactor As String[/COLOR]) As Long
   TestDates = DateDiff([COLOR=Red]Nz(diffFactor,"d")[/COLOR], pDate1, pDate2)
End Function

I have highlighted the changes to your code, let me know if this is something you are after..

 

[penfold1992]

If (nDate2Diff > 0) Then
    MsgBox ("Completion date can not be before Authorised Date.")
    strDateComp = Null
    strDateComplete = "NULL"
    InsertFlag = False
[COLOR=red]ElseIf nDate2Diff = 0 Then[/COLOR]
[COLOR=red]  newDate2Diff = TestDates(strDateComp, strAuthourisedDateTime, "n")[/COLOR]
End If
[B][COLOR=teal]'==================================================================================[/COLOR][/B]
Function TestDates(pDate1 As Date, pDate2 As Date[COLOR=red], diffFactor As String[/COLOR]) As Long
   TestDates = DateDiff([COLOR=red]Nz(diffFactor,"d")[/COLOR], pDate1, pDate2)
End Function

i have a few questions about this hopefully not too difficult.
what exactly does "Nz(diffFactor,"d")" do? I dont know what Nz is...
also, "newDate2Diff" does this need a:
dim newDate2Diff as string but also it appears this should be the correct syntax:
newDate2Diff = TestDates("n",strDateComp, strAuthourisedDateTime)

I am also wondering if I could do something like....

Function TestDates(pDate1 As Date, pDate2 As Date[COLOR=red], diffFactor As String[/COLOR]) As Long
   TestDates = DateDiff[COLOR=black]([COLOR=black]diffFactor[/COLOR],[/COLOR] pDate1, pDate2)
End Function

then change the If function to...:

diffFactor = d
If (nDate2Diff > 0) Then
    MsgBox ("Completion date can not be before Authorised Date.")
    strDateComp = Null
    strDateComplete = "NULL"
    InsertFlag = False
[COLOR=red]ElseIf nDate2Diff = 0 Then[/COLOR]
diffFactor = n
nDate2Diff = TestDates(strDateComp, strAuthourisedDateTime)
If (nDate2Diff >= 0) Then
MsgBox ("Completion date can not be before Authorised Date.")
strDateComp = Null
strDateComplete = "NULL"
UpdateFlag = False
End If
 
End If

I am a novice coder, I dont know if this would even work but I am just trying my best to understand.

EDIT: it works if i just change the
Dim ndate2diff as Long
but thats not the point... XD

 

[pr2-eugin]

Sorry there is a slight change to the code..

Function TestDates(diffFactor As String, pDate1 As Date, pDate2 As Date) As Long
   TestDates = DateDiff(diffFactor, pDate1, pDate2)
End Function

what exactly does "Nz(diffFactor,"d")" do? I dont know what Nz is...

Nz() is a simplified If statement that tests for only one condition Is Null.. So the general syntax is

Nz( [I][B]theItemToTest[/B][/I] , [I][B]whatValueIfNull[/B][/I] )

So the code I have sees if the diffFactor is Null if so sets the default as "d", if not null i.e. if it has a value the use that value in this case "n".. However thinking again String will never be Null.. So I removed that bit..

dim newDate2Diff as string but also it appears this should be the correct

Since your edit says you changed it..

I am also wondering if I could do something like....

nDate2Diff = TestDates("d", strDateComp, strAuthourisedDateTime, diffFactor)
If (nDate2Diff > 0) Then
    MsgBox ("Completion date can not be before Authorised Date.")
    strDateComp = Null
    strDateComplete = "NULL"
    InsertFlag = False
[COLOR=red]ElseIf nDate2Diff = 0 Then[/COLOR]
     nDate2Diff = TestDates("n", strDateComp, strAuthourisedDateTime)
     If (nDate2Diff >= 0) Then
         MsgBox ("Completion date can not be before Authorised Date.")
         strDateComp = Null
         strDateComplete = "NULL"
         UpdateFlag = False
     End If
End If