Buckie

Thank you! I'll have to read more thoroughly on Harmonic numbers and harmonic series.

What about my other question about calculating time if the speed is being constantly reduced?

Yes, it's exactly as Bignose said: the total distance to travel is 142 km and every kilometer there's a sign and at every sign we drop the speed by 1 kilometer per hour, starting at 142 km/hr and ending at 1km/hr.

I think this sums it up:

https://www.wolframalpha.com/input/?i=sum&a=*C.sum-_*Calculator.dflt-&f2=1%2Fx&f=Sum.sumfunction%5Cu005f1%2Fx&f3=1&f=Sum.sumlowerlimit%5Cu005f1&f4=142&f=Sum.sumupperlimit2_142&a=*FVarOpt.1-_**-.***Sum.sumvariable---.*--

So thank you for educating me on this!

I think it's fairly easy to modify the formula to handle different cases, such as speed being reduced only by half (0.5 km/hr instead of 1 km/hr at every sign) - that would transform it into 1/(2*i), correct?

Another question that I have is can we calculate the time if the speed is being dropped gradually but still according to the same starting principle, starting at 142 km/hr and at 141 km mark already finding ourselves going at 141 km/hr. It's obvious that we're never reaching the destination but is there a way to set a cut-off point somewhere for our measurements? Perhaps at 1km mark (without changing the starting and ending conditions)? And can it also be put into a clear formula?

And a followup question: what is the way to calculate the sum? I don't know how WA do it, but is it possible to have a simple single string like (x+y)/z and have the correct result or maybe the only way to actually produce the number is to calculate intermediate results for each step and then add them up?

Hi folks. I recently had a long trip, driving and counting the remaining miles and thought up a funny riddle: suppose you drive and reach a sign that says your destination point is 142 kilometers away. You then proceed forward at a speed of 142 kilometers per hour. Next sign comes up and says the destination point is now 141 kilometers away. You immediately drop the speed to 141 kilometers per hour and drive on. You keep going like that and eventually reach a point that says "1 kilometer" and you walk towards the destination at 1 kilometer per hour, and naturally the last 1 km takes 60 minutes. So I wondered if there was a way to put it into a formula or maybe a way to calculate it easily, and some background as to what is may be called. The best I could come up with was arithmetical progression but I'm not sure we're dealing with that here. So... it will be really cool to know. I'm not skilled in mathematics but I like science.

Perhaps it may be even more interesting if we make it infinite, i.e. instead of cutting it into 1 kilometer chunks drop the speed gradually, although we'd never reach the destination then.

I think it's "formulae", not "formuli".

ewmon, thanks for the insight. Your post had really got me thinking about that. It was one of the problems I didn't approach because I had no idea on how to organize shifts in a 'humane' way. Implementing constraints for day/night shifts per month per employee is surely a valid idea but I'm not sure exactly how to implement that programmatically. It would need balancing on the rows and I'm filling it by columns, got to think more about how that could be done. The problem of ND shifts on month borders has already been solved as I'm always looking into the last day of the previous month to figure out what to place for the 1st.

I'd really appreciate if you could elaborate more on how to best fill the table. It's not clear what you mean by 'assign them to a shift on a weekly basis', do you mean we shouldn't plan the shift table for a whole month but instead for just one/two weeks? It would be very helpful if you could provide a sample row of 'ideal' shifts how you see it (day 1-31) so I could study that example. Also, I've noted your point about weekends and that is something that should also be implemented in employee's preferences.

Well, it wasn't that hard. There was one important thing that I initially missed - it's that we have strictly 4 day shifts and 3 night shifts. So instead of filling everything totally at random I started filling the table column by column to have days and nights in random places for our 14 employees per day. So the algorithm goes from record #1 to record #15 and puts "D" or "N" in a random place and then goes on and on all the time minding the total number of each type of shift for that given day. Then it proceeds to fill the next day minding the previous day's shift data (so we don't have "ND" shifts). I've also implemented a simple bias system for employees where randomly generated "D" or "N" are biased by an index number in the employee record. So for example if the bias number is "1" then only "N"s are selected and the closer the number to zero, the less nights that employee is going to receive. Nice!

One thing I'm still struggling to overcome is a large discrepancy between total number of shifts per month per employee. After auto-filling some might get 23 shifts per month while others as low as 9. The numbers obviously change and sometimes the difference isn't too big but it's still unacceptable. It happens because the generated pattern in the table is very random - lots of visually contiguous empty spaces and shifts may be clumped together. I'm looking to add more order into the algorithm but don't know where to start. Right now I generate the row to stop on (to place a day or night shift in) by looping through records until Round ( Random * 10 ) = Round ( Random * 10 ). Not optimal perhaps but it does make random stops. Any suggestions to organize the noise a bit?

PS Currently filling the table with 30 days and 14 employees takes about 20 seconds.

Just got that last one figured out too by using another bias index (If ( Random ) > bias...) for checking whether there should be added a new shift for the employee if he was already assigned a shift yesterday. Closer to one will yield a more 'uniformly' filled table while closer to zero will create more lumps.

Schrödinger's hat,Yes, the brute force idea was the first that came to mind actually. So basically just fill it with random data until it clicks into place? There are about 15 people in total so it should be doable. And thanks for the clues on the fields to research/apply!

John,

great! outsourcing it to Japan is another way to do it! I presume everybody likes sudoku and kakuro there.

Hello bright minds! I've recently started on a project to help somebody to track employee shifts data in a database in FileMaker Pro. And while the database itself is going just fine, nothing fancy here I wanted to take it further just because the improvement I have in mind is exciting to implement.

I'm not familiar with advanced mathematics so I will need more down to earth explanations. In a way this call for help is almost like saying "do this for me" and I'm sorry about that, but it's a very interesting problem to tackle nonetheless.

So with all that out of the way here's what I'm looking to implement:

We have a table with columns of days in a given month and rows of employee names. We need to fill the matrix with day and night shift data so that

- the total number of shifts per person per month is ideally 15 (but can be up to 17)

- there are always 4 persons on a day shift and 3 persons on a night shift per day (this is strict)

- no double shifts (i.e. no night shifts immediately following day shifts and no day shifts immediately following night shifts)

- there may be reserved days for certain employees when they can't go on a shift due to personal reasons (those days will be specially marked in the database)

- certain persons have strict pre-defined schedules that are manually input in the database beforehand (for example, a 10-day series of consecutive day shifts followed by vacation days)

- certain persons would want strictly day shifts or night shifts for the entire month

In other words, the solution should check every unmarked cell and find out whether it should be marked according to the preexisting (pre-filled) data.

I've attached a sample table that we use, it's filled by hand by a manager so it should give a visual idea of what I'm talking about. Don't mind the numbers much, the table usually requires manual tweaking afterwards to allow 5 days shifts for example or more than usual shifts per month for a particular person and so on. Ideally the number of specified shifts per person per month should be handled by the solution as well.

So is this even possible? Any clues are greatly appreciated!

table.pdf