Calculating the number of days from dates 2

[piovesan]

Hi all;

I want to calculate the number of days it took someone to respond to a problem. I have a Distributed date APPRDTTM (which is the date the problem was sent to the person) and then a start date STARTDTTM which is when the task was started by that person. I want to know the number of days between the APPRDTTM and the STARTDTTM.

Thanks
Theresa

 

[lespaul]

Try the DateDiff function:

SELECT DateDiff('n', Apprdttm, StartDttm) AS mDateDiff

you will need to select the correct first argument to the function, I'm pretty sure that the 'n' will return the number of months, 'd' for days, 'y' for years, etc.

Leslie

 

[sfreeman]

If your table ios called tblWork, and you have two date fields, [APPRDTTM] and [STARTDTTM], you can find the number of days in a calculated fcield in a query like this:

SELECT
tblWork.APPRDTTM
, tblWork.STARTDTTM
, DateDiff("d",[apprdttm],[startdttm])

AS
NumDays

FROM
tblWork;

Dataset would look something like:

APPRDTTM STARTDTTM NumDays
1/1/1999 2/1/1999 31
3/2/1998 4/1/1998 30
7/1/1997 1/1/2005 2741

HTH

Sam_F
"90% of the problem is asking the right question.

 

[piovesan]

OK, I have set up a new field called responsetime and I made it a number (decimal) field. I want to update that field with the # of days it took to respond.....How can I do this in an update query??

 

[sfreeman]

Are you sure you want an update query? Just add a calculated field per the example above, and it wil populate your # Days field.

Sam_F
"90% of the problem is asking the right question.

 

[piovesan]

OK, that sounds good, but how do I set up a calculated field....you are working with a beginner here....I have a field called responsetime, and I set it to be a decimal number??? do I then make that field a "look up" to a query with what you entered above?? or am I setting the field up incorrectly in the first place??

Sorry!!
Theresa

 

[lespaul]

you shouldn't store calculated fields in your tables (breaks third normal form, see link for more details), you should only store the raw data needed to perform the calculation and do the calculation in a query as shown above. As a new database designer you should read 'The Fundamentals of Relational Database Design'.

Leslie

 

[sfreeman]

No need to apologize, we all started somewhere :-B

I agree with the above. Do not add this value to be stored in your table. There is no need to do so from your description.

Presummably you have a form, and that form is based upon an underlying query that includes at least two fields, StartDate [named "apprdttm"] and EndDate [named startdttm"].

Just add another field in the query. Call it anything you want, something like "NumDays". In the query design window the field name should look something like:

NumDays: DateDiff("d",[apprdttm],[startdttm])

Then, in your form, add a text field and set it's data source to "NumDays".


Sam_F
"90% of the problem is asking the right question.

 

[piovesan]

Thank you so much Sam!!! That worked!!!

Out of curiosity, if I had my dates with times included....which I do...and I want my calculation to go to two digits after the decimal (for example I want to know if it took them 2.24 days to get to the job....How do I adjust my DateDiff to do that??

Thanks again!!!!

 

[PHV]

Try this:
NumOfDays: Format([End date] - [Start date], "#.##")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[piovesan]

That works too! Thanks so so much! Works great!

 

[sfreeman]

Looks pretty good... one minor concern....

I assume that this is a management tool to track performance of employees, and it MIGHT be a bit misleading in the below circumstance.

Let's say employee A gets a task on Monday morning 9:00 AM, and starts it Wednesday 9:00 AM, about two days.

Employee B gets a task on Friday afternoon at 4:00 PM starts it at 9:00 AM on Monday morning, about 3 hours.

With the above, Employee A took 2.0 days and Employee B took 2.7 days.

May need to consider using work days only.

HTH

Sam_F
"90% of the problem is asking the right question.

 

[piovesan]

You are right. But is there a way of doing this??

 

[lespaul]

FAQ181-261 has an example on how to do that including holidays.



Leslie

 

[sfreeman]

Not to further complicate this, but now that I think about it, to be precise you cannot just count business 'days', but would need to reference only business 'hours'.

Why?

Same argument as above.

Emp A assigned a task Monday at 9:00 AM - starts it at 11:00 AM - total time 2 hours.

Emp B assigned a task Thursday at 4:00 PM - starts it at 9:00 AM on Friday - total time 17 hours [but he really spent the same amount of time getting to the task - 2 hours].




Sam_F
"90% of the problem is asking the right question.

 

[snoopy92211]

I actually have a question that piggybacks off this issue.

I am using the DateDiff function; if the start date is after the end date, the function returns a negative number. Is there any way to In my query either ignore the record (that returns the negative number) or return some special value to let me know that it was negative?

here's my query so far:
SELECT Item.ID, Item.Description, DateDiff("d",[Received],[Submitted]) AS CalDays
FROM Item;

 

[PHV]

Simply play with the IIf function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[snoopy92211]

Thanks! I was actually going to do that, but I suppose my real question is how would I refer to the negative value?

 

[PHV]

IIf([Received]>[Submitted], negative stuff here, positive stuff here)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244