Overtime worksheet (1 Viewer)

[Hawkins1989]

Hi,

I am in need of some help, I have created a database in work which has a continuous form that I used to allow myself and colleagues to record the overtime that we do, the form has the staff name, date, usual end time and actual end time and then a calculated field formatted to time which caluclates the

 

[Hawkins1989]

Sorry posted before finishing the post.

It calculates the difference between the times with a running SUM() at the bottom.

If usual<actual then time is added and if usual>actual then time is taken away, this is shown by colour codes - green = owed, orange = taken.

The issue that I have now found is that after 24 hours it rolls back to 00:00 and starts again and if you use all the overtime you have accumulated it doesn't let you know, is there a way to make it calculate the days, hours, minutes with how it is currently set up or should I make some changes tot he layout?

I would appreciate any suggestions as I have about 10 people using it who currently think it is all working fine.

Many thanks
Matt

 

[arnelgp]

you need to replace your Calculated field with just a Short text.
and use user-defined function to calculate your actual time.

there is a function here that calculate the hours, minutes and seconds:
(1) Hours, Minutes, Seconds | Access World Forums (access-programmers.co.uk)

 

[Pat Hartman]

The issue that I have now found is that after 24 hours it rolls back to 00:00 and starts again and if you use all the overtime you have accumulated it doesn't let you know, is there a way to make it calculate the days, hours, minutes with how it is currently set up or should I make some changes tot he layout?

The date/time data type is POINT in time. It is NOT elapsed time. Remember, it is not stored as a string, it is stored as a double precision number with the integer portion representing the number of days since Dec 30, 1899 and the decimal portion being the part of a day. So it ALWAYS represents a date as well as a time. 0.0 = midnight on Dec 30, 1899

Calculate the difference in minutes and store it that way. You can display it as hours and minutes by dividing by 60 and then calculating the remaining minutes

 

[Hawkins1989]

Thanks for all the suggestions guys.

I used your method Pat and calculated the minutes then converted down to hours it's a lot better than how it was and I am happy that it accurately show was was owed and taken so thank you all.

Matt