Comparing 2 Dates

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Hi,

I'm using the DTPicker to get the date and time.

I have a table that has 2 columns called RFIStart and
RFIEnd, on my form I setup a DTPicker for each box and that
works fine. The user clicks on the RFIStart box and picks
the Date, and changes the time to the correct time it
started. and the same goes for the RFIEnd.

So they look like

RFIStart = 05/Jul/2004 12:12
RFIEnd = 07/Jul/2004 15:45

I have the mask setup as dd/MMM/yy HH:mm

What I want is to have another box that would display the
difference between the start and end datea... and then if
the differece falls within a range I can have another box
say that the SLA was met or not.

Thanks
 
Tim,

Without knowing what that "difference" is, it's hard to give you an accurate
answer.

You might get away with measuring the difference in seconds, minutes, hours,
days, weeks, months or years:
DateDiff("ss", Date1, Date2)
DateDiff("nn", Date1, Date2)
DateDiff("hh", Date1, Date2)
DateDiff("dd", Date1, Date2)
DateDiff("ww", Date1, Date2)
DateDiff("mm", Date1, Date2)
DateDiff("yyyy", Date1, Date2)

But if you want anything more complex, one of these might be what you're
after:
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm
http://www.pacificdb.com.au/MVP/Code/DiffTime.htm

Then you need to do the comparison:
If diff > acceptablerange Then
MsgBox "The SLA has not been met!"
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Thanks Graham,

I have 2 different compairs to make, one is to compare the
number of days between the start and end date.

an example is that we recieve a ticket on 07/Jul/04 and we
have 3 day sla to meet and the job is complete on 09/Jul/04
if the difference is less than or equal to the 3 day sla
when we have met the sla and all is well if it is more than
the 3 days then there is a penalty.

we have other tickets that also rely on the time. so if a
ticket comes in at 07/Jul/04 9:13 AM and the sla is 3 days
and its not finished till 10/Jul/04 9:30 AM then we did not
met the sla and there is a penelty.

This is what I'm trying to figure out.

there are some variences, like if the ticket comes in
before 2pm that day then the sla starts from the date
recieved. If it comes in after 2pm then the sla is
calculated starting the next day.

I hope this makes sense.

I'll take a look at the links you provided.

-----

As a side question how is work for IT people in Sydney?
I'm an Aussie living in Canada and was thinking about
returning to Sydney. I'm currently a System Admin..

Thanks again for you help.
 
Tim,

<<...number of days between the start and end date>>
The thing to keep in mind is that the DateDiff function counts boundary
crossings - not the actual timespan. So to count the number of days between
the start and end date, you need to check if the exact hour, minute and
second has been reached.

The following will count the exact difference in days:
Abs(DateDiff("d", Date1, Date2) - IIf(Format(Date1, "hhnnss") <=
Format(Date2, "hhnnss"), 0, 1))

If you don't care about the time, then this will do:
DateDiff("d", Date1, Date2)

<<...how is work for IT people in Sydney?>>
IT work in Sydney is scarce if you're over 35, but for the younger ones its
not too bad (as I understand it).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top