create a table of dates/times based on weekly shift start and finish times and holidays

i'm trying to make a list of dates and times based on a table of days of the week and shift start time /stop time also holidays

if day is blank then no work on that day
all times can vary

This is a bit clunky and works OK for what i need to do
BUT
it would be useful if i could change any time and the list expand to include those time

Column I is the table of dates and times - each minute
row 2 = now()
row 3 = sets now to a minute with no seconds
row 4 (I4) is where the formula starts
looking at the previous day and time compares with the shift table to see if its a saturday or sunday and IF there are any start stop times it inlcludes those shifts
if not it goes to Monday and looks at the start time and uses that
otherwise it just works Monday to Friday 24 hours

just would be useful to put any of the shift times in and have the list generate

also if its a bankholiday / holidays- look at the next shift and see if that is valid and use that - otherwise move on to the next day and check that
I have a list of holidays

I only need the table of minutes to run for a month - so 50,000 rows - this example just has 2000 rows to show a day and the change over , to keep the file small for the forum
Just drag down to extend

any suggestions

Hi Wayne,
gave it a try to incorporate looking at holidays.
Can these UK bank holidays be calculated?

Also took the liberty to simplify your formulas somewhat.
They're pretty nested. Not sure of the performance impact with 50K rows.

Not sure what you envision by (seems to me that the table is just doing that)

just would be useful to put any of the shift times in and have the list generate

Thanks for that and thanks for simplifying the formula
However, its not working correctly -if you set say a friday date/time then it should restart on Monday at the time in F3 as no time set in sat/sun
=NOW()+6
i should have a list starting at now()+6 - friday 21:00 - and they go on each minute untill midnight
Then the next shift on the table is Monday at 06:00 - so should start then

if there was a time in Sat say 11:00 then it should stop at friday G7 time and restart at Sat F8 time

I'm helping a friend out.....
the idea is to have a list of cumulative minutes which is in K
then a number of production times are added together and for each production cycle time and volume an estimated date and time are looked up - based on the cumulative time
but there can be lots of different shift times, different parts and cycle time and different volumes - Also a priority order
i have that all worked out and can look up varies minutes to get the date/time
i thought that was the best way to get the estimate date/time

Monday starts at 6:00 - but may start at anytime
Sat and Sunday maybe worked
Friday finishes at midnight
and if Sat and sun has no times set then the next startdate and time should be Monday at the shift starttime
if monday is a holiday , then starts on tuesday at 6am - which will not be in the shift

then once a full set of dates/times for a month are listed are listed
taking into account the shift table and any holidays

I was hoping to generate the list of minutes based on all the start and stoptimes in the shift table
and automatically exclude holidays, bankholidays and closed days - any dates entered into the table

Ok. Will need to chew on that for a while

Thanks - yea i chewed for quite a while and what he was after so of changed each time i spoke to him - hence the nested IF -i like your approach - so will have to look into how you did that

Getting the list of minutes based on the shift table would be more useful than the holidays if possible

Thanks for looking into this - really appreciated

I have managed to work out a way to create a list of dates
Based on a shift week - start and stop times and to exclude holidays from a table
The list needs to show every minute available for the shifts

the formula is very clunky

I set-up the starting date/time in F4
C4 to C103 - are the holiday dates table
I4 to I10 - are the days of the weeks - number Sunday =1, Mon 2 etc
J4 to J10 - are the shift start times
K4 to K10 - are the Shift end times
If a shift starts at 00:00 and ends at 00:00 - then that day is ignored

The minutes G:G have a formula - so that there is no error - as with this formula a date for every day is entered dd/mm/yyyy 00:00 - and so i dont want to count that minute

F3 = now() to start the date / time as now
F4 = rounds that time up to the minute - so no seconds included

Formula start in F5
BUT in order to keep the files size down i have reduce the number of rows F5 extends down to -
to see the way the formula works - you would need to extend the formulas - in F103 and G103 - down
Only need about 6 weeks in the list - but thats about 60,000 rows


=IF(NOT(ISERROR(INDEX($C$4:$C$103,MATCH(INT(F4),$C$4:$C$103,0)))),
DATE(YEAR(F4),MONTH(F4),DAY(F4)+1)+TIME(0,0,0),

IF(AND(TIME(HOUR(F4),MINUTE(F4),0)>=INDEX(CALENDAR!$J$4:$J$10,MATCH(WEEKDAY(F4,1),CALENDAR!$I$4:$I$10,0)), TIME(HOUR(F4),MINUTE(F4),0)<INDEX(CALENDAR!$K$4:$K$10,MATCH(WEEKDAY(F4,1),CALENDAR!$I$4:$I$10,0))),

F4+(1/24/60),

IF( TIME(HOUR(F4),MINUTE(F4),0)<INDEX(CALENDAR!$J$4:$J$10,MATCH(WEEKDAY(F4,1),CALENDAR!$I$4:$I$10,0)),

DATE(YEAR(F4),MONTH(F4),DAY(F4))+TIME(HOUR(INDEX(CALENDAR!$J$4:$J$10,MATCH(ROUNDUP(MOD(WEEKDAY(F4),7.1),0),CALENDAR!$I$4:$I$10,0))),MINUTE(INDEX(CALENDAR!$J$4:$J$10,MATCH(ROUNDUP(MOD(WEEKDAY(F4),7.1),0),CALENDAR!$I$4:$I$10,0))),0),

DATE(YEAR(F4),MONTH(F4),DAY(F4)+1)+TIME(HOUR(INDEX(CALENDAR!$J$4:$J$10,MATCH(ROUNDUP(MOD(WEEKDAY(F4)+1,7.1),0),CALENDAR!$I$4:$I$10,0))),MINUTE(INDEX(CALENDAR!$J$4:$J$10,MATCH(ROUNDUP(MOD(WEEKDAY(F4)+1,7.1),0),CALENDAR!$I$4:$I$10,0))),0))))

Almost there too, but ran into a circular reference which I can't find. Probably because it's right in my face
Will post my results tonight. Think I got it figured out pretty close to what you need.
Cut the whole thing up in comprehensible chuncks and use helper columns.

OK - thanks for taking the time to look at that - will the code be much quicker to run, ONLY - i have been asked to create upto 15 of this lists with different shift tables today !!!!!

Hi Wayne,
here's what I've managed to construct. Hope I'm getting close.
Didn't compare it to your results yet, but should be about the same.

Holidays are all calculated now.
Rest is denoted on the sheet.
There's one formula going through the whole range of dates, but it's only executed when an other week starts. So execution of that part is reduced to abt. 4 or 5 times iso for each rows. Guess this will save performance. With the 2150 rows of the example it performs ok.

I split the whole thing up in little pieces and put these in separate columns.
I use this approach a lot with complex formulas or frequently changing/expanding requirements.
Helps to focus on the relevant (changing) part.

i have been asked to create upto 15 of this lists with different shift tables today !!!!!

Are you still friends?

Thanks for going to all that trouble
Where do I change the date and time to test

looking at the code , i changed the date in I2

to now() = 6/5/15 time 17:55
and the time list started at 06:00

is that what you have setup ?

Yep, I changed I2 to test.
Change it back to now() and it runs like the original.
When now()=6-5-2015 17:55 , I3 should be 6-5-2015 17:16. Oops
If you change the formula in V3 into

Formula:

Please Login or Register  to view this content.

it will run ok.
This makes the formula check if the EndTime is reached.

While going through the sheet I discovered another flaw with calculating the index for looking up the StartTime.
That doesn't give the correct index when the initial date (I2) is somewhere half way a week.
Solution would be to calculate the first workday in a given week independently of the initial date.
Need to chew on that again.

Here's a new version.
Adjusted the formula for determining the (adjusted) index to use for looking up the StartTime.
Adjusted index is now also used for checking the EndTime.
Detailed explaination is on the sheet.

Thanks very much for that
for some reason

Sat does not appear to return the correct result - its 1 hour out ?

In the week shown 4/5 was a bank holiday.
So Tuesday was the first working day but should take the starttime of Monday, Wednesday takes the starttime of Tuesday etc
So Saturday is using the starttime of Friday.
Took that feature from you post #3

if monday is a holiday , then starts on tuesday at 6am - which will not be in the shift

thanks - yes if a holiday then the shift starts at 6am correct
But on I4
its showing Sat 02:01
but the start time and end time of the shift is set to
01:00 / 01:05

so it should start at 01:00 and not 02:00

thanks for looking at this

My interpretation of the statement "if monday is a holiday , then starts on tuesday at 6am - which will not be in the shift" was that
if Monday is a holiday then all start- and endtimes for that week need to shift one day forward like illustrated in the picture below.
ShiftingTimes.JPG
So this makes Saturday using the starttime of Friday (2:00) instead of it's own (1:00), because in this week Monday was a holiday.
May be I misinterpreted the statement above. If so please let me know how it should be interpreted.

Yes , sorry you have misinterpretated my message
its just the one following day

So if a monday
Tuesday will start at 6am even if the shift time says 00:00 to 06:00 for tuesday (if the time on the shift is a later starttime , then it will start at that time and NOT 6am
weds as shift, thur as shift

if holiday is a wednesday - then will start at 6am even if the shift says 00:00 to 06:00

On a Friday then the shift will start based on the starttimes shown in the shift pattern - so no need to adjust

Of course if the holiday is 2 consecutive days

Tues/wed
then stop at shift time on Monday and restart at 6am on Thursday

hope that makes a little more sense

thanks for continuing to look at this

Ok, so I've been jumping to conclusions.
Think I'm gonna introduce a substitution table. Gives some extra flexability too.

Ok, Thanks

I have managed to get it to work ok with this nested IF

Range N4:N103 is the holiday list
and
Settings!
B, C, D
are the shift times
Day, Start, End

The formula starts in Row T5, so the reference to T4 is the previous cell row
seems to work OK , and a much simpler nest of IFs

=IF(NOT(ISERROR(INDEX($N$4:$N$103,MATCH(INT(T4),$N$4:$N$103,0)))),
DATE(YEAR(T4),MONTH(T4),DAY(T4)+1)+TIME(0,0,0),

IF(AND(TIME(HOUR(T4),MINUTE(T4),0)>=INDEX(SETTINGS!$C$5:$C$11,MATCH(WEEKDAY(T4,1),SETTINGS!$B$5:$B$11,0)), TIME(HOUR(T4),MINUTE(T4),0)<INDEX(SETTINGS!$D$5:$D$11,MATCH(WEEKDAY(T4,1),SETTINGS!$B$5:$B$11,0))),

T4+(1/24/60),

IF( TIME(HOUR(T4),MINUTE(T4),0)<INDEX(SETTINGS!$C$5:$C$11,MATCH(WEEKDAY(T4,1),SETTINGS!$B$5:$B$11,0)),

DATE(YEAR(T4),MONTH(T4),DAY(T4))+TIME(HOUR(INDEX(SETTINGS!$C$5:$C$11,MATCH(ROUNDUP(MOD(WEEKDAY(T4),7.1),0),SETTINGS!$B$5:$B$11,0))),MINUTE(INDEX(SETTINGS!$C$5:$C$11,MATCH(ROUNDUP(MOD(WEEKDAY(T4),7.1),0),SETTINGS!$B$5:$B$11,0))),0),

DATE(YEAR(T4),MONTH(T4),DAY(T4)+1)+TIME(HOUR(INDEX(SETTINGS!$C$5:$C$11,MATCH(ROUNDUP(MOD(WEEKDAY(T4)+1,7.1),0),SETTINGS!$B$5:$B$11,0))),MINUTE(INDEX(SETTINGS!$C$5:$C$11,MATCH(ROUNDUP(MOD(WEEKDAY(T4)+1,7.1),0),SETTINGS!$B$5:$B$11,0))),0))))

I've been breaking my head on getting the right endtime to check if it's being exceeded.
Think/hope I got it right now
Used dummy start- and endtimes for testing/checking. Change'm to your needs.
The calculation proces is explained on the sheet referring to the columns where things happen.

Can you upload you're solution. Curious about how you've got it done.

i have cutdown as much as possible to load onto the forum

here is the verison

its uses Column G to add a shift adjustment , so that it add the number of minutes to a starttime (360 for 6hrs) if a shift starts before 6am and also does not add the time if consecutive days - ie if a Tues/Wed/Thu OFF
then the shift starts on friday at 6am - based on the offset calculations and reference in column O

Column Q is the hours to add
as you see a Friday is set to 0 - since monday is a startime of 6am
If that was changed to a shift start of 00:00
then Q would need to add the 6hrs if friday is a holiday

hope that makes some sense

i cutdown the times to be able to load here
hence the ref errors