Calculating number of hours between 2 start and finish times on su

  • Thread starter Thread starter Ammo
  • Start date Start date
A

Ammo

Hi all,

I have a sub form that has 'VisitStartTime' and 'VisitFinishTime' fields
that allows the user to enter times in date format. I also have another field
I inserted called 'HoursComplete'. My query is, how do I enter a formula for
the 'HoursComplete' field that calculates and displays the hours and minutes
between the 'VisitStartTime' and 'VisitFinishTime' fields?

Many Thanks

Ammo
 
You could use the DateDiff( ) function to determine the number of minutes
between the start and end date, then divide that by 60.

HoursComplete: =DateDiff("n", [VisitStartTime], [VisitEndTime]) / 60

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
It all starts with the data...

Regardless of how the fields might be formatted for display purposes, what
is the underlying data type of those two fields?

"How" you calculate the difference depends on how the data is stored.

Note that it is rarely necessary or a good idea to store a calculated value
(e.g., [HoursComplete]) when you can use a query to calculate the value
dynamically.

Take a look at the DateDiff() function in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks guys, but how do I return both the hours and minutes between the 2
values. For example:

VisitStartTime: 10:00
VisitEndTime: 12:30

The DateDiff() formula you provided after dividing by 60 will return 2.5
hours, however I would like access to covert the 0.5 hours to 30 minutes. On
calculator this can only be done by deducting the 2 hours and then
multiplying the 0.5 hours remaining by 60.

Any function able to do the above?

Cheers

Ammo

Dale Fye said:
You could use the DateDiff( ) function to determine the number of minutes
between the start and end date, then divide that by 60.

HoursComplete: =DateDiff("n", [VisitStartTime], [VisitEndTime]) / 60

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Ammo said:
Hi all,

I have a sub form that has 'VisitStartTime' and 'VisitFinishTime' fields
that allows the user to enter times in date format. I also have another field
I inserted called 'HoursComplete'. My query is, how do I enter a formula for
the 'HoursComplete' field that calculates and displays the hours and minutes
between the 'VisitStartTime' and 'VisitFinishTime' fields?

Many Thanks

Ammo
Click to expand...
Click to expand...
 
If all you want is to display 2:30 (and not use it in a calculation), you
can take advantage of the fact that Access stores times as a fraction of a
day, and there are 1440 (24 * 60) minutes in a day:

Format(DateDiff("n", [VisitStartTime], [VisitEndTime])/1440, "hh:nn")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ammo said:
Thanks guys, but how do I return both the hours and minutes between the 2
values. For example:

VisitStartTime: 10:00
VisitEndTime: 12:30

The DateDiff() formula you provided after dividing by 60 will return 2.5
hours, however I would like access to covert the 0.5 hours to 30 minutes.
On
calculator this can only be done by deducting the 2 hours and then
multiplying the 0.5 hours remaining by 60.

Any function able to do the above?

Cheers

Ammo

Dale Fye said:
You could use the DateDiff( ) function to determine the number of minutes
between the start and end date, then divide that by 60.

HoursComplete: =DateDiff("n", [VisitStartTime], [VisitEndTime]) / 60

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Ammo said:
Hi all,

I have a sub form that has 'VisitStartTime' and 'VisitFinishTime'
fields
that allows the user to enter times in date format. I also have another
field
I inserted called 'HoursComplete'. My query is, how do I enter a
formula for
the 'HoursComplete' field that calculates and displays the hours and
minutes
between the 'VisitStartTime' and 'VisitFinishTime' fields?

Many Thanks

Ammo
Click to expand...
Click to expand...
Click to expand...
 
Hi,

I don't suppose you know how I can sum the total HoursComplete field for all
records entered in the sub form.

I tried adding a new field in the subform and entered the following in the
control source:

=Sum([HoursCompleted])

However I keep receiving an error message in form view. Many thanks.

KR

Ammo

Douglas J. Steele said:
If all you want is to display 2:30 (and not use it in a calculation), you
can take advantage of the fact that Access stores times as a fraction of a
day, and there are 1440 (24 * 60) minutes in a day:

Format(DateDiff("n", [VisitStartTime], [VisitEndTime])/1440, "hh:nn")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ammo said:
Thanks guys, but how do I return both the hours and minutes between the 2
values. For example:

VisitStartTime: 10:00
VisitEndTime: 12:30

The DateDiff() formula you provided after dividing by 60 will return 2.5
hours, however I would like access to covert the 0.5 hours to 30 minutes.
On
calculator this can only be done by deducting the 2 hours and then
multiplying the 0.5 hours remaining by 60.

Any function able to do the above?

Cheers

Ammo

Dale Fye said:
You could use the DateDiff( ) function to determine the number of minutes
between the start and end date, then divide that by 60.

HoursComplete: =DateDiff("n", [VisitStartTime], [VisitEndTime]) / 60

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Hi all,

I have a sub form that has 'VisitStartTime' and 'VisitFinishTime'
fields
that allows the user to enter times in date format. I also have another
field
I inserted called 'HoursComplete'. My query is, how do I enter a
formula for
the 'HoursComplete' field that calculates and displays the hours and
minutes
between the 'VisitStartTime' and 'VisitFinishTime' fields?

Many Thanks

Ammo
Click to expand...
Click to expand...
Click to expand...
 
Back
Top