Question Calculating Hours and Time

[majid.pervaiz]

Dear Expert Friends,

I would really appreciate if you guys can support me on the below.

I have a database to manage the department request and each type of request has different turn-around-time (Circulars - 6 hours (360 min), Policy - (960 mins), Form (420 mins), Process (new - 600 mins and amendments - 420 mins).

In my database I have a field Req_Date_Time and Resp_Date_Time which are ultimately appearing on a form. Users will enter date and time of a request received example (10-05-2016 10:30) and based on the type of request when they will enter Resp_Date_Time, I want to calculate total hours and minutes taken in the database field (Total_Time_Taken).

Kindly note that working hours are Sunday to Thursday (07:15 am - 02:30 PM).

I am currently managing an excel sheet and it's working perfect with all the above scenarios but I want to migrate to access.

a sample database is also attached, please open taskslist form, once the task is published it will be moved to a form completed.

Appreciate your support.

 

[Minty]

Have a look at the datediff() function. http://www.techonthenet.com/access/functions/date/datediff.php

 

[majid.pervaiz]

I saw the above mentioned link it is only calculating hours difference but my requirement is bit complex...

 

[Minty]

You can get it to calculate days, hours, minutes or even seconds, then simply convert the result to the output format you require. What do you need it to do ? Can you explain in simple terms.

 

[majid.pervaiz]

Hi Minty - I need total time taken to handle a request excluding weekends and working hours

 

[Minty]

Ah - That is a little more complex. You can use a function to work out the real time differences.
Have a look here http://www.access-programmers.co.uk/forums/showthread.php?t=116721

 

[majid.pervaiz]

see the attached sample database... surely you will know the complete requirement.

thanks

 

[Minty]

I've not got the time to program it for you - the code in the linked thread will do almost exactly what you need. You will learn much more by examining it and adapting it to your needs.

 

[The_Doc_Man]

majid, we often offer advice, but complex programming services through this forum are strictly at the convenience of the members. Minty's response makes it clear. I'll make it more explicit. We offer advice but you need to follow up by reading the answers we give and that includes looking at the links.

Now, as to time management, the question is always going to be, what data are you capturing (and how are you capturing it) to do time computations? If you have a task, you can capture start-time and end-time easily in date/time variables using the Now() function or to do manual time input. It is possible to leave it this way or to convert this to something else. (Hint: don't mix those two methods - either always take a timestamp or always take manual input; otherwise it gets confusing.)

The difference between two date/times in access is a time interval (which also should be stored in a date/time variable). This time interval, like all Access date/times, is in units of days and fractions thereof. You can get a meaningful number out of that interval using CDbl(date-var) to convert it to double-floating, then you can manipulate that to convert it to seconds, minutes, or hours by multiplying by the correct conversions. If you have intervals, you can add the intervals and just convert the totals. There are ways to convert that after-the-fact to various formats, but you can even do this:

string-var = Format( date/time-var, "hhh:mm:ss" )

to convert an interval into hours, minutes, and seconds. From this information, you should be able to manage your time accumulations.