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:
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
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 |
Any guidance on this would be hugely appreciated
Kind regards
Damo