Working with Time Intervals (1 Viewer)

[hokiewalrus]

I have a payroll system that is dealing with a lot of time intervals as opposed to an absolute time (ie; 8:30 is 8 hours and 30 minutes, not 8:30AM) and I need some advice.

I've searched this forum a lot and read this: http://www.mvps.org/access/datetime/date0009.htm

Which seems like it would work but doesn't seem very...elegant, for lack of a better word. Especially since I'm going to have a fair amount of intervals that are longer than 24 hours and would love for them to display as hhh:mm

How do most people handle time intervals? It seems clear that the date data type wasn't really designed with this in mind, so should I try to make it work or should I attempt to roll my own base 60 data type?

 

[SOS]

You might store them as text and then use some conversions when you need to do something with it. Otherwise you might let the entry be as such but then convert to a decimal form for storage.

 

[dcb]

You might store them as text and then use some conversions when you need to do something with it. Otherwise you might let the entry be as such but then convert to a decimal form for storage.

I agree - Store minutes then you always know where you stand, let the forms, reports etc show the user the aesthetic time....

 

[hokiewalrus]

So I should convert everything to minutes then store that value as text?

That seems like a good idea...thanks.

 

[ChrisO]

How are you entering and storing the time interval?

 

[hokiewalrus]

Currently? I'm not storing them, they are being entered as TimeIn and TimeOut and then stored as Date data types formatted to Short Time. I calculate the actual intervals as needed for display, but I've begun to run into problems adding up various intervals for various reasons.

This is all for a payroll system I'm developing for a company, and they have the most convoluted payroll I've ever seen. As a result, I have to keep track of a lot of different times and a lot of adjustments for each of those times and I'm finding the Date data type hard to work with.

 

[ChrisO]

I would think that a payroll system would not only need to know the duration but would also need to know the starting date which duration alone would not show.

Overtime, public holidays, shift allowance and so forth. For that, a start date/time and an end date/time is all you should need to store. You should be able to calculate everything from those two values.

 

[Galaxiom]

Ideally I would enter the start and end times if available and appropriate. Then calculate and store minutes (integer) or the times as Chris suggested above.
You really don't want people doing maths themselves.

Otherwise I would enter the time as hours and minutes probably in two separate boxes. This avoids any trouble with incorrect delimiters or any need to parse that string.

Use the After Update events of these boxes to calculate the minutes into a bound textbox.

Use the form OnCurrent event to recaclulate the hours and minutes back into the boxes.

Hoursbox = [minutefield]\60

Minutesbox: = [minutefield] MOD 60

Note: the backslash is the Integer Divide.
Same result as:
Fix([minutefield]/60)
or:
Int([minutefield]/60) ' with positive numbers only

 

[hokiewalrus]

True, they do need to know the date the work was preformed, however that is linked with a FK to another table that holds data for the day (as I said, very convoluted). Certain adjustments are made at the "per-job" level, others at the day level, and still others at the week level, depending on if they are working with actual work time, billing time, or pay time.

Without getting too bogged down in the craziness that they do, storing the date again the TimeIn/TimeOut fields seemed redundant and therefore not necessary, but perhaps it would help with the issues I am having.

At this point I think some experimenting and testing is needed before I can determine how best to proceed, but so far I've received really good suggestions that I hadn't considered.

Mainly I was wondering if there was a typical way intervals are handled, because I know the Date data type wasn't designed with that in mind and I feel like it must come up. Also because getting into the specifics of my project would drive any sane man around the bend. I know it has for me...

 

[Galaxiom]

Storing start and end times and themiutes is a normalization anomaly.

You should store Starttime EndTime and the adjustments.
The minutes should be recalculated on demand from that information.

 

[hokiewalrus]

Storing start and end times and themiutes is a normalization anomaly.

You should store Starttime EndTime and the adjustments.
The minutes should be recalculated on demand from that information.

That is how I am currently storing everything, though I question whether I am storing everything in the best format. TimeIn/TimeOut are stored as "Short Time" as I said and adjustments are stored as number of minutes as Integers.

Should I bail on the Date data type and just convert everything into # of minutes? (TimeIn/TimeOut being # of minutes since midnight) It would make the math behind the scenes easier and then I would only need to do one conversion. Or should I be storing adjustments as "Short Time" as well to the same end (minimizing conversation)?

I feel like I'm having a "can't see the forest for the trees" moment.

 

[ChrisO]

>>True, they do need to know the date the work was preformed, however that is linked with a FK to another table that holds data for the day (as I said, very convoluted).<<

That is meaningless to me. They may hold ‘data for the day’ but you have not said what data is being stored.

You have also said that the period could exceed 24 hours. If so then what is the start Date/Time and what is the end Date/Time.

In other words, with the information supplied by you so far how would you know if the employee ran into penalty rates?

 

[Galaxiom]

Should I bail on the Date data type and just convert everything into # of minutes? (TimeIn/TimeOut being # of minutes since midnight) It would make the math behind the scenes easier and then I would only need to do one conversion. Or should I be storing adjustments as "Short Time" as well to the same end (minimizing conversation)?

I would stick with the Date/Time format. People relate to days, hours and minutes and Access handles this well. The minutes are easy to extract from any date/time field and and Round as required.

If it looks cluttered turn some of the maths into functions.

 

[RainLover]

Are you saying that your people get paid by the minute.

 

[hokiewalrus]

No, they don't get paid by the minute, but they need to keep accurate records of who did what, they get paid on the 1/4 hour.

I have it all worked out now, thanks for everyone's help. I wrote some functions to fluidly convert times into Integers (number of minutes) and back again for when I need to calculate an interval or when the amount of time is over 24 hours and so far it's working great.

I still wish there was a Time data type that doesn't tie into a time of the day, but this is working well now and I haven't had to break any normalization or pull my hair out to do it.

I'm tempted to explain how this company handles payroll and billing hours but I doubt you'd believe me, heh.

 

[RainLover]

You seem to have solved the problem which is good. But I would still like to show how I handle this.

I would have three tables.

The First would be the Work Order Table, The Second to the amount of time worked, and a Third as the date worked. You would also have other Tables such as Employee Details and perhaps Client Details depending on your business.

tblWorkOrder

WorkOrderID as Autonumber
ClientID as Long FK to your Client Table if necessary
WorkOderNumber as Long or maybe Text (Text Would be for HXC 345670) Long if you just use whole numbers.
WorkOrderDetails
Other Tables as required

tblEmployeeJobs

EmployeeJobsID as Autonumber
DateWorked as Date
StartTime
EndTime

tblEmployeeJobDetails

EmployeeJobDetailsID
WorkOrderID as Long FK to tblWorkOrder
EmployeeJobsID as FK to tblEmployeeJobs
NT as Double
TH as Double
DT as Double
PH as Double

The last four field are for Normal Time, Time and a Half. Double Time and Public Holidays. Your business may have different rates of pay.

I would enter these time as Numbers. e.g. 4.50 as 4 and a Half Hours. 2.25 as 2 and a Quarter Hours.

This to some extent breaks normalisation rules but as far as data entry is concerned it is much faster and more accurate. It also alows you as the programer to calculate hours much easier without having to jump through loops. To me this is justification for bending the Normalising Rule.

Hope this helps.

 

[Galaxiom]

I still wish there was a Time data type that doesn't tie into a time of the day.

Short time format can be used like this. It is a 24 hour format.

Edit: But not for more than 24 hours. Forgot that was part of the original problem.

I agree with hokiewalrus. It would be a useful to have a Time format that went beyond 24 hours.

 

[c_smithwick]

Just to clarify something, the DATE data type is stored as (8-byte) floating-point numbers that represent dates ranging from 1 January 100 to 31 December 9999. The "Short Date" is a display format and has nothing to do with the data that is stored. When you are inputing a date, as long as you enter values for day, month, year, hours and minutes your data will be accurate to the minute. If you enter just day, month and year in a field and the hours and minutes in another, you can't successfully add them together error free. Why don't you just collect your start date/time and end date/time and use DateDiff("n", EndDate, StartDate) to get the difference in minutes between the two?