MsAccess utility to calculate offset time.

There is a requirement from user to calculate time difference in minutes between two date. The requirement is that you will not count the time from 6pm to the next day 9am.

Since we know that stored procedures and user defined functions cannot work in MsAccess if you want to use the database in web pages, you will have to do it in the views itself.

The formula is as follows.

tat: IIf(DatePart("d",[CREADATETIME]) <> DatePart("d",[ACKDATETIME]),IIf(DatePart("w",[CREADATETIME])=7,DateDiff("n",[creadatetime],[ackdatetime])-2520,IIf(DatePart("h",[creadatetime]) >18,DateDiff("n",Format([ackdatetime],"m/d/yyyy"),[ackdatetime])-540,DateDiff("n",[creadatetime],[ackdatetime])-900)),DateDiff("n",[creadatetime],[ackdatetime]))

It looks awful to concatenate the iifs but it works.

The script has a few conditions.

1. If it is Saturday, it will offset the time from 3pm to the next Monday 9am (2520 min).

2. If it is weekday and greater than 6pm, calculate the time from 9am (540 min) of the second date.

3. If is normal hours and span more than one day then offset by 15 hours (900 min)

4. If both date are on the same day then just calculate the difference between the two date.