• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time based calculations avoiding break times

Damodamo

New Member
Hi all, New here and this is my first post.
I need some guidance on how to approach a problem. I'm trying to calculate a recurring target time for work to be complete. On a 24/7 calendar this would be a piece of cake but I'd like to calculate this for a typical working week Mon to Fri where Fridays are half day. Not only this but i want to exclude all of the non working times such as lunch breaks and tea breaks.

So far I've created a list of durations for each 'Production', period and each 'Non production', period as decimal of 1 day so i was thinking of using some kind of 'Sum if' function? PS: I'm no whiz on excel but not a complete armature either.

The repeating target time value is 14.5 hours.
A typical Monday look like this
07:30 to 07:40 - non production
07:40 to 09:47 Available
09:47 to 10:00 non production
10:00 to 12:27 Available
12:27 to 13:00 non production
13:00 to 14:47 Available
14:47 to 15:00 non production
15:00 to 16:27 Available

This is repeated for Tues, Weds and Thurs, Fri is almost the same except there is nothing after 12:30. So i'd need to know what the date and time would be 14.5 hours after the first start time but only by summing the 'Available', parts of the day. I've done this long hand and it looks like this:

16/05/2022 07:40​
17/05/2022 15:26​
19/05/2022 14:17​
24/05/2022 08:06​
25/05/2022 15:52​
30/05/2022 09:28​
01/06/2022 08:32​
02/06/2022 16:18​
07/06/2022 09:54​
09/06/2022 08:58​
13/06/2022 11:29​
15/06/2022 10:33​
17/06/2022 09:24​
So this is the output I'd want to generate but be able to extend it into the future.

Any guidance on this would be hugely appreciated
Kind regards

Damo
 

p45cal

Well-Known Member
I'm trying to put something together but I'm getting into a bit of a muddle with times; I can't get them to agree with yours.
In the attached is a putative solution in a table at cell F2 of the attached. I need you to tell if if they're more or less correct.

To fill you in a bit, the way that I've done this is to create a massive table (using the table (at cell B2) of start/finish working times for the days of the week), in the background being one row per minute for the whole period 40 days or so from 15/5/2022 07:40, then taken only 1 row every 870 rows (870 minutes = 14.5 hours) from that big table.

To help you verify (or not) the results, I've exposed that big table on the sheet at cell B23. It's empty at the moment to save file size, but all you need to do to reinstate the data in it it, is to right-click somwhere in that table then choose Refresh. It should end up with the bottom of the table somewhere around row 13000 of the sheet.
There are some extra columns in that table to try and help verify how I'm hoping to do this:
The column headed Modulo10 is the modulus 10 of the Index column, so if you filter the Modulo10 column only to show the zeroes, you should get 10 minute intervals in the date/time stamps (column headed Custom). If you think this is correct then clear the filter from that column and filter the Modulo120 column in the same way. The aim of this is to have 2 hour recurring jobs; in this case these jobs should include a break or two and I ask that you verify these are correct. You can do similar with the other columns. Finally, there's the Modulo870 column which if filtered in the same way should give the same results as in the table at cell F2.

Over to you now to tell me if these date time stamps are correct…
 

Attachments

Last edited:

Damodamo

New Member
I'm trying to put something together but I'm getting into a bit of a muddle with times; I can't get them to agree with yours.
In the attached is a putative solution in a table at cell F2 of the attached. I need you to tell if if they're more or less correct.

To fill you in a bit, the way that I've done this is to create a massive table (using the table (at cell B2) of start/finish working times for the days of the week), in the background being one row per minute for the whole period 40 days or so from 15/5/2022 07:40, then taken only 1 row every 870 rows (870 minutes = 14.5 hours) from that big table.

To help you verify (or not) the results, I've exposed that big table on the sheet at cell B23. It's empty at the moment to save file size, but all you need to do to reinstate the data in it it, is to right-click somewhere in that table then choose Refresh. It should end up with the bottom of the table somewhere around row 13000 of the sheet.
There are some extra columns in that table to try and help verify how I'm hoping to do this:
The column headed Modulo10 is the modulus 10 of the Index column, so if you filter the Modulo10 column only to show the zeroes, you should get 10 minute intervals in the date/time stamps (column headed Custom). If you think this is correct then clear the filter from that column and filter the Modulo120 column in the same way. The aim of this is to have 2 hour recurring jobs; in this case these jobs should include a break or two and I ask that you verify these are correct. You can do similar with the other columns. Finally, there's the Modulo870 column which if filtered in the same way should give the same results as in the table at cell F2.

Over to you now to tell me if these date time stamps are correct…
Pascal!

What an elegant solution. I was along the right lines to this but wouldn't have got to where you have. It does appear to work a treat and I can confess why your output didn't match mine is because i'd provided incorrect input data to you - my apologies!
When i wrote the original post last night i didn't have access to the break times data so tried to do it from memory.

The breaktimes are in fact as follows:

Monday to Thursday
07:30 to 7:40
09:57 to 10:10
12:27 to 13:00
14:47 to 15:00
Day ends at 16:22

For a Friday its
07:30 to 7:40
09:57 to 10:10
Day ends at 12:22

I'm going to have a go at modifying your sheet to see if I can get it to match my output. Will update you later but already i can say a big thankyou as i can now see the logic to how to approach this and best of all it hasn't involved a stupidly lengthy chain of formulas!
 

p45cal

Well-Known Member
Putting in those adjusted work times made the results exactly match your calculations; excellent.
In the attached I've updated the work times and added a bit of functionality:
1. Enter the date/time of the start in cell N2
2. Enter the length of the recurring job in decimal hours in cell O2
3. Got it to look ahead 100 days instead of 40.

All you need to do is to refresh the table in cell L2.
 

Attachments

Damodamo

New Member
Thanks Pascal this is really excellent! Did you get excel to create the date and time stamps automatically from the table in B2:D20 in the original file? If so what function did you use to do that? Just curious.
This latest file though looks great and has saved me getting lost in formulas. I had been researching functions like 'Network days', and arrays etc which were getting pretty complex.
All the best
 

p45cal

Well-Known Member
It's a Power Query solution.
If you right-click the result table (it's called Merge1) and choose Table then Edit Query… it'll take you into the Power Query Editor.
On the left, if you expand the Queries pane you'll see 5 queries.
The one called StartDateAndTime only fetches the data from cell N2, which is a named range called StartDateTime.
The one called JobLengthHours only fetches data from cell O2, which is a named range called JobLengthinHours.
The one called WorkingHours just grabs the table (called WorkingHours) from the sheet.
Those 3 do little more than fetch data from the sheet. Next…
The one called Query1 just grabs the StartDateAndTime query and creates a list of dates 100 days from that start date. It then adds a column next to that list representing the day of the week for each of theose dates using the same enumeration as the Weekday column (column B) on the sheet.
The 5th query is the one which does the donkey work, called Merge1. This one starts with Query1 and merges it with WorkingHours.
At this point, you'll be able to follow the steps in that query in the pane on the right of the Power Query Editor called Query Settings, below which you'll see the Applied Steps. You just need to click on each step to follow what goes on. About half way down the Applied Steps you'll see one called Added Custom which adds lists of minute by minute data for each working period. When those lists are expanded (the step called Expanded Custom) we then have a big list of minute by minute data but only for the working hours. I then added an index (numbered the rows), added a new column with the modulo calculation based on the number of minutes in the JobLengthHours (just multiply by 60) then filtered that list for only zero values, then removed all columns but the list of date/times remaining and put that on the sheet.
 
Top