Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a list of employees around 30. I need to create a simple table that will assign them a specific time that they are to be on the phone and 24 hours later excel will automatically(randomly) change the time they are to be on the phone example

        mon   tue  wed  thu  fri
John   8-9
Sam    9-10
Debbie 10-11
Susan  11-12
Josh   12-1
sean   1-2

call times are 1 hour intervals between 8am and 6pm:

8-9, 9-10, 10-11, 11-12, 12-1, 1-2, 2-3, 3-4, 4-5, 5-6

What formula would take these times and automatically change them randomly every 24 hours through out the week?

share|improve this question
    
You want 10 periods (1 hour each) to randomly be distributed over 30 employees 5 days per week. No regard as to if they have the same time as another on the same day and no regard as to whether or not you have coverage from 8-6? Or are there some other requirements you haven't told us. –  Raystafarian Mar 8 '12 at 0:27
1  
I'm curious if that's a real world timesheet management question? -Hey boss, what's with the weird schedules? -The HAT has decided so! –  mtone Mar 8 '12 at 0:40
    
@mtone "we leave the scheduling up to Ayn Rand() around here!" –  Raystafarian Mar 8 '12 at 0:52
add comment

2 Answers

I would assume you do not want overlapping hours with the same day (one employee always on the phone), so starting from Raystafarian's suggestion, I'd make a separate list of shifts that are assigned a number.

Order  Schedule
=RAND()  9-10
=RAND()  10-11
=RAND()  11-12
=RAND()  12-1
=RAND()  1-2
=RAND()  1-3
=RAND()  
=RAND()  
=RAND()  // Since you have more employees than schedules, 
=RAND()  // copy formula to as many blank results as you have employees (30).

Then, do a manual sort by the Order column, and you'll end up with 30 randomized results:

Order    Schedule
 0.3      <blank>
 0.4     1-3
 0.5      <blank>
 ..      <blank>
 ..      9-10

Now make a new table with your list of employees and use a simple formula to refer to the random schedule results, like this, so whenever you sort the values again, they will align (randomly) in front of each employee.

Employee  Today's schedule
 John    =B2
 Jane    =B3
 ..      =B4
 ..      =B5
 ..      =B6

Resulting in something like this:

Employee  Today's schedule
 John    <blank>
 Jane    12-1
 ..      <blank>
 ..      3-4
 ..      9-10

At this point, it's important that you manually copy those values (Paste Special...As Values) into a fixed daily schedule table like in your question, otherwise any new sort will replace your results permanently. Also make your that both employees lists always match in the same order.

Notice I highlighted the word manual twice. If you would like to automate these tasks you will need a VBA macro to do so, since there's no formula to automatically sortg or automatically copy formulas into fixed values somewhere else.

share|improve this answer
add comment

Why not just generate a random start time? Employees will know it's for 1 hour. =randbetween(8,17) will generate a start time between 8am and 5pm (24 hour time format)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.