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

  • Thread starter Thread starter Robert Molina
  • Start date Start date
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.
 
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
 
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.
Click to expand...
I cant use the DateDiff for reason above.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
 
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
 
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)
 
Back
Top