Calculate Time Difference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I see there are many entries and answers to this question. However, I don't
seem to understand it where I can make it work. I am a novice so please stay
with me on this.

On my form I have a IN and OUT field to represent time in and time out. On
my table, these fields are set up as Date/Time fields. My input mask and
format are short time.

I want to subtract the IN time from the OUT time in a report and at the
bottom of the report have it total(if possible). Do I need to do a query?
Please explain in simple terms what the formula would be and where I would
put it to make it work.

I would appreciate any help you folks can give.

Thanks!!
 
Check help on DateDiff, you didn't specify the different you are looking for,
so for example if you want to use the different in days, then use in the
field control source

=DateDiff("d",[In Date],[Out Date])

In the Sum field on the buttom, you can write
=Sum(DateDiff("d",[In Date],[Out Date]))
 
Doug,

For the difference in minutes, the expression is:

=DateDiff("n",[In],[Out])

Sprinks
 
I'm glad to see that someone else wanted help with this; I just learned
about DateDiff this morning!

I am using DateDiff to calculate the duration of videoconferences (have to
keep track of the time, because of funding) - in minutes. I put together a
query, and put this formula into a field row:

Duration: DiffDiff("n",[StartTime], [EndTime])

I'd like to use the query containing that calcuation in a report. How can I
format that duration - again, given in minutes - to look like real time?

I used a report wizard to get this report started; for the "Duration" text
box, I tried using this as the control source:
ControlSource = [Duration] \ 60 & Format[Duration] Mod 60, "\:00")

I get an #ERROR for that field. What am I doing wrong? The minutes are
calculated correctly, so it's something in the formatting.

Thanks for any help!

Sprinks said:
Doug,

For the difference in minutes, the expression is:

=DateDiff("n",[In],[Out])

Sprinks

Doug_C said:
Hello,

I see there are many entries and answers to this question. However, I
don't
seem to understand it where I can make it work. I am a novice so please
stay
with me on this.

On my form I have a IN and OUT field to represent time in and time out.
On
my table, these fields are set up as Date/Time fields. My input mask and
format are short time.

I want to subtract the IN time from the OUT time in a report and at the
bottom of the report have it total(if possible). Do I need to do a query?
Please explain in simple terms what the formula would be and where I
would
put it to make it work.

I would appreciate any help you folks can give.

Thanks!!
Click to expand...
Click to expand...
 
If you're using a formula as the control source, you need to include an
equal sign in front: your ControlSource in Design mode would need to be

=[Duration] \ 60 & Format[Duration] Mod 60, "\:00")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Maureen Smith said:
I'm glad to see that someone else wanted help with this; I just learned
about DateDiff this morning!

I am using DateDiff to calculate the duration of videoconferences (have to
keep track of the time, because of funding) - in minutes. I put together a
query, and put this formula into a field row:

Duration: DiffDiff("n",[StartTime], [EndTime])

I'd like to use the query containing that calcuation in a report. How can I
format that duration - again, given in minutes - to look like real time?

I used a report wizard to get this report started; for the "Duration" text
box, I tried using this as the control source:
ControlSource = [Duration] \ 60 & Format[Duration] Mod 60, "\:00")

I get an #ERROR for that field. What am I doing wrong? The minutes are
calculated correctly, so it's something in the formatting.

Thanks for any help!

Sprinks said:
Doug,

For the difference in minutes, the expression is:

=DateDiff("n",[In],[Out])

Sprinks

Doug_C said:
Hello,

I see there are many entries and answers to this question. However, I
don't
seem to understand it where I can make it work. I am a novice so please
stay
with me on this.

On my form I have a IN and OUT field to represent time in and time out.
On
my table, these fields are set up as Date/Time fields. My input mask and
format are short time.

I want to subtract the IN time from the OUT time in a report and at the
bottom of the report have it total(if possible). Do I need to do a query?
Please explain in simple terms what the formula would be and where I
would
put it to make it work.

I would appreciate any help you folks can give.

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
Doug,
Thanks for your reply. . .I've tried it both with the equal sign, and
without.

Same result, both ways.
 
Rename the textbox you're trying to put the value in. Since both the textbox
and the field in the underlying recordset are named Duration, Access is
likely getting confused. I always rename all of my textbox controls to have
a prefix of txt.

If you like, you can automate this process:

Dim ctlCurr As Control

For Each ctlCurr In Me.Controls
If ctlCurr.ControlType = acTextBox Then
If Left$(ctlCurr.Name, 3) <> "txt" Then
ctlCurr.Name = "txt" & ctlCurr.Name
End If
End If
Next ctlCurr


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Maureen Smith said:
Doug,
Thanks for your reply. . .I've tried it both with the equal sign, and
without.

Same result, both ways.


Douglas J Steele said:
If you're using a formula as the control source, you need to include an
equal sign in front: your ControlSource in Design mode would need to be

=[Duration] \ 60 & Format[Duration] Mod 60, "\:00")
Click to expand...
Click to expand...
 
I'll give it a go on Monday, when I get back to work. My at-home verison of
Access is 97, so I can't easily transfer stuff between work & home. I'll
post to let you know how it goes.

Thanks!
 
Good afternoon, all!

I changed the name of the textbox, and double-checked my stuff - I think I
was missing a paranthesis - but whatever I did, it worked a charm!

Now all I need to do is to figure out how to add all those minutes and total
them, formatting that total to look like real time.

I have a detail footer and would like to enter that sum total there. Is
there a way to format the Sum of Duration?
 
Back
Top