Dates and times

[Guest]

I am working on database which has 2 fields imported from an Excel speadsheet
- a start date and time and a finish date and time. I need Acces to
automatically calculate the difference in a third field and then calculate
whether an SLA was met. Therefore I need to calculate in a fourth column
some sort of IF statement. I've never done this before. Any help gratefully
received. Phil.

 

[Guest]

Hi Phil,

One of these articles should be helpful to you when working with dates:

How to store, calculate, and compare Date/Time data in Microsoft Access
http://support.microsoft.com/?id=210276

How to Calculate Daily Hours Based on Clock In/Clock Out Times
http://support.microsoft.com/?id=237958

On time and how much has elapsed
http://office.microsoft.com/en-us/assistance/HA011102181033.aspx

Using dates and times in Access
http://office.microsoft.com/en-us/assistance/HA010546621033.aspx

Calculate Number of Working Days
http://www.mvps.org/access/datetime/date0006.htm

Doing WorkDay Math in VBA
http://www.mvps.org/access/datetime/date0012.htm


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Arvin Meyer [MVP]]

To calculate the date difference use the datediff() function:

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

So try someting like in a query column:

Difference: DateDiff("h", [FinishDate], [Start Date])

then use the Immediate If function IIF()

IIf(expr, truepart, falsepart)

so you'd do something like:

SLA_Met: IIF([Difference] > 4, -1, 0)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access