Hi Everyone,
I have had so much success using this site but I am facing another challenge here in excel. I have version 2013 on my windows computer.
Goal: The goal is to create an easy to use schedule creator for the shifts here at my work. I am trying to make it very simple to use so that anyone here can use it.
I have a basic idea of what I need it to do and a layout, but I am not sure if it is the best.
In Cell A3, I want to be able to type in the Calendar Week number. (ex. CW 1, CW 2, CW 30). I need it to use the correlating date to set up the dates for that week. (Cell B3 is a Sunday, Cell D3 is a Monday, Cell F3 is a Tuesday....but I want it to say 19 January for example, in the Tuesday slot, 20 January in the Wednesday slot, etc.)
I then need to be able to set shift start and shift end times (shift start would be in for example, cells B6, D6, F6, etc) (shift end times would be in cells C6, E6, G6, etc.....going through the entire week). I would need the system to know that if someone is working a night shift and it goes from 9pm until 6am, that it would NOT fall into the next workday and it would need to show the hours in that same workday that the original shift began. For example (A worker starts at 9pm on Monday (3 hours until midnight), from midnight until 6am those 6 hours show on the Monday total hours since they technically started the shift on Monday)....Monday shift for that person shows 9 hours in total.
Now, the law requires us to give a 30 mins break to anyone working over 6 hours here so I also need a rule in the hours when I am making the schedule, to say that if the hours are over 6, to automatically include an unpaid break mathematically. (Ex. If I give a start time of 7am and the end time of 12pm, then the system would show 5 hours working time. If I gave a start time of 7am and an end time of 3pm, then the system would show 8-.5 of an hour for a total of 7.5 working hours.
I would like it to total the amount of hours used for the week in a column (let's say column P, for each person that is working through out the week, and then after the last employee, give a grand total of the hours being used for the entire week with all the workers included.
Then there would also need to be a section that was able to show during which time slots how many employees were working, for example....3 working between 6am and 12pm, then another one starts at 12pm so now it says we have 4, and then 1 leaves at 2pm so now it says we have 3. Then 2 more come on at 2:30 and 1 leaves so we have 4 again. Is that possible? I just want it to show certain time windows where the number actually changes, or maybe even just an easy to read time line of a 24 period that shows different blocks of how many people we have at certain times.
I would need to be able to add and remove names from the file and still have everything function. I need around 30 people I think. Has anyone seen anything like this before or can anyone help me out with how I can create this. It does not seem complicated, but at the same time I am horrible with excel and I really could use some help.
Thank you!
I have had so much success using this site but I am facing another challenge here in excel. I have version 2013 on my windows computer.
Goal: The goal is to create an easy to use schedule creator for the shifts here at my work. I am trying to make it very simple to use so that anyone here can use it.
I have a basic idea of what I need it to do and a layout, but I am not sure if it is the best.
In Cell A3, I want to be able to type in the Calendar Week number. (ex. CW 1, CW 2, CW 30). I need it to use the correlating date to set up the dates for that week. (Cell B3 is a Sunday, Cell D3 is a Monday, Cell F3 is a Tuesday....but I want it to say 19 January for example, in the Tuesday slot, 20 January in the Wednesday slot, etc.)
I then need to be able to set shift start and shift end times (shift start would be in for example, cells B6, D6, F6, etc) (shift end times would be in cells C6, E6, G6, etc.....going through the entire week). I would need the system to know that if someone is working a night shift and it goes from 9pm until 6am, that it would NOT fall into the next workday and it would need to show the hours in that same workday that the original shift began. For example (A worker starts at 9pm on Monday (3 hours until midnight), from midnight until 6am those 6 hours show on the Monday total hours since they technically started the shift on Monday)....Monday shift for that person shows 9 hours in total.
Now, the law requires us to give a 30 mins break to anyone working over 6 hours here so I also need a rule in the hours when I am making the schedule, to say that if the hours are over 6, to automatically include an unpaid break mathematically. (Ex. If I give a start time of 7am and the end time of 12pm, then the system would show 5 hours working time. If I gave a start time of 7am and an end time of 3pm, then the system would show 8-.5 of an hour for a total of 7.5 working hours.
I would like it to total the amount of hours used for the week in a column (let's say column P, for each person that is working through out the week, and then after the last employee, give a grand total of the hours being used for the entire week with all the workers included.
Then there would also need to be a section that was able to show during which time slots how many employees were working, for example....3 working between 6am and 12pm, then another one starts at 12pm so now it says we have 4, and then 1 leaves at 2pm so now it says we have 3. Then 2 more come on at 2:30 and 1 leaves so we have 4 again. Is that possible? I just want it to show certain time windows where the number actually changes, or maybe even just an easy to read time line of a 24 period that shows different blocks of how many people we have at certain times.
I would need to be able to add and remove names from the file and still have everything function. I need around 30 people I think. Has anyone seen anything like this before or can anyone help me out with how I can create this. It does not seem complicated, but at the same time I am horrible with excel and I really could use some help.
Thank you!
