Time calculation

K

KARL DEWEY

What about ---
DateAdd("h",[Your value], [YourFieldName])
h - hours
d - days
n - minutes
w - weeks
m - months
q - quarters
yyyy - years
 
R

Rose

How do you get the minutes to show as well. For instant 2 and a half hours.
--
Rose


KARL DEWEY said:
What about ---
DateAdd("h",[Your value], [YourFieldName])
h - hours
d - days
n - minutes
w - weeks
m - months
q - quarters
yyyy - years
--
KARL DEWEY
Build a little - Test a little


Rose said:
Is there away to minus time in hours beyond [CheckOut]-[CheckIn]
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --

Stay: "Hours: " & DateDiff("h",[CheckIn],[CheckOut]) & " Minutes: " &
((DateDiff("h",[CheckIn],[CheckOut]))*60)-DateDiff("n",[CheckIn],[CheckOut])

--
KARL DEWEY
Build a little - Test a little


Rose said:
How do you get the minutes to show as well. For instant 2 and a half hours.
--
Rose


KARL DEWEY said:
What about ---
DateAdd("h",[Your value], [YourFieldName])
h - hours
d - days
n - minutes
w - weeks
m - months
q - quarters
yyyy - years
--
KARL DEWEY
Build a little - Test a little


Rose said:
Is there away to minus time in hours beyond [CheckOut]-[CheckIn]
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

That could lead to erroneous results since DateDiff counts changes in
the increment so going from any time in an hour to any time in the next
hour will get a result of 1 even though it is possible that less than a
minute has elapsed. For example,
DateDiff("h",#10:59:59#,#11:00:01#)
will return one not zero.

I would use something like

Stay: "Hours: " & DateDiff("n",[CheckIn],[CheckOut])\60
& " Minutes: " & DateDiff("n",[CheckIn],[CheckOut])) Mod 60

And if the number of minutes was crucial I might go so far as to use
dateDiff seconds and then divide that by 60 to get the minutes and then
use Mod 60 on the result.


If the poster wanted something like
32:40 to represent 32 hours and 40 minutes then we would need to throw
in the format function to ensure leading zeroes for durations such as
32:07 (32 hours 7 minutes)

Stay: DateDiff("n",[CheckIn],[CheckOut])\60
& ":" & Format(DateDiff("n",[CheckIn],[CheckOut])) Mod 60,"00")



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
R

Rose

Hi John,

Your last calculation worked but I am unable to sum the total time by
members. Your first few calculations look good but I recieve an invalid
syntax error You need to enclose your text data in quotes. Do you know what
I might be doing wrong. Thanks for all your help.
--
Rose


John Spencer said:
That could lead to erroneous results since DateDiff counts changes in
the increment so going from any time in an hour to any time in the next
hour will get a result of 1 even though it is possible that less than a
minute has elapsed. For example,
DateDiff("h",#10:59:59#,#11:00:01#)
will return one not zero.

I would use something like

Stay: "Hours: " & DateDiff("n",[CheckIn],[CheckOut])\60
& " Minutes: " & DateDiff("n",[CheckIn],[CheckOut])) Mod 60

And if the number of minutes was crucial I might go so far as to use
dateDiff seconds and then divide that by 60 to get the minutes and then
use Mod 60 on the result.


If the poster wanted something like
32:40 to represent 32 hours and 40 minutes then we would need to throw
in the format function to ensure leading zeroes for durations such as
32:07 (32 hours 7 minutes)

Stay: DateDiff("n",[CheckIn],[CheckOut])\60
& ":" & Format(DateDiff("n",[CheckIn],[CheckOut])) Mod 60,"00")



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


KARL said:
Try this --

Stay: "Hours: " & DateDiff("h",[CheckIn],[CheckOut]) & " Minutes: " &
((DateDiff("h",[CheckIn],[CheckOut]))*60)-DateDiff("n",[CheckIn],[CheckOut])
Click to expand...
Click to expand...
 
J

John Spencer

Hard to tell, but I am guessing that you are trying to sum the calculated
string.

You need to sum the differences and then use that sum when you build the
string.

Can you post the SQL of your query? (Hint - Menu: View: SQL and copy and
paste)

As for the error, I had an extra closing parenthesis

Stay: "Hours: " & DateDiff("n",[CheckIn],[CheckOut])\60
& " Minutes: " & DateDiff("n",[CheckIn],[CheckOut]) Mod 60

You might want the following, but whether it will work or not depends on the
structure of your query. Are you using a totals (or aggregate) query.

Stay: "Hours: " & SUM(DateDiff("n",[CheckIn],[CheckOut]))\60
& " Minutes: " & SUM(DateDiff("n",[CheckIn],[CheckOut])) Mod 60
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Rose said:
Hi John,

Your last calculation worked but I am unable to sum the total time by
members. Your first few calculations look good but I recieve an invalid
syntax error You need to enclose your text data in quotes. Do you know
what
I might be doing wrong. Thanks for all your help.
--
Rose


John Spencer said:
That could lead to erroneous results since DateDiff counts changes in
the increment so going from any time in an hour to any time in the next
hour will get a result of 1 even though it is possible that less than a
minute has elapsed. For example,
DateDiff("h",#10:59:59#,#11:00:01#)
will return one not zero.

I would use something like

Stay: "Hours: " & DateDiff("n",[CheckIn],[CheckOut])\60
& " Minutes: " & DateDiff("n",[CheckIn],[CheckOut]) Mod 60

And if the number of minutes was crucial I might go so far as to use
dateDiff seconds and then divide that by 60 to get the minutes and then
use Mod 60 on the result.


If the poster wanted something like
32:40 to represent 32 hours and 40 minutes then we would need to throw
in the format function to ensure leading zeroes for durations such as
32:07 (32 hours 7 minutes)

Stay: DateDiff("n",[CheckIn],[CheckOut])\60
& ":" & Format(DateDiff("n",[CheckIn],[CheckOut])) Mod 60,"00")



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


KARL said:
Try this --

Stay: "Hours: " & DateDiff("h",[CheckIn],[CheckOut]) & " Minutes: " &
((DateDiff("h",[CheckIn],[CheckOut]))*60)-DateDiff("n",[CheckIn],[CheckOut])
Click to expand...
Click to expand...
Click to expand...
 
Top