Having trouble with calculating time differences in ms access

  • Thread starter Thread starter Esca
  • Start date Start date
E

Esca

Hi,

Just Wondering if anybody can help me out! Im doing a timesheet
database for our engineering department and in this timesheet it
consist of 5 different departments, 5 start time, 5 end time, and 5
total hours. All of this is just for the one day they work because the
technician can work in different departments in one day.
What im having trouble with is calculating the time. The format of the
Start time and End Time is - General Date.
and the expression in my total hours is - =Format(DateDiff("n",[Start
Time1],[End Time1])/60,".00")

For Example: Start Time1: 10:00, End Time: 15:00, Total hours that it
gives me is correct. - 5.00
But when i try calculating Start time: 22:00, End Time - 3:00 The
correct Total hours is suppose to be 5.00 but instead it gives me a
negative -19.

Can anyone solve my problem?
 
Store the start time as the date and time in the field, then your formula
will work just fine. When you store just the time, ACCESS actually stores it
as a time on the date December 31, 1899, which is why your formula doesn't
work when the start time and end time cross midnight.
 
Hi,

Just Wondering if anybody can help me out! Im doing a timesheet
database for our engineering department and in this timesheet it
consist of 5 different departments, 5 start time, 5 end time, and 5
total hours. All of this is just for the one day they work because the
technician can work in different departments in one day.
What im having trouble with is calculating the time. The format of the
Start time and End Time is - General Date.
and the expression in my total hours is - =Format(DateDiff("n",[Start
Time1],[End Time1])/60,".00")

For Example: Start Time1: 10:00, End Time: 15:00, Total hours that it
gives me is correct. - 5.00
But when i try calculating Start time: 22:00, End Time - 3:00 The
correct Total hours is suppose to be 5.00 but instead it gives me a
negative -19.

Can anyone solve my problem?
Click to expand...

Last I checked, 3 am was in fact nineteen hours earlier than 10 pm. Access is
giving you the correct answer.

If you're going to be spanning midnight you should really store the *date and
time* together in the same field, or make some other provision to ensure that
you handle midnight correctly. #10/1/2009 22:00:00# is in fact five hours
before #10/2/2009 03:00:00#, and DateDiff will calculate it on that basis.
 
The following expression can be used to calculate elapsed time when there is
no date component and times may be over two days. For example, 11:49 PM to
1:00 AM

Number of Minutes:
(DateDiff("n",Starttime,Endtime) + 1440) Mod 1440

Number of Seconds:
(DateDiff("s",Starttime,Endtime) + 86400) Mod 86400

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Hey guys,

Thanks a lot for all your replies really appreciate it. I figured the
formula for the Total Hours but the expressions ended up being "IIf
([StartTime]<[EndTime],DateDiff("h",[StartTime],[EndTime]),24-DateDiff
("h",[EndTime],[StartTime]))". but i have another problem!! In my
subform where i have the start time, end time, Total hours (With the
formula mentioned) and a sub total hours to calculate the total hours
and i tried putting the expressions (=Sum([Total Hours])) but some how
no digits are coming up just shows a blank box? Also why arent the
digits from the total hours showing up in the tables because i also
need to put the total hours in a report but cant really do it because
its not showing up in the table, just blank!!?? What am I doing
wrong???

Thanks again

Mark Escalante
 
Also why arent the
digits from the total hours showing up in the tables because i also
need to put the total hours in a report but cant really do it because
its not showing up in the table, just blank!!?? What am I doing
wrong???
Click to expand...

Assuming that a calculation on the screen will (or should) be stored in your
table. It won't, and it shouldn't!

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox. Just put the same
expression as the control source of a textbox on the Report.
 
Day zero in Access is actually 30 December 1899. Rumour has it that the
Access development team misunderstood the basis for leap year calculation and
had to move the origin for the date/time implementation back a day. Whether
there's any truth in that or not I've no idea.
Click to expand...

The story I heard (and likewise, I have no idea whether it's true) is that the
date was chosen for compatibility with the date convention in Lotus 123... and
that the Lotus programmers were the ones who did not realize that 1900 was not
a leap year.
 
Oops, sorry, thanks for catching that, Ken. Slip of the fingers for 31
instead of 30.


--

Ken Snell
http://www.accessmvp.com/KDSnell/


KenSheridan via AccessMonster.com said:
Day zero in Access is actually 30 December 1899. Rumour has it that the
Access development team misunderstood the basis for leap year calculation
and
had to move the origin for the date/time implementation back a day.
Whether
there's any truth in that or not I've no idea.

Ken Sheridan
Stafford, England

Ken said:
Store the start time as the date and time in the field, then your formula
will work just fine. When you store just the time, ACCESS actually stores
it
as a time on the date December 31, 1899, which is why your formula doesn't
work when the start time and end time cross midnight.
Hi,
Click to expand...
[quoted text clipped - 15 lines]
Can anyone solve my problem?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top