Activity Timing

[MattBaldry]

Hi all,

I am trying to get my head around the possibility of something, and hoping someone somewhere may already have this in the dust and cobwebs.

I want to be able to work out the end date/time of an activity based on start date/time, worked out based on working hours.

Example we have John Smith who works 07:30 - 16:00 each day, minus 10:00 - 10:10 tea break and 13:00 - 13:30 lunch.

If we enter an activity to start on Monday at 07:30 and takes 10 hours, I would like it to work out the end time to be 09:40 on the Tuesday.

I have worked this out as 600 minutes job time, 470 working minutes per day, so all day Monday (600 - 470 = 130) so 130 minutes into Tuesday, 09:40.

Hopefully my working out is correct and makes sense.

Each employee would have different working hours and break times. This will be recorded in a separate table.

Hopefully one of the amazing brains in here already has something in their memory locker....

~Matt

EDIT: Just to throw an additional table in the mix, I would also like to include the possibility of scheduled time off in this equation.

So if the above person had Tuesday booked off, it would show the end date as Wednesday 09:40.

 

[plog]

Use the DateAdd() function:

MS Access: DateAdd Function

This MSAccess tutorial explains how to use the Access DateAdd function with syntax and examples. The Microsoft Access DateAdd function returns a date after which a certain time/date interval has been added.

www.techonthenet.com

 

[MattBaldry]

Use the DateAdd() function:

MS Access: DateAdd Function

This MSAccess tutorial explains how to use the Access DateAdd function with syntax and examples. The Microsoft Access DateAdd function returns a date after which a certain time/date interval has been added.

www.techonthenet.com

Hi plog,

I have tried this, but I need to add the time only during the working hours. If I add 600 minutes to 07:30 I would get 17:30, not 09:40 the next day when you take out the breaks and out of working hours....

~Matt

 

[Gasman]

Hi plog,

I have tried this, but I need to add the time only during the working hours. If I add 600 minutes to 07:30 I would get 17:30, not 09:40 the next day when you take out the breaks and out of working hours....

~Matt

Well you have to take the other time durations into account as well?

StartTime + WorkTime + BreakTime + SmokeTime + SkivingTime = EndTime.

You would add to a DateTime so that you would get the next day and not just the TimeValue of the start time.

 

[plog]

This needs to be a custom function in a module where you do all that math. With the structure and names of relevant/fields and tables I can be more specific, but in general, you would pass that function whatever it needs to do the math (whether that be a starting time and and EmployeeID so it can look up the rest of the information or pass it all the information directly), the function does all the calculations and returns your end date/time.

DateDiff is another function you will need for determining how long breaks are based on start/end time:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

 

[MattBaldry]

Well you have to take the other time durations into account as well?

StartTime + WorkTime + BreakTime + SmokeTime + SkivingTime = EndTime.

You would add to a DateTime so that you would get the next day and not just the TimeValue of the start time.

Skiving time they have more of that than anything....

~Matt

 

[gemma-the-husky]

If the breaks are quarter hours, and the project is whole hours, I don't see how you can have 40 minutes in the answer. The answer would have to be at a quarter hour point.

 

[MattBaldry]

If the breaks are quarter hours, and the project is whole hours, I don't see how you can have 40 minutes in the answer. The answer would have to be at a quarter hour point.

Apologies, I got over excited. The tea break should have been 10 minutes.

 

[MattBaldry]

This needs to be a custom function in a module where you do all that math. With the structure and names of relevant/fields and tables I can be more specific, but in general, you would pass that function whatever it needs to do the math (whether that be a starting time and and EmployeeID so it can look up the rest of the information or pass it all the information directly), the function does all the calculations and returns your end date/time.

DateDiff is another function you will need for determining how long breaks are based on start/end time:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

Hi plog,

At the moment I am as basic as below for the employee side.

EmployeeID, EmployeeName

WorkingHoursID, EmployeeID, MondayStart, MondayEnd (and remaining weekdays)

TeaBreakID, EmployeeID, MondayTeaStart, MondayTeaEnd (and remaining weekdays)

LunchBreakID, EmployeeID, MondayLunchStart, MondayLunchEnd (and remaining weekdays)

Using this I have it calculating the working minutes per day perfectly.

It's getting my head around calculating the end date/time from the start date/time using the working hours without the breaks.

I want to be able to select the employee. Specify a start date and time, and then duration, and the end date and time are shown for me.

~Matt

 

[LarryE]

Here is a very simple time keeping project. It keeps track of Activity Hours for dates and times during the day. Dates are in Short Date format and times are in Medium Time format.

Open the form in design mode and take special note of the formatting of the dates and times fields in the form and study how the Hours are calculated in the Hours field. You need to do it this way because you could have work hours spanning two different dates. ACCESS wants those dates and times in a special format (General Date) in order to calculate times that include two different dates. So take special note of the how Hours are calculated. I do use the DateDiff() function within the calculation.

You will also need fields for:

1. Tea times: TeaStartTime and TeaEndTime
2. Lunch times: LunchStartTi,me and LunchEndTime
3. TimeOff time: TimeOffStartDate, TimeOffEndDate,

In addition, in order to link the ActivityHours table to your employee table, you will need a Foreign Key field link to your EmployeeID field in your Employee table (I assume you have one).

Take special note: Because you want to track so many different times (work, tea, lunch, time off) during a single day, the calculations are going to get complicated. So be very careful with your table and form field naming conventions. What you want to do is certainly possible but just take care.

 

[MattBaldry]

Here is a very simple time keeping project. It keeps track of Activity Hours for dates and times during the day. Dates are in Short Date format and times are in Medium Time format.

Open the form in design mode and take special note of the formatting of the dates and times fields in the form and study how the Hours are calculated in the Hours field. You need to do it this way because you could have work hours spanning two different dates. ACCESS wants those dates and times in a special format (General Date) in order to calculate times that include two different dates. So take special note of the how Hours are calculated. I do use the DateDiff() function within the calculation.

You will also need fields for:

1. Tea times: TeaStartTime and TeaEndTime
2. Lunch times: LunchStartTi,me and LunchEndTime
3. TimeOff time: TimeOffStartDate, TimeOffEndDate,

In addition, in order to link the ActivityHours table to your employee table, you will need a Foreign Key field link to your EmployeeID field in your Employee table (I assume you have one).

Take special note: Because you want to track so many different times (work, tea, lunch, time off) during a single day, the calculations are going to get complicated. So be very careful with your table and form field naming conventions. What you want to do is certainly possible but just take care.

Hi Larry,

Thank you for the db. I have had a quick look and wonder why you have separate fields for the date and time? If you have these in a single field, you can just use the DateDiff to get the answer your formula shows.

DateDiff: DateDiff("n",[ActivityStartDate],[ActivityEndDate])/60

This is with the ActivityStartDate and EndDate being changed to general date.

My issue would be removing all the non-working times between the 2 dates. I have an idea in my mind, but am off for a 4 day break now so it will have to wait.

~Matt

 

[LarryE]

why you have separate fields for the date and time? If you have these in a single field, you can just use the DateDiff to get the answer your formula shows.

Because form input is very difficult with General Date formatting. A Short Date and a Medium Time format (for example 10:00 PM) is much easier to input for users and more understandable. Only the Hours calculation needs to be in General date format so hours spanning two dates can work.

 

[MattBaldry]

Because form input is very difficult with General Date formatting. A Short Date and a Medium Time format (for example 10:00 PM) is much easier to input for users and more understandable. Only the Hours calculation needs to be in General date format so hours spanning two dates can work.

That does make sense. I feel your pain of people and entering of data. I have had to write more code than I would care to make simple entry foolproof. And I still get amazed some days on what people have done.

 

[LarryE]

That does make sense. I feel your pain of people and entering of data. I have had to write more code than I would care to make simple entry foolproof. And I still get amazed some days on what people have done.

Use what you can. Nothing is foolproof just FYI...

 

[plog]

EmployeeID, EmployeeName

WorkingHoursID, EmployeeID, MondayStart, MondayEnd (and remaining weekdays)

TeaBreakID, EmployeeID, MondayTeaStart, MondayTeaEnd (and remaining weekdays)

LunchBreakID, EmployeeID, MondayLunchStart, MondayLunchEnd (and remaining weekdays)

Your tables are not set up properly.

1. Tables with identical structures. Your 3 time tables should be just 1 table with an additional field to hold the type (Work, TeaBreak, Lunch, etc.). Valid data shouldn't be stored in table or field names. Your putting the type of hours in table and field names when instead you should accomodate that data with a field for the type. Getting all your date/time data into one table will allow you to usee built in aggregate functions of SQL (COUNT, MAX, MIN, SUM, etc.) to accomplish what you want.

2. Storing data horizontally. You shouldn't have fields for each day of the week, instead you should just add more records to your table to accommodate it. For someone who works 5 days a week, that would require 5 records in your time table. You can always use built in date functions to determine what day of the week a date falls on if needed. Just like getting all your different types into one table will allow you to aggregate(SUM, COUNT, etc.) your data easily so will getting all your date/time data into two field (start, end). So when you want to add up the hours for a time period you just enter the time period and use a SUM function instead of adding every field together.

 

[The_Doc_Man]

The basic assumption here is that these incremental times can be added up and lead to a date. The problem is that the length of the calendar day is much longer than the working day AND the calendar is continuous whereas working days are not. They are broken up by tea time, lunch time, other breaks, holidays, and further complicated by having an arbitrary start time that might be after one of the other breaks.

I see this as a function that has a loop where you have working blocks: start of day to tea, tea to lunch, lunch to (some other break), other break to end - plus holidays and non-work weekend days. You also have the implied duration of tea time, lunch time, other break time. You take as input a start date and the number of minutes. Iteratively compare the block size to the remaining time. If the remaining time is bigger than the block, subtract the block and step to the next time block. Add the time of the break to the reference date to allow for the time not worked in the calendar day. Every time you come to the end of the day, step to the next calendar day set to starting time. At some point you reach a remaining time that is less than the next block. So you add the remainder to your time and there you go, you've got the ending date/time.

As a shortcut, you should know the size of the work day in total and could check whether the remaining time at the start of the day covers a whole day, and could just step the date forward to the next start of the day. So basically, from the start time, you either do the detailed loop or, if you are at the start of a day and have a big block of time still remaining, chop off whole days. Sounds tedious. But there are only four work blocks per day. If you do this iteration, you might have at most 3 blocks on the first day and 3 blocks on the last day PLUS the intervening number of whole days.

I don't see this working as a pure query, though you could make it a public function that a query could call.

 

[MattBaldry]

The basic assumption here is that these incremental times can be added up and lead to a date. The problem is that the length of the calendar day is much longer than the working day AND the calendar is continuous whereas working days are not. They are broken up by tea time, lunch time, other breaks, holidays, and further complicated by having an arbitrary start time that might be after one of the other breaks.

I see this as a function that has a loop where you have working blocks: start of day to tea, tea to lunch, lunch to (some other break), other break to end - plus holidays and non-work weekend days. You also have the implied duration of tea time, lunch time, other break time. You take as input a start date and the number of minutes. Iteratively compare the block size to the remaining time. If the remaining time is bigger than the block, subtract the block and step to the next time block. Add the time of the break to the reference date to allow for the time not worked in the calendar day. Every time you come to the end of the day, step to the next calendar day set to starting time. At some point you reach a remaining time that is less than the next block. So you add the remainder to your time and there you go, you've got the ending date/time.

As a shortcut, you should know the size of the work day in total and could check whether the remaining time at the start of the day covers a whole day, and could just step the date forward to the next start of the day. So basically, from the start time, you either do the detailed loop or, if you are at the start of a day and have a big block of time still remaining, chop off whole days. Sounds tedious. But there are only four work blocks per day. If you do this iteration, you might have at most 3 blocks on the first day and 3 blocks on the last day PLUS the intervening number of whole days.

I don't see this working as a pure query, though you could make it a public function that a query could call.

Thanks Doc,

You have explained it very well and you are right. I need to find a way of taking the start date and then adding all minutes needed for the activity, including any breaks, out of hours times, public holidays and such. For now, if I can get a way of just taking the start time and finding the end time including breaks and normal out of hours times, that will be a good start. Holidays can come at a later date.

~Matt

 

[LarryE]

In order to use ACCESS properly you need to keep your Activity Types (Regular hours, breaks, time off etc.) in a separate table. So you would have:

1. Multiple employees
2. Each employee with multiple activity types and activity hours

So, your design might look something like this for example:

 

[LarryE]

@MattBaldry
Matt are you going to use this project data to calculate employee pay? If so, you need will need a PayRate table connected to the Employee table.

 

[MattBaldry]

@MattBaldry
Matt are you going to use this project data to calculate employee pay? If so, you need will need a PayRate table connected to the Employee table.

Hi Larry,

No, this would only be needed to work out when an activity should end based on how many minutes it should take, and when it should start.

It is just a start and end formula needed.

~Matt