HELP: Date Calculations

[Guest]

What formula do I need to use to calculate the number of minutes that have
elapsed between a START TIME and an END TIME.

I.E.

START TIME END TIME # of MINUTES
2:00 AM 3:45 AM 105

 

[Allen Browne]

Try the DateDiff() function:
DateDiff("n", [Start Time], [End Time])

More details:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

 

[Joseph Meehan]

What formula do I need to use to calculate the number of minutes that
have elapsed between a START TIME and an END TIME.

I.E.

START TIME END TIME # of MINUTES
2:00 AM 3:45 AM 105

What format is the data in? It is a text field? Did you know Access
has a time filed?

In any case if you don't use the Access date-time filed type you will
need to convert what you have into a numeric type before doing the
computation. If it extends past midnight that will cause additional
problems.

Assuming no midnight cross overs and no need to go beyond minutes (no
seconds) then I suggest converting everything to minutes with

AM = 720 minutes
hours = 60 minutes

Do your subtraction and then convert back.

 

[John Vinson]

What formula do I need to use to calculate the number of minutes that have
elapsed between a START TIME and an END TIME.

I.E.

START TIME END TIME # of MINUTES
2:00 AM 3:45 AM 105

DateDiff("n", [Start Time], [End Time])

will do it. Despite the name, DateDiff can calculate differences in
any unit from seconds to years.

Note that it's "n" for miNutes, and "m" for Months.

John W. Vinson[MVP]

 

[Guest]

Hi, your post was helpful to me. However, I am having difficulty displaying
the query results in a form that I have created.

I have the following fields:
[StartTime], [EndTime], and [TourLength]

I succesfully created a query that gives TourLength in minutes. However, in
the form, I do not know how to link the [TourLength] field to the query
results.

Thanks,
Diana

Try the DateDiff() function:
DateDiff("n", [Start Time], [End Time])

More details:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What formula do I need to use to calculate the number of minutes that have
elapsed between a START TIME and an END TIME.

I.E.

START TIME END TIME # of MINUTES
2:00 AM 3:45 AM 105

 

[Rick B]

If you calculated it in the query, then you gave it a name. Just pull that
name from the field list in your report.

Your query probably has a column with something like....

TourLength: DateDiff("n", [Start Time], [End Time])


If so, just pull the "TourLength" to your form.


--
Rick B

Hi, your post was helpful to me. However, I am having difficulty displaying
the query results in a form that I have created.

I have the following fields:
[StartTime], [EndTime], and [TourLength]

I succesfully created a query that gives TourLength in minutes. However, in
the form, I do not know how to link the [TourLength] field to the query
results.

Thanks,
Diana

Try the DateDiff() function:
DateDiff("n", [Start Time], [End Time])

More details:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What formula do I need to use to calculate the number of minutes that have
elapsed between a START TIME and an END TIME.

I.E.

START TIME END TIME # of MINUTES
2:00 AM 3:45 AM 105

 

[Guest]

Hello,

In reference to what Rick B wrote, how does one "pull" a field name into a
form? I tried linking my query result [Minutes] to the field name
[TourLength] found in my data-entry form by inserting
"=[QueryTourLength]![Minutes]" into ControlSource (found in the properties of
the [TourLength] field). However, this approach did not work.

I would like to directly add the [Minutes] field by using the "Field List"
tool. However, this tool contains only a list of fields from one table. Is
there a way to add fields to this list from other tables and queries? Maybe
this will solve my problem. Thank you.

Diana

If you calculated it in the query, then you gave it a name. Just pull that
name from the field list in your report.

Your query probably has a column with something like....

TourLength: DateDiff("n", [Start Time], [End Time])


If so, just pull the "TourLength" to your form.


--
Rick B

Hi, your post was helpful to me. However, I am having difficulty displaying
the query results in a form that I have created.

I have the following fields:
[StartTime], [EndTime], and [TourLength]

I succesfully created a query that gives TourLength in minutes. However, in
the form, I do not know how to link the [TourLength] field to the query
results.

Thanks,
Diana

Try the DateDiff() function:
DateDiff("n", [Start Time], [End Time])

More details:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What formula do I need to use to calculate the number of minutes that have
elapsed between a START TIME and an END TIME.

I.E.

START TIME END TIME # of MINUTES
2:00 AM 3:45 AM 105