Calculate Time Difference

[Guest]

Hi,

I have two fields of the date/time data type on my form:

StartDate/Time and EndDate/Time

I'd like to add a field next to those two on the form which automatically
calculates the amount of time elapsed between the two, expressed in hours and
quarter hours (e.g., 2.25 hrs.).

What function can I use in the calculated field to do this?

Many thanks,

 

[Douglas J. Steele]

Use DateDiff to calculate the different in minutes, then do arithmetic on
the minutes to get it in hours and quarter hours.

 

[fredg]

Hi,

I have two fields of the date/time data type on my form:

StartDate/Time and EndDate/Time

I'd like to add a field next to those two on the form which automatically
calculates the amount of time elapsed between the two, expressed in hours and
quarter hours (e.g., 2.25 hrs.).

What function can I use in the calculated field to do this?

Many thanks,

Assume values in the fields as 4 PM and 6:30 PM.

1) To calculate the difference in minutes between 2 Fields:
= DateDiff("n",[Field1],[Field2])
Result is 135

To calculate the difference and display the difference in hours plus a
percentage part of an hour:
= DateDiff("n",[Field1],[Field2])/60
Result is 2.25

2) The above calculation can be done in an unbound control on a form
or in a report, or in a query expression.

3) The calculated result need not be stored in a table. Whenever you
need the result of the calculation, simply calculate it, using the
above expression.

 

[Guest]

Hi Fred,

Ok, I've created an unbound control (textbos) on my form and added the
function = DateDiff("n",[StartDateTime],[EndDateTime])/60.

I am getting a result of 0. Could this be because I use the double-click
event in the StartDateTime and EndDateTime fields? The double-click event is
coded with NOW() and populates the fields with the current date and time.

Do I need to adjust the function to accommodate this?

Many thanks,

Hi,

I have two fields of the date/time data type on my form:

StartDate/Time and EndDate/Time

I'd like to add a field next to those two on the form which automatically
calculates the amount of time elapsed between the two, expressed in hours and
quarter hours (e.g., 2.25 hrs.).

What function can I use in the calculated field to do this?

Many thanks,

Assume values in the fields as 4 PM and 6:30 PM.

1) To calculate the difference in minutes between 2 Fields:
= DateDiff("n",[Field1],[Field2])
Result is 135

To calculate the difference and display the difference in hours plus a
percentage part of an hour:
= DateDiff("n",[Field1],[Field2])/60
Result is 2.25

2) The above calculation can be done in an unbound control on a form
or in a report, or in a query expression.

3) The calculated result need not be stored in a table. Whenever you
need the result of the calculation, simply calculate it, using the
above expression.

 

[Guest]

Oops - so sorry, Fred! It worked perfectly. When I ran the test, the start
time and end time were the same, that's why I got a zero (duh!). So thanks
very much for your help. I do have one more question, though.

How do I change the time display to only two decimal places?

Thanks again,
Rosemary

Hi,

I have two fields of the date/time data type on my form:

StartDate/Time and EndDate/Time

I'd like to add a field next to those two on the form which automatically
calculates the amount of time elapsed between the two, expressed in hours and
quarter hours (e.g., 2.25 hrs.).

What function can I use in the calculated field to do this?

Many thanks,

Assume values in the fields as 4 PM and 6:30 PM.

1) To calculate the difference in minutes between 2 Fields:
= DateDiff("n",[Field1],[Field2])
Result is 135

To calculate the difference and display the difference in hours plus a
percentage part of an hour:
= DateDiff("n",[Field1],[Field2])/60
Result is 2.25

2) The above calculation can be done in an unbound control on a form
or in a report, or in a query expression.

3) The calculated result need not be stored in a table. Whenever you
need the result of the calculation, simply calculate it, using the
above expression.

 

[Guest]

Oops again -- I got it to display in two decimal places by changing the
format in the text box's properties.

I am all set -- it works great.

Thanks so much, Fred!

Rosemary

Oops - so sorry, Fred! It worked perfectly. When I ran the test, the start
time and end time were the same, that's why I got a zero (duh!). So thanks
very much for your help. I do have one more question, though.

How do I change the time display to only two decimal places?

Thanks again,
Rosemary

Hi,

I have two fields of the date/time data type on my form:

StartDate/Time and EndDate/Time

I'd like to add a field next to those two on the form which automatically
calculates the amount of time elapsed between the two, expressed in hours and
quarter hours (e.g., 2.25 hrs.).

What function can I use in the calculated field to do this?

Many thanks,

Assume values in the fields as 4 PM and 6:30 PM.

1) To calculate the difference in minutes between 2 Fields:
= DateDiff("n",[Field1],[Field2])
Result is 135

To calculate the difference and display the difference in hours plus a
percentage part of an hour:
= DateDiff("n",[Field1],[Field2])/60
Result is 2.25

2) The above calculation can be done in an unbound control on a form
or in a report, or in a query expression.

3) The calculated result need not be stored in a table. Whenever you
need the result of the calculation, simply calculate it, using the
above expression.