Date/Time Calculation past midnight...help - Microsoft Access

R

Robert Molina

Im a beginner with Access 2007. I have a report where I need to
calculate elapsed time in hh:mm between "Start Time" and "End Time"
=[End Time]-[Start Time].
The problem occurs during the 3rd shift when entering start times
before midnight and end times after midnight. (this is a machining
report)
Im only capturing Time and not date/time because in manufacturing the
third shift reports all their production to previous day when their
shift began.

I cant use the DateDiff for reason above.
 
R

Rob Parker

Date/time fields actually hold a number, where the integer portion
represents the date as days since 30/12/1899, and the decimal portion
represents the time as portion of 24 hours (eg. .5 is 12 hours). If you're
only storing the time component, the date component is 0. What you need to
do, when End Time is less than Start Time, is to add a day to the End Time.
So you can set the controlsource for your elapsed time textbox to the
following expression:
=IIf([End Time]>[Start Time],[End Time]-[Start Time],([End Time]+1)-[Start
Time])

HTH,

Rob
 
R

Robert Molina

Date/time fields actually hold a number, where the integer portion
represents the date as days since 30/12/1899, and the decimal portion
represents the time as portion of 24 hours (eg. .5 is 12 hours).  If you're
only storing the time component, the date component is 0.  What you need to
do, when End Time is less than Start Time, is to add a day to the End Time.
So you can set the controlsource for your elapsed time textbox to the
following expression:
=IIf([End Time]>[Start Time],[End Time]-[Start Time],([End Time]+1)-[Start
Time])

HTH,

Rob




Im a beginner with Access 2007. I have a report where I need to
calculate elapsed time in hh:mm between "Start Time" and "End Time"
=[End Time]-[Start Time].
The problem occurs during the 3rd shift when entering start times
before midnight and end times after midnight. (this is a machining
report)
Im only capturing Time and not date/time because in manufacturing the
third shift reports all their production to previous day when their
shift began.
I cant use the DateDiff for reason above.- Hide quoted text -

- Show quoted text -

SOLVED!!!
Rob, you are the man!
I've googled the heck out of this subject and even looked at forums
for a while and have found zero solutions or help.
Your expression worked perfectly!
Thanks,
Robert.
 
Joined
Jun 1, 2012
Messages
33
Reaction score
0
Hey Rob, can you help with a similer problem, I'm trying to calculate somewhat the same, except, I'm trying to get mine to show minutes only and my event may happen befor or after midnight.

e.g KPI time is 23:00 hours, the place time maybe anywhere from 22:00 - 02:00 the following morning, with an iif statement I get -1380 or DateDiff("n" I get the correct calc, unless I go over midnight... Please help, I'm pulling my hair out on this one...

Regards

Blair
 
Joined
Oct 4, 2012
Messages
1
Reaction score
0
Can someone help me with my problem, I have 4 textbox in my form StartTime, EndTime, Duration, and AmounttoPay. Im trying to get mine to show minutes only and my event may happen before or after midnight.

and after StartTime and EndTime calculated to get Duration i want to compute Duration with the given amount per hour to get the Amountto Pay.........Please Help.......

Here is my E-mail: (e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top