Macro for finding points of time which are available and setting new points according to input parameters

dezinsektor

New Member
Joined
Sep 30, 2019
Messages
25
Hy everyone, this one is difficult, even to explain.
Idea is to make a macro which will scan what time periods are available and then set the START and END times for a new unit, but there must be at least 15 minutes gap between all units' working time.
The unit working time is between START and END - pretty self explanatory :)
So, time periods between all START and END times are not available. After 24 hours of unit RESET time next START time must be RESET time. Therefore, for let's say unit MB-34, 5th START should be 1st START (not 10:50:40 like in table) which is RESET time and that is true for all units after their 24 hour cycle.
If I want to introduce a new unit with unique IDLE TIME and RUNING TIME, I want that all start and end times are calculated for this new unit having in mind above mentioned and not changing the data (IDLE, RUNNING, RESET, START and END times) for old units.

DATA
UnitIdle time [hours]running timereset time1st startend2nd startend3rd startend4th startend5th startend6th startend7th startend
MB-346:00:000:05:1010:30:00
10:30:00​
10:35:10​
16:35:10​
16:40:20​
22:40:20​
22:45:30​
4:45:30​
4:50:40​
10:50:40​
-> reset time
MB-416:00:000:01:3012:30:00
12:30:00​
12:31:30​
18:31:30​
18:33:00​
0:33:00​
0:34:30​
6:34:30​
6:36:00​
12:36:00​
-> reset time
MB-426:00:000:02:4514:30:00
14:30:00​
14:32:45​
20:32:45​
20:35:30​
2:35:30​
2:38:15​
8:38:15​
8:41:00​
14:41:00​
-> reset time
MB-474:00:000:03:309:30:00
9:30:00​
9:33:30​
13:33:30​
13:37:00​
17:37:00​
17:40:30​
21:40:30​
21:44:00​
1:44:00​
1:47:30​
5:47:30​
5:51:00​
9:51:00​
-> reset time
MB-5124:00:000:00:4016:00:00
16:00:00​
16:00:40​
16:00:40​
-> reset time
MB-86:00:000:01:2011:00:00
11:00:00​
11:01:20​
17:01:20​
17:02:40​
23:02:40​
23:04:00​
5:04:00​
5:05:20​
11:05:20​
-> reset time
MB-3224:00:000:01:3015:00:00
15:00:00​
15:01:30​
15:01:30​
-> reset time
MB-498:00:000:01:1011:30:00
11:30:00​
11:31:10​
19:31:10​
19:32:20​
3:32:20​
3:33:30​
11:33:30​
reset time
New unit
reset timethis is the time when unit starts to function, after 24 hours unit must start again at this time, it cannot start after that time!

Thank you, I am sorry for posting table in this form but I cannot install Free XL2BB Add-in (due to admin restrictions on my computer).
I can send you Excel file if you want.

Cheers!
dezinsektor
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,594
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
as you can link your workbook to a files host website like Dropbox for example …​
 

Forum statistics

Threads
1,144,162
Messages
5,722,848
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top