Calculate response time based on 9-5:30 day

[0wen]

I would like to calculate response time

I have two fields "Opened Date" and "Opened Time" from these fields I would like to automaticaly update "Due Date" and "Due Time" with a 4hour response time. However if the call is logged at 5pm I want the response time to be 12:30pm tomorrow as we close at 5:30pm.

Open 9-5:30 Monday - Friday

Any ideas how I could do this?

 

[0wen]

Any one got any ideas?

I have created a example database to work this out

This is the Log Table

And below is the Priority Table

This is the form that would be filled in.

First thing I would like to work out is how I can add the number of hours in the priority e.g. 4 to the data and time it was opend as in the example above.

I have attached a copy of the database in 2007 & 2003 format

Download it here

 

[Extra_Cover]

DateAdd will work using hours. I've taken this from a query - hope you can follow.

Time: Format(Now(),"dd/mm/yy hh:mm")

This gives you the current date and time.

Timeplus: Format(DateAdd("h",16,[Time]),"dd/mm/yy hh:mm")

You can replace the '16' with the hours in your priority table. If it after 5 'o' clock add 12 hours to it.

 

[0wen]

Thanks for your reply, I have had a read through the function and I can get it to work from a set date

DateAdd("m", 1, "31-Jan-95")

When I use your code

Format(DateAdd("h",16,[Time]),"dd/mm/yy hh:mm")

It cannot find time so I change this to

Format(DateAdd("h",16,[Log.Opened Date]),"dd/mm/yy hh:nn")

but it said it can't find Log.Opened Date

Am I missing something or am I going about it the wrong way?

 

[JANR]

Format(DateAdd("h",16,[Log[COLOR=red]][/COLOR].[COLOR=red][[/COLOR]Opened Date]),"dd/mm/yy hh:nn")

You forgot some squarebrakets

JR

 

[0wen]

Did not know I needed them, sorry only started using access for the first time a month ago.

I get and error Could not find field 'Log].[Opened Date'

i tried adding two more [] but not really sure what im doing.
Format(DateAdd("h",16,[[Log].[Opened Date]]),"dd/mm/yy hh:nn"

 

[JANR]

Either this:

Format(DateAdd("h",16,[Log[COLOR=black]].[[/COLOR]Opened Date]),"dd/mm/yy hh:nn")

Or this:

Format(DateAdd("h",16,([Log[COLOR=black]].[[/COLOR]Opened Date])),"dd/mm/yy hh:nn")

The [log] part is your table and [opened Date] is you field in the log table, the second code i use () around the statement to disambigiue the refrence.

JR

 

[0wen]

Hi

Tried those however still get the same error Could not find field 'Log].[Opened Date'

Perhaps try it your self zip it attached to the original post.

Thanks for help so far

 

[Extra_Cover]

Open the query in design view - the field 'Response Time' contains the calculation you need.

 

[0wen]

Thanks,

I have taken a look and understand what you have done, I have set the ResponseTime field to =[QryResponseTime]![Responce Time] however I get #Name?. Could this be because the query runs before the priority is set?