Date and Time Diff

[Vassago]

I've searched through the forum and found the answer to one or the other, but not both. This is my problem. I have a table with four fields

Date Entered
Time Entered
Date Resolved
Time Resolved

What I need to do is come up with a formula to find how many days it took to resolve from the date and time it was entered.

Is there a way to combine the Date and Time Entered compare it to the Date and Time Recieved to come up with the number of days between the two dates in such a way as to say for example:

date entered = 10/15/02 (American)
time entered = 6:00 am
date resolved = 10/17/02
time resolved = 12:00 pm
Difference = 2.25 days

I need a formula in a query to produce the Difference.

Please help...ANYONE!!!

I need this today!

thanks,

 

[WayneRyan]

vassago,

Dim intHours As Integer
intHours = DateDiff("h", de & " " & te, dr & " " & tr)

This worked when I just tested it.
de = date entered
te = time entered
dr = date resolved
tr = time resolved

It gave 54 hours

days = Int(intHours/24)
Fraction = intHours - Int(intHours/24)*24

I hope someone comes up with something more elegant,
but since its due today ...

Wayne

 

[Vassago]

That would be wonderful, except it's not working right for me, it's only doing the hours and forgetting the days...

I'm using it in a query, it gave me 6 hours for that....

If you can figure it out please let me know...

Thanks for your help,

 

[Vassago]

My Mistake, it works great, I just overlooked a few records.

Thanks a lot, you have always been a great help

 

[Vassago]

One more question...

How do I get the formula to show in format "#.00" without converting it to a string.

I want it to stay an integer, but while it is an integer, it won't show the decimal for an overlapse of 2 days and 6 hours, it just shows 2 instead of 2.25.

Please help me...::biting fingernails::

 

[WayneRyan]

vassago,

days = Int(intHours/24)
Fraction = Days + (intHours - Int(intHours/24)*24)/24

Where Fraction is a double,

days and intHours are integer or long.

Wayne

 

[p patel]

Hi
I have a similar query - is someone able to tell me how to work this for excel? I have the same 4 fields and need to find how long it took from time entered to time resolved?
Please help, i know this is an access forum but if someone can advise that would be great.

 

[mjreiman]

My question is I am running a query for calculating clock in and out times for employees and want it to display as a decimal instead of a single integer using a query and not in visual basic. Like if clockin time is 10:00AM and clockout time is 06:30PM what would the query header be for the field? I used Hours: DateDiff("?",[clockin],[clockout])

 

[The_Doc_Man]

Actually, in Excel you can do this by copying the date/time difference to another field (or computing the difference in another field) and making the format numeric rather than date/time.

The trick with date and time in Access is to understand how they are stored. Dates are integer numbers of days since the reference date. I think day 1 is 1 Jan 1900. Times are fractions of a 24-hour day. Midnight is 0.000, while Noon is 0.500, etc.

Add date to time and you get a DOUBLE that is days and fractions since midnight of the reference date. Put that number through a DATE() function, though, and you get gibberish because Access "implies" that the base date is always there. Look at doing things like splitting out the days via truncation, then use the remainder with a custom time format like "hhh:nn" or something like that.