time expression

F

ferde

I want to calculate the difference between start time and stop time
however my formula datediff("n"[start time],[stop time]) only works if the
time does not cross over into a new day. ......such as 23:45 start time
and 00:15 stop time.. My answer should be 30 minutes in this example
but instead returns -1480.

I need to use military time . Can anyone help me figure this out? Thank
you in advance.
 
M

Michel Walsh

Hi,


Include the day in the date_time argument for start time, and stop time,
*OR* , add one day (24*60*CDec(60)) to the result if the result is negative,
assuming you JUST span one day.



Hoping it may help,
Vanderghast, Access MVP
 
J

John Vinson

I want to calculate the difference between start time and stop time
however my formula datediff("n"[start time],[stop time]) only works if the
time does not cross over into a new day. ......such as 23:45 start time
and 00:15 stop time.. My answer should be 30 minutes in this example
but instead returns -1480.

I need to use military time . Can anyone help me figure this out? Thank
you in advance.
Click to expand...

What is the time difference between noon and 1pm?

49 hours, right? It is if it's noon Monday and 1pm Wednesday!

It's often best to take advantage of the fact that Access date/time
values can store both a date and a time. If you were storing
#7/31/2006 23:45# and #8/1/2006 00:15# in your table fields, the
DateDiff expression would work correctly.

If you can't (for some reason) do so, and you will never have more
than 24 hours at a stretch, you can use an expression like

DateDiff("n", [Start Time], [Stop Time]) + IIF([Start Time] > [Stop
Time], 1440, 0)

to "wrap" the time around midnight.

John W. Vinson[MVP]
 
F

ferde

YOUR MY HERO......THANK YOU SO MUCH

John Vinson said:
I want to calculate the difference between start time and stop time
however my formula datediff("n"[start time],[stop time]) only works if the
time does not cross over into a new day. ......such as 23:45 start time
and 00:15 stop time.. My answer should be 30 minutes in this example
but instead returns -1480.

I need to use military time . Can anyone help me figure this out? Thank
you in advance.
Click to expand...

What is the time difference between noon and 1pm?

49 hours, right? It is if it's noon Monday and 1pm Wednesday!

It's often best to take advantage of the fact that Access date/time
values can store both a date and a time. If you were storing
#7/31/2006 23:45# and #8/1/2006 00:15# in your table fields, the
DateDiff expression would work correctly.

If you can't (for some reason) do so, and you will never have more
than 24 hours at a stretch, you can use an expression like

DateDiff("n", [Start Time], [Stop Time]) + IIF([Start Time] > [Stop
Time], 1440, 0)

to "wrap" the time around midnight.

John W. Vinson[MVP]
Click to expand...
 
Top