Question Calculating Turn Around Time (1 Viewer)

The database I am designing needs to be able to calculate the turn around time (T.A.T.) on samples being processed.

Each record in the main table will have a lot of data for each sample, but the times that are being recorded are in three fields: DateIn (Will autopopulate when the record is created using =Date()), TimeIn (Autopopulates with =Time()), and DateTimeOut (Autopopulated as a General Date format using =now() when the record is modified).

We calculate T.A.T. (in hours) = DateTimeOut - (DateIn + TimeIn) (excluding hours outside of 8a-5p M-F)

Would this be better done through a calculated field? Query? SQL?

Any direction and nudge towards the right coding would be appreciated!

This would be best done by storing the data correctly. Why is DateTimeOut one field, but DateIn and TimeIN two? If you store them together you don't have to merge them later on.

To hack around this you would build a query to generate the calculated value using the Date functions here: https://www.techonthenet.com/access/functions/index.php

plog said:

This would be best done by storing the data correctly. Why is DateTimeOut one field, but DateIn and TimeIN two? If you store them together you don't have to merge them later on.

Click to expand...

Thanks for the link, I'll get looking into that.

As for the data storage, That's how we had it setup in the previous system (which has been phased out, not an access database, just a plain spreadsheet). I don't have a problem changing up the data storage so it is all the same format

If you need to also allow for Public Holidays if your in / out dates can go over a single date, you may well need to build a function.

We have a target TAT individually stored per customer, so I have a function to calculate the ETA date on the fly based on a start date.

It can become a little more challenging when you include times, due to crossing dates at midnight.

Minty said:

If you need to also allow for Public Holidays if your in / out dates can go over a single date, you may well need to build a function.

We have a target TAT individually stored per customer, so I have a function to calculate the ETA date on the fly based on a start date.

It can become a little more challenging when you include times, due to crossing dates at midnight.

Click to expand...

Our current model accounts for company holidays (which I figure I can add in every year using another table that provides the data for a function)

I'm wondrign if the function you use couldn't be modified for our use. Out TAT can range anywhere from 00:01 (hh:mm) for samples that require minimal testing and we spend more time doing paperwork for, up to trouble samples that can span the course of 48:00+

To find the time (in whatever increments you want) between date/times you use the DateDiff function:

https://www.techonthenet.com/access/functions/date/datediff.php