How do I use RAND to generate times for 8 hour shifts

[Guest]

I am trying to generate random times for shift workers to accomplish a task.
3 shifts, 0600-1400, 1400-2200, 2200-0600. I know how to do it for a 24 hour
period but cannot break it down into 8 hour segments.

 

[Biff]

Can you provide more detail? What exactly are you trying
to do?

Biff

 

[Myrna Larson]

You said "times ... to accomplish a task". To me that means you are
subtracting the starting time from the ending time. Is that difference somehow
related to the shift? Are the 3rd shift people slower than the day or evening
shifts?

OTOH, if you are talking about the starting or ending time, that does change
with the shift.

To get a random time that does not exceed 8 hours, the formula =RAND()*.33

Since you want the time to begin at 6:00 instead of midnight, add 0.25, i.e.
=RAND()*.33+.25

To get a time between 14:00 and 22:00, add 14/24 instead of 0.25
To get a time between 22:00 and 6:00 the next day, add 22/24 instead of 0.25

Format the cells as hh:mm.