subtracting two date/time fields to calculate hours 1

[snkeyes]

I have not worked in Access for quite some time, but here is what I am attempting to do.

I have two date/time fields that I need to subtract for each user ID and then total them for a specific site.

Example:

userid location date/time_out - date/time_in =(need hours)

xxxxx xxx mm/dd/yyyy hh:nn ""
____________________________________________________________

total hours for site

I thought about changing the time format to military time, but that would still leave the date out of the equation. I tried the expression builder, and apparently I do not recall how it works.

Any help or tips would be greatly appreciated.

BTW, I am using access 2000

Thanks for your time

 

[dhookom]

In the detail section, use:
=DateDiff("n",TimeIn, TimeOut)/60
In a group or report footer (or header) use:
=Sum(DateDiff("n",TimeIn, TimeOut))/60


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[snkeyes]

Ok, forgive this basic quesion.

I changed the timein/timeout fields to the correct ones that I have, but how do I display the results? Is datediff a built in function?

Again, the expression builder is not my strong point.

TIA

 

[dhookom]

Use the =DateDiff(...) as the control source of a text box in the report.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[dhookom]

Sorry,
I should have confirmed that DateDiff() is a function in Access.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[snkeyes]

Thanks for the simplification.

I am now getting the displayed time, except it is incorrect. Does the DateDiff function account for differences between days, and AM/PM? Could field formatting cause it to be off?

Any ideas on the miss calcuation?

Thanks

 

[dhookom]

You haven't provided a sample of your data or if the times are on the same date or times are greater than 24 hours or other significant info.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[snkeyes]

Sorry about being a little vague. It is a sign-in/sign-out sheet, and the times can be greater than 24 hours with different dates.

Here is some of the sample data:

userid site dateout datein hours

xxx xx 06/29/04 6:00 AM 07/01/04 5:00 PM 0:00

It seems that all of the times that are greater than 24 hours are returning 0:00. I played around with the the formatting, added days into the equation and it returned some ridiculous amount of days (something like 22) for the entry above. Also, I think the AM/PM situation is throwing the calculation off on same day entries.

Any ideas on how to calculate just the hour and minute difference with the information given above? What are the properties of the DateDiff function?

Again, thanks for your time and effort.

 

[dhookom]

The following should provide the total hours (with decimal) between the two field values:
=DateDiff("n",DateIn, DateOut)/60
You can't display this with a "date/time" type format since it has been converted to a number of hours. Set the format to Standard with the desired number of decimal places.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[snkeyes]

That is what I was looking for. I tried =DateDiff("n",DateIn, DateOut)/1440 and the hours were correctly calculated with short time format, but it would not account for the difference in days. I then tried all kinds of preset time formats, along with a couple of make-shift ones that I came up with, to no avail.

I appreciate your time.

Have a safe and Merry Christmas along with a prosperous New Year.

Seth

 

[MichaelRed]

DateIn = #12/22/04 8:23:15 AM#
DateOut = Date

? Format(DateIn, "Short date") & ";     " & Format(DateDiff("n", DateIn, DateOut)/1440, "hh:n:ss")
12/22/2004;     15:37:00

MichaelRed