Hello @lroche3262, Thanks for watching our videos. Glad to hear that. This template is useful to create an employee roaster. Keep learning Excel with ExcelDemy! Regards ExcelDemy
Very good setup. I have been using a similar setup myself. But it needs a change for me. Instead of selecting D1,D2... I want to specify an off day/weakened for each employee. So when I search for Mr.A at September month, depending of his weakened calendar will show me the dates based on weakened. So here's the setup. Jack #B8 have off-day Monday #C8. In calendar section I select month of August 2024. Now in range #E8:AI8 I want all the Monday of that month to be "Off". Next week will be night shift. So date 5,12,19,26 is his weakened, after each weakened his shift will rotate. In September 24 his weakeneds will be 2,9,16,23,30. His day/night shift will carry on, like in aug-24 say 20-25 was day shift 26 is off-day from 27-31 is night shift. When I select September -24 date 1 is his night shift 2 is off-day 3-8 night. Vice versa If you can help me with this I would be very grateful. If you have confutation please let me know.
Hello @do_the_right4272, To add an off day you can modify the current formula to check if the day is an off-day (e.g., if the weekday matches the employee's off day like Monday). =IF(OR($C8="",E$7=""),"",IF(WEEKDAY(E$7)=MATCH($C8, {"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0), "Off", IF(D8="",C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1))))) This checks if the date matches the off-day, marks it as "Off," and otherwise continues the rotation. Ensure that the formula continues the shift pattern across months by linking the shift from the previous month to the current one. This might require storing the last shift of the previous month and referencing it in the current month’s formula. Regards ExcelDemy
Hello @hugomascena, By using our existing template create the roaster then adjust the start and end time in the sheet based on your requirements. To add start and end time you will need to add two columns next to each date. Format the cells under these columns to accept time input. Then, insert the respective start and end times for each shift. Example: 03-Aug Start Time End Time D1 09:00 21:00 N1 21:00 09:00 D3 09:00 21:00 Regards ExcelDemy
Hello! Great video tutorials... mind blowing! I don't have any benefit or any staff under my supervision, I'm just doing this for the learning alone, trying to build the smartest apps and figure out the size of excel. I'm stuck on step 6 and specifically on writing the type =IFERROR(DATEVALUE(J4&W2&AC2),"") even in a similar type like =DATE(AC2;W2;J4) or =DATEVALUE(J4&W2&AC2), it shows #VALUE as an error and not the date serial number where by converting that, I will get the correct date. I have tried formatting dates, region, etc. I work with office 2021, in the Greek version and even though I change e.g. Greek months in English, he continues to see it as an error. However, I should note that any formula I mentioned above works perfectly if the month is expressed by a number (eg: for the month of March I put the number 3, April 4, December 12, etc.), then in the formula =IFERROR(DATEVALUE( J4&W2&AC2),"") returns the date normally and correctly. Sorry for being tired, I just don't like to give up! Thanks and keep uploading videos..
Dear @jfna, Thank you for your question. We appreciate your feedback. Regarding the use of DATE and DATEVALUE functions. If you use the DATE function it will return an error for the month argument since the DATE function accepts a number from 1 to 12 as its month argument. Therefore, if you want to use the DATE function then the month argument has to be a number (1-12) instead of the month name. I have added a screenshot with annotation in the Excel file below. As for the DATEVALUE function, I applied only the DATEVALUE function, however, I did not receive any #VALUE! error, again you can find it in the Excel file given below. Now, I’m not sure as to why this is the case, but one possible explanation may be the difference in Excel versions, I’m using Excel 365. Since you’ve already tried changing the date format without any luck, perhaps you could use the IFERROR and DATEVALUE functions or you can change the month argument in the DATE function to a corresponding month number. Whatever works, right? Excel file: www.exceldemy.com/wp-content/uploads/2023/11/Making-a-Roster-1.xlsm Hopefully, this answers your query. Make sure to stay connected with Exceldemy!❤. Have a good day. Regards, Exceldemy
Thank you for your answer and I will agree about the difference in the versions of excel, since I found that in functions instead of a comma (,) I have to put (;).... !!! Should something like this be to blame... However, my purpose has been achieved since I finished your application and it works perfectly [(apart from my initial problem that (Month display cell)], which looks like a number. Thanks a lot!😀😀😀
Dear, Thanks for your question! We have reviewed your requirements and make adjustments accordingly. You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/05/Lopsidedxi-SOLVED.xlsm
Thank you, It was really helpful, I did it by myself with your video help, but you didn't add the weekend to the sheet as I want it to skip the weekends and count after it, can you help me with that as I want to keep only Friday as the weekend
Dear, thanks for your compliment! We have reviewed your requirements and adjusted an Excel file based on your needs. In particular, we added weekend information to the Settings sheet and created a Named Range for weekends. Later, we had to modify the date formula to skip Friday. You can download the file: www.exceldemy.com/wp-content/uploads/2024/06/Ebrahim-Al-Mulla-SOLVED.xlsm
This is good. But I have a more complex case with 3 shifts with 24/7 model and 2 roles like a supervisor and associate along with 5 days work week followed by 2 week off's a any suggestions ?
Dear, Thanks for your compliment! We have reviewed your problem and demonstrated the situation. You can download the Excel file: www.exceldemy.com/wp-content/uploads/2024/06/Abhinandan-Karki-SOLVED.xlsm
Hello sir, thank you so much for your clear guidance! I have one question. Would you be able to advise on the following? I want to schedule for both Day and Night shift. Normally the shift goes like this, Day-Day-Night-Night-Off-Off. But max people per night is 2. How do i make it that if there are already 2 people scheduled for a night, the next employee can be scheduled as Day-Day-Day-Night-Off-Off. It does not matter if more are scheduled for day. Very small team of 8 personnel. Thank you!!!
Dear, Thanks for your compliments! You are very welcome. We have reviewed your requirements and developed an idea using an extra Excel VBA sub-procedure that will fulfil your goal. All the necessary adjustments are made for the sub-procedure to work appropriately within an improved file. Please check the following: www.exceldemy.com/wp-content/uploads/2024/05/Make-Employee-Roster-Template-in-Excel.gif You can download the improved file: www.exceldemy.com/wp-content/uploads/2024/05/Shazni-Azmi-SOLVED.xlsm
Thanks for a great video how would i enter shifts for employees that work 8 hrs shift with diffferent rotations and some are part-timers some work saturdays and their rotations report after 12 weeks so it continues throughout the different years but rotations are 12 weeks only
Hello @rupinderwalia4000, Thank you for your kind words! To enter shifts for employees working 8-hour shifts with different rotations, including part-timers and those who work on Saturdays, you can follow this approach: 8-Hour Shifts: Set up a schedule where each shift lasts 8 hours. Different Rotations: Create different rotation patterns for each group of employees. Part-Timers: Designate specific shifts for part-time employees within your schedule. Saturday Work: Include Saturday shifts for those employees who work on weekends. 12-Week Rotations: Implement a 12-week rotation cycle that repeats every 12 weeks. To manage this complexity, it's helpful to create three separate workbooks: Regular Employees: For full-time employees with standard shifts. www.exceldemy.com/wp-content/uploads/2024/07/Make-a-Roster-for-regular-employee.xlsm Part-Time Employees: For employees working fewer hours or days. www.exceldemy.com/wp-content/uploads/2024/07/Make-a-Roster-for-part-time-employee.xlsm Saturday Employees: For those who have shifts on Saturdays. www.exceldemy.com/wp-content/uploads/2024/07/Make-a-Roster-for-saturday-employee-1.xlsm By organizing it this way, you can ensure clarity and maintain a manageable schedule throughout the years. Regards ExcelDemy
This was so helpful, thank you so much. But kindly assist me , why the summary is not working. When i put the number of leaves or the symbols it does'nt change on the summary. its remains zeros. What can do to fix this?
Dear, thank you for your kind words. Your appreciation means a lot to us. We would appreciate it if you could describe the problem and share the workbook you are working on in the ExcelDemy Forum. ExcelDemy Forum: exceldemy.com/forum/
Dear @alaminkhan7214, Thank you for your question. We appreciate your feedback. Regarding your question on how to solve the issue of employees taking leaves. You can follow our leave tracker video and download the template from the video description to track your employee leaves. Leave tracker video: ua-cam.com/video/cKvnW9vcQ9E/v-deo.html Make sure to stay connected with Exceldemy!🥳❤. Have a good day. Regards, Exceldemy
hi, I just finished watching the video and it is really useful. thank you so much. but I was wondering what would be the formula if the shift contains Day, Afternoon, Night & Off (2 days). Thanks!
Dear @ditamaura, Thank you for your question. We appreciate your feedback. Regarding your question on how to modify the formula if the shift contains Day, Afternoon, Night, and Off (2 days). You don’t have to modify the formula. Just make the following changes in the “Settings Worksheet”: First, in Column E, add the “Afternoon” and “Off” shifts in the “Shifts Type” column. Then, in Column F, add the “Shift Codes” serially, for example, D1, A1, N1, O1, O2. Here, D refers to the Day shift, A refers to the Afternoon shift, N for the Night shift, and O for Off. Now if you select a shift for an employee in the “Roster Worksheet” this cycle will keep repeating for the entire month. Lastly, you can follow a similar procedure as shown in the video to apply the Conditional Formatting and the Totals table in the roster. For your ease of reference, we’ve added a sample Excel file with all the changes described above. Excel file: www.exceldemy.com/wp-content/uploads/2023/11/Making-a-Roster.xlsm Hopefully, this answers your question. Make sure to stay connected with Exceldemy! 🎉❤. Have a good day. Regards, Exceldemy Team!
@@exceldemy2006 Dear Sir, i have also question and need your help, i try to change some items on shift codes (settings sheet) with a duplicate, but the problem when checking on the roster, it doesnt follow the sequence anymore, kindly correct the formula, =IF(OR($C8="",E$7=""),"",IF(D8="",C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1)))), really appreciate it. For example on shift codes the original is D1,A1,N1,O1,O2,D2,A2,N2,O3,O4, the rosters run perfectly according to that sequence. but if i change and put like this D1,A1,N1,O1, and D1 again or O1 or A1 (duplicate shift), the roster not showing the correct sequence as per the revised shift codes.
@@eraltolentino9669 Dear, Thanks for sharing your problem! We have reviewed your problem. For the formula to work properly, it is important to keep the shifts unique. However, you can repeat the shift by applying a trick; you only need to add extra space at the end when repeating the shifts in the settings worksheet. Don't worry! You can download the workbook used to solve your problem: www.exceldemy.com/wp-content/uploads/2024/05/ERAL-TOLENTINO-SOLVED.xlsm
Hello @patrycjaniedzwiecka8622, Here, I define each shift pattern in a separate range. For example: Pattern 1: M-F, Sat-Sun off Pattern 2: M-Sat, Sun off, Pattern 3: Tues-Sat, Sun-Mon off Then created a dynamic formula to assign shifts based on the shift patterns. Download your roaster file from here: www.exceldemy.com/wp-content/uploads/2024/07/Roaster-Based-on-Different-Weekday-and-Weekends.xlsx Regards ExcelDemy
How would I go about something like this but be able to have each employee show their days off on the calendar? Also if I have 3 different groups of employees like supervisor, quality analyst, and specialist all doing 4 different shifts throughout the day and night (day, swing, sunrise6, sunrise8 )
Hello @josephinebejarano184, You can show each employee's day off in the calendar. But for 3 different groups, you need to create 3 different Excel workbooks. Here, I created a Template for the Supervisor group and changed the Settings sheet. First, insert your shift name in the Shift Codes column including OFF-1 and OFF-2 Then, insert the employee name of particular groups in the Employee Name column. Do similar things in 2 different workbooks for Quality Analysts and Specialist. Download the Template from here: www.exceldemy.com/wp-content/uploads/2024/06/Make-a-Roster-based-on-Department.xlsm Regards ExcelDemy
Hello @gitanjalipuri9216, You can't paste the formula to other dates. Since our Excel file is fully automatic, users can select the month and year to populate the month sheet. Please select the month name from the drop-down of the Months column and select the year from the drop-down of the Year column. It will auto-populate your respective month's sheet. I am attaching the link to images to see how to select months and years. How to Select Year: Year: www.exceldemy.com/wp-content/uploads/2024/07/Select-Year.png How to select Month: www.exceldemy.com/wp-content/uploads/2024/07/Select-Month.png Download the updated Excel file: www.exceldemy.com/wp-content/uploads/2024/07/Updated-Roaster-Sheet.xlsm N.B: If this solution doesn't work for you, please share the specific formula with the Excel file that’s causing issues, I'd be happy to take a closer look and provide more targeted advice. Regards ExcelDemy
Hello @sultanalsalti3056 The formulas are copied from the given article. You will find all the formulas in our article: www.exceldemy.com/how-to-make-a-roster-in-excel/ I am attaching the formulas used for each range here: Employee_List (employee names): =OFFSET(Settings!$G$4,1,0,COUNTA(Settings!$G:$G)-1,1) Shift_Codes (Shift Codes): =OFFSET(Settings!$F$4,1,0,COUNTA(Settings!$F:$F)-1,1) YearList (Years): =OFFSET(Settings!$C$4,1,0,COUNTA(Settings!$C:$C)-1,1) Regards ExcelDemy
Dear @lagiusamate-pio4352, Thank you for your feedback. Regarding your question on manually typing out the *OFFSET* and *COUNTA* functions when defining the Named Ranges. Actually, you can copy this formula from the article linked in the reply or from the video description. Article link: www.exceldemy.com/how-to-make-a-roster-in-excel/#:~:text=Employee_List%20(employee%20names)%3A Make sure to stay connected with Exceldemy!🎉❤. Have a good day. Regards, Exceldemy
Dear, Thanks for sharing your requirements! As you requested, we have modified the template based on your specifications. You can download the Excel file: www.exceldemy.com/wp-content/uploads/2024/05/Ahl-Vincent-Arela-SOLVED.xlsm
Dear Sir, i have also question and need your help, i try to change some items on shift codes (settings sheet) with a duplicate, but the problem when checking on the roster, it doesnt follow the sequence anymore, kindly correct the formula, =IF(OR($C8="",E$7=""),"",IF(D8="",C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1)))), really appreciate it. For example on shift codes the original is D1,A1,N1,O1,O2,D2,A2,N2,O3,O4, the rosters run perfectly according to that sequence. but if i change and put like this D1,A1,N1,O1, and D1 again or O1 or A1 (duplicate shift), the roster not showing the correct sequence as per the revised shift codes. @ExcelDemy
Dear Sir, i have also question and need your help, i try to change some items on shift codes (settings sheet) with a duplicate, but the problem when checking on the roster, it doesnt follow the sequence anymore, kindly correct the formula, =IF(OR($C8="",E$7=""),"",IF(D8="",C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1)))), really appreciate it. For example on shift codes the original is D1,A1,N1,O1,O2,D2,A2,N2,O3,O4, the rosters run perfectly according to that sequence. but if i change and put like this D1,A1,N1,O1, and D1 again or O1 or A1 (duplicate shift), the roster not showing the correct sequence as per the revised shift codes.
Dear@@eraltolentino9669, Thank you for your question. Regarding your question on duplicate shifts not showing the correct sequence. You asked an interesting question and guess what, the Exceldemy Forum is here to provide the answers to your questions, and it’s free of cost. All you need to do is sign up in the Exceldemy Forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need. Exceldemy Forum: exceldemy.com/forum/ Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems. Make sure to stay connected with Exceldemy!❤🥳. Regards, Exceldemy
Dear Sir,I have also question and need ur help🙏 If my employee roster template's day is from top to the bottom Not left to the right. The formula I have no idea 😢 Thank you so much
Dear @mingyicheng5446, Thank you for your question. We appreciate your interest in rosters. Regarding the layout of your roster. A horizontal layout for a roster can display more information and minimize scrolling. Now having a vertical layout is also fine. However, the formulas applied here are especially made for this roster. If you need help with your roster, then you can look up the Exceldemy Forum. All you need to do is sign up in the forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need. Exceldemy Forum: exceldemy.com/forum/ Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems. Make sure to stay connected with Exceldemy! 🎉❤. Regards, Exceldemy
Dear @GSMHMD, Thank you for your question. We appreciate your interest in rosters. Regarding your question on planning your group work for 28 days. You asked an interesting question and guess what, the Exceldemy Forum is here to provide the answers to your questions, and it’s free of cost. All you need to do is sign up in the Exceldemy Forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need. Exceldemy Forum: exceldemy.com/forum/ Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems. Make sure to stay connected with Exceldemy!❤️🥳. Have a good day. Regards, Exceldemy
Dear @user-zy9ne2eg2p, Thanks for your feedback. Regarding your question on how to add more employees. If you download the Excel file from the video description or from the link given below, you’ll find a “Settings” worksheet. This worksheet contains the source data for the roster. In column G of this worksheet, there is an “Employee Name” column. Just add the names to the “Employee Name” column. Now, if you head over to the Roster worksheet and click on any drop-down in the “Employee” column you’ll find the newly added names appear on that list. Feel free to download the Excel file, you can see I have added a couple of names. Excel file: www.exceldemy.com/wp-content/uploads/2024/01/Make-a-Roster.xlsm Make sure to stay connected with ExcelDemy!🥳❤. Have a good day. Regards, ExcelDemy
Hi Sir, How to Make a shift for 21 Employee Schedule For 1 People = 5 Shift and 2 Off WeekDay : Shift 1 = 2 People Shift 2 = 9 People Shift 3 = 2 People Shift 4 = 4 People Shift 5 = 2 People Weekend : Shift 1 = 2 People Shift 2 = 4 People Shift 3 = 1 People Shift 4 = 4 People Shift 5 = 2 People
Dear @EzStory, Thank you for your question. We appreciate your interest in rosters. Regarding your question on how to make a shift for 21 Employees. You asked an interesting question and guess what, the Exceldemy Forum is here to provide the answers to your questions, and it’s free of cost. All you need to do is sign up in the Exceldemy Forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need. Exceldemy Forum: exceldemy.com/forum/ Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems. Make sure to stay connected with Exceldemy! 🎉❤. Have a good day. Regards, Exceldemy Team!
Wow! Nearly what I am looking for !
Hello @lroche3262,
Thanks for watching our videos. Glad to hear that. This template is useful to create an employee roaster. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Very good setup. I have been using a similar setup myself. But it needs a change for me. Instead of selecting D1,D2... I want to specify an off day/weakened for each employee. So when I search for Mr.A at September month, depending of his weakened calendar will show me the dates based on weakened.
So here's the setup. Jack #B8 have off-day Monday #C8. In calendar section I select month of August 2024. Now in range #E8:AI8 I want all the Monday of that month to be "Off". Next week will be night shift. So date 5,12,19,26 is his weakened, after each weakened his shift will rotate. In September 24 his weakeneds will be 2,9,16,23,30. His day/night shift will carry on, like in aug-24 say 20-25 was day shift 26 is off-day from 27-31 is night shift. When I select September -24 date 1 is his night shift 2 is off-day 3-8 night. Vice versa
If you can help me with this I would be very grateful. If you have confutation please let me know.
Hello @do_the_right4272,
To add an off day you can modify the current formula to check if the day is an off-day (e.g., if the weekday matches the employee's off day like Monday).
=IF(OR($C8="",E$7=""),"",IF(WEEKDAY(E$7)=MATCH($C8, {"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0), "Off",
IF(D8="",C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1)))))
This checks if the date matches the off-day, marks it as "Off," and otherwise continues the rotation.
Ensure that the formula continues the shift pattern across months by linking the shift from the previous month to the current one. This might require storing the last shift of the previous month and referencing it in the current month’s formula.
Regards
ExcelDemy
Thanks man, just what im looking for.
How could I possibly add time on this, for example 3/09 from 09:00 to 21:00?
Hello @hugomascena,
By using our existing template create the roaster then adjust the start and end time in the sheet based on your requirements.
To add start and end time you will need to add two columns next to each date. Format the cells under these columns to accept time input.
Then, insert the respective start and end times for each shift.
Example:
03-Aug Start Time End Time
D1 09:00 21:00
N1 21:00 09:00
D3 09:00 21:00
Regards
ExcelDemy
Hello! Great video tutorials... mind blowing! I don't have any benefit or any staff under my supervision, I'm just doing this for the learning alone, trying to build the smartest apps and figure out the size of excel. I'm stuck on step 6 and specifically on writing the type =IFERROR(DATEVALUE(J4&W2&AC2),"") even in a similar type like =DATE(AC2;W2;J4) or =DATEVALUE(J4&W2&AC2), it shows #VALUE as an error and not the date serial number where by converting that, I will get the correct date. I have tried formatting dates, region, etc. I work with office 2021, in the Greek version and even though I change e.g. Greek months in English, he continues to see it as an error. However, I should note that any formula I mentioned above works perfectly if the month is expressed by a number (eg: for the month of March I put the number 3, April 4, December 12, etc.), then in the formula =IFERROR(DATEVALUE( J4&W2&AC2),"") returns the date normally and correctly. Sorry for being tired, I just don't like to give up! Thanks and keep uploading videos..
Dear @jfna,
Thank you for your question. We appreciate your feedback. Regarding the use of DATE and DATEVALUE functions.
If you use the DATE function it will return an error for the month argument since the DATE function accepts a number from 1 to 12 as its month argument. Therefore, if you want to use the DATE function then the month argument has to be a number (1-12) instead of the month name.
I have added a screenshot with annotation in the Excel file below.
As for the DATEVALUE function, I applied only the DATEVALUE function, however, I did not receive any #VALUE! error, again you can find it in the Excel file given below. Now, I’m not sure as to why this is the case, but one possible explanation may be the difference in Excel versions, I’m using Excel 365.
Since you’ve already tried changing the date format without any luck, perhaps you could use the IFERROR and DATEVALUE functions or you can change the month argument in the DATE function to a corresponding month number. Whatever works, right?
Excel file: www.exceldemy.com/wp-content/uploads/2023/11/Making-a-Roster-1.xlsm
Hopefully, this answers your query. Make sure to stay connected with Exceldemy!❤. Have a good day.
Regards,
Exceldemy
Thank you for your answer and I will agree about the difference in the versions of excel, since I found that in functions instead of a comma (,) I have to put (;).... !!! Should something like this be to blame... However, my purpose has been achieved since I finished your application and it works perfectly [(apart from my initial problem that (Month display cell)], which looks like a number. Thanks a lot!😀😀😀
Thanks for your appreciation. It means a lot to us.
Hey,
Thank you for this. How can you set up 4 working days on the weekday and 1 dayoff on a weekday and no work for the weekend
Dear, Thanks for your question! We have reviewed your requirements and make adjustments accordingly. You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/05/Lopsidedxi-SOLVED.xlsm
Thank you, It was really helpful, I did it by myself with your video help, but you didn't add the weekend to the sheet as I want it to skip the weekends and count after it, can you help me with that as I want to keep only Friday as the weekend
Dear, thanks for your compliment!
We have reviewed your requirements and adjusted an Excel file based on your needs. In particular, we added weekend information to the Settings sheet and created a Named Range for weekends. Later, we had to modify the date formula to skip Friday.
You can download the file: www.exceldemy.com/wp-content/uploads/2024/06/Ebrahim-Al-Mulla-SOLVED.xlsm
Very useful.
Thanks.
Thank you! Stay connected with Exceldemy for more helpful content! 🎉❤
Regards,
Exceldemy Team!
This is good. But I have a more complex case with 3 shifts with 24/7 model and 2 roles like a supervisor and associate along with 5 days work week followed by 2 week off's a
any suggestions ?
Dear, Thanks for your compliment! We have reviewed your problem and demonstrated the situation. You can download the Excel file: www.exceldemy.com/wp-content/uploads/2024/06/Abhinandan-Karki-SOLVED.xlsm
Interesting thanks for this
@@09Abhinandan Dear, you are very welcome!
Hello sir, thank you so much for your clear guidance! I have one question. Would you be able to advise on the following?
I want to schedule for both Day and Night shift. Normally the shift goes like this, Day-Day-Night-Night-Off-Off. But max people per night is 2. How do i make it that if there are already 2 people scheduled for a night, the next employee can be scheduled as Day-Day-Day-Night-Off-Off. It does not matter if more are scheduled for day. Very small team of 8 personnel. Thank you!!!
Dear, Thanks for your compliments! You are very welcome.
We have reviewed your requirements and developed an idea using an extra Excel VBA sub-procedure that will fulfil your goal. All the necessary adjustments are made for the sub-procedure to work appropriately within an improved file. Please check the following: www.exceldemy.com/wp-content/uploads/2024/05/Make-Employee-Roster-Template-in-Excel.gif
You can download the improved file: www.exceldemy.com/wp-content/uploads/2024/05/Shazni-Azmi-SOLVED.xlsm
@@exceldemy2006wow. you guys are amazing. thank you 🤗
You are most welcome. Please stay connected with us.
Thanks for a great video how would i enter shifts for employees that work 8 hrs shift with diffferent rotations and some are part-timers some work saturdays and their rotations report after 12 weeks so it continues throughout the different years but rotations are 12 weeks only
Hello @rupinderwalia4000,
Thank you for your kind words! To enter shifts for employees working 8-hour shifts with different rotations, including part-timers and those who work on Saturdays, you can follow this approach:
8-Hour Shifts: Set up a schedule where each shift lasts 8 hours.
Different Rotations: Create different rotation patterns for each group of employees.
Part-Timers: Designate specific shifts for part-time employees within your schedule.
Saturday Work: Include Saturday shifts for those employees who work on weekends.
12-Week Rotations: Implement a 12-week rotation cycle that repeats every 12 weeks.
To manage this complexity, it's helpful to create three separate workbooks:
Regular Employees: For full-time employees with standard shifts.
www.exceldemy.com/wp-content/uploads/2024/07/Make-a-Roster-for-regular-employee.xlsm
Part-Time Employees: For employees working fewer hours or days.
www.exceldemy.com/wp-content/uploads/2024/07/Make-a-Roster-for-part-time-employee.xlsm
Saturday Employees: For those who have shifts on Saturdays.
www.exceldemy.com/wp-content/uploads/2024/07/Make-a-Roster-for-saturday-employee-1.xlsm
By organizing it this way, you can ensure clarity and maintain a manageable schedule throughout the years.
Regards
ExcelDemy
This was so helpful, thank you so much. But kindly assist me , why the summary is not working. When i put the number of leaves or the symbols it does'nt change on the summary. its remains zeros. What can do to fix this?
Dear, thank you for your kind words. Your appreciation means a lot to us. We would appreciate it if you could describe the problem and share the workbook you are working on in the ExcelDemy Forum.
ExcelDemy Forum: exceldemy.com/forum/
Thanks for making a great video. It is grateful to me. But I need some help if the employee takes leave then how do we solve this issue.
Dear @alaminkhan7214,
Thank you for your question. We appreciate your feedback. Regarding your question on how to solve the issue of employees taking leaves. You can follow our leave tracker video and download the template from the video description to track your employee leaves.
Leave tracker video: ua-cam.com/video/cKvnW9vcQ9E/v-deo.html
Make sure to stay connected with Exceldemy!🥳❤. Have a good day.
Regards,
Exceldemy
hi, I just finished watching the video and it is really useful. thank you so much. but I was wondering what would be the formula if the shift contains Day, Afternoon, Night & Off (2 days). Thanks!
Dear @ditamaura,
Thank you for your question. We appreciate your feedback. Regarding your question on how to modify the formula if the shift contains Day, Afternoon, Night, and Off (2 days). You don’t have to modify the formula. Just make the following changes in the “Settings Worksheet”:
First, in Column E, add the “Afternoon” and “Off” shifts in the “Shifts Type” column. Then, in Column F, add the “Shift Codes” serially, for example, D1, A1, N1, O1, O2. Here, D refers to the Day shift, A refers to the Afternoon shift, N for the Night shift, and O for Off.
Now if you select a shift for an employee in the “Roster Worksheet” this cycle will keep repeating for the entire month. Lastly, you can follow a similar procedure as shown in the video to apply the Conditional Formatting and the Totals table in the roster.
For your ease of reference, we’ve added a sample Excel file with all the changes described above.
Excel file: www.exceldemy.com/wp-content/uploads/2023/11/Making-a-Roster.xlsm
Hopefully, this answers your question. Make sure to stay connected with Exceldemy! 🎉❤. Have a good day.
Regards,
Exceldemy Team!
@@exceldemy2006 Dear Sir, i have also question and need your help, i try to change some items on shift codes (settings sheet) with a duplicate, but the problem when checking on the roster, it doesnt follow the sequence anymore, kindly correct the formula, =IF(OR($C8="",E$7=""),"",IF(D8="",C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1)))), really appreciate it.
For example on shift codes the original is D1,A1,N1,O1,O2,D2,A2,N2,O3,O4, the rosters run perfectly according to that sequence.
but if i change and put like this D1,A1,N1,O1, and D1 again or O1 or A1 (duplicate shift), the roster not showing the correct sequence as per the revised shift codes.
@@eraltolentino9669 Dear, Thanks for sharing your problem! We have reviewed your problem. For the formula to work properly, it is important to keep the shifts unique. However, you can repeat the shift by applying a trick; you only need to add extra space at the end when repeating the shifts in the settings worksheet.
Don't worry! You can download the workbook used to solve your problem: www.exceldemy.com/wp-content/uploads/2024/05/ERAL-TOLENTINO-SOLVED.xlsm
Is it possible to create different shift patterns for example m-f, s-sun off, every other week m-sat, sun off, tues-Sat?
Hello @patrycjaniedzwiecka8622,
Here, I define each shift pattern in a separate range. For example:
Pattern 1: M-F, Sat-Sun off
Pattern 2: M-Sat, Sun off,
Pattern 3: Tues-Sat, Sun-Mon off
Then created a dynamic formula to assign shifts based on the shift patterns.
Download your roaster file from here: www.exceldemy.com/wp-content/uploads/2024/07/Roaster-Based-on-Different-Weekday-and-Weekends.xlsx
Regards
ExcelDemy
How would I go about something like this but be able to have each employee show their days off on the calendar? Also if I have 3 different groups of employees like supervisor, quality analyst, and specialist all doing 4 different shifts throughout the day and night (day, swing, sunrise6, sunrise8 )
Hello @josephinebejarano184,
You can show each employee's day off in the calendar. But for 3 different groups, you need to create 3 different Excel workbooks.
Here, I created a Template for the Supervisor group and changed the Settings sheet.
First, insert your shift name in the Shift Codes column including OFF-1 and OFF-2
Then, insert the employee name of particular groups in the Employee Name column.
Do similar things in 2 different workbooks for Quality Analysts and Specialist.
Download the Template from here: www.exceldemy.com/wp-content/uploads/2024/06/Make-a-Roster-based-on-Department.xlsm
Regards
ExcelDemy
Hi, very useful. However, i am stuck, my formula is throwing error when pasted to other days/ dates. can you pls help
Hello @gitanjalipuri9216,
You can't paste the formula to other dates. Since our Excel file is fully automatic, users can select the month and year to populate the month sheet.
Please select the month name from the drop-down of the Months column and select the year from the drop-down of the Year column. It will auto-populate your respective month's sheet.
I am attaching the link to images to see how to select months and years.
How to Select Year: Year: www.exceldemy.com/wp-content/uploads/2024/07/Select-Year.png
How to select Month: www.exceldemy.com/wp-content/uploads/2024/07/Select-Month.png
Download the updated Excel file:
www.exceldemy.com/wp-content/uploads/2024/07/Updated-Roaster-Sheet.xlsm
N.B: If this solution doesn't work for you, please share the specific formula with the Excel file that’s causing issues, I'd be happy to take a closer look and provide more targeted advice.
Regards
ExcelDemy
IN THE FIRST YOU IMSERT YOUR FARMULA USING OFFSET BUT HOW DID YOU COPY IT > ?
Hello @sultanalsalti3056
The formulas are copied from the given article. You will find all the formulas in our article:
www.exceldemy.com/how-to-make-a-roster-in-excel/
I am attaching the formulas used for each range here:
Employee_List (employee names):
=OFFSET(Settings!$G$4,1,0,COUNTA(Settings!$G:$G)-1,1)
Shift_Codes (Shift Codes):
=OFFSET(Settings!$F$4,1,0,COUNTA(Settings!$F:$F)-1,1)
YearList (Years):
=OFFSET(Settings!$C$4,1,0,COUNTA(Settings!$C:$C)-1,1)
Regards
ExcelDemy
Do we manually type out the offset & count define name functions?
Dear @lagiusamate-pio4352,
Thank you for your feedback. Regarding your question on manually typing out the *OFFSET* and *COUNTA* functions when defining the Named Ranges. Actually, you can copy this formula from the article linked in the reply or from the video description.
Article link: www.exceldemy.com/how-to-make-a-roster-in-excel/#:~:text=Employee_List%20(employee%20names)%3A
Make sure to stay connected with Exceldemy!🎉❤. Have a good day.
Regards,
Exceldemy
Hi sir how to modify your template if we have 3 shift which is label A,B,C every shift change after 6 days and then 2 day off can you help please.
Dear, Thanks for sharing your requirements! As you requested, we have modified the template based on your specifications.
You can download the Excel file: www.exceldemy.com/wp-content/uploads/2024/05/Ahl-Vincent-Arela-SOLVED.xlsm
Dear Sir, i have also question and need your help, i try to change some items on shift codes (settings sheet) with a duplicate, but the problem when checking on the roster, it doesnt follow the sequence anymore, kindly correct the formula, =IF(OR($C8="",E$7=""),"",IF(D8="",C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1)))), really appreciate it.
For example on shift codes the original is D1,A1,N1,O1,O2,D2,A2,N2,O3,O4, the rosters run perfectly according to that sequence.
but if i change and put like this D1,A1,N1,O1, and D1 again or O1 or A1 (duplicate shift), the roster not showing the correct sequence as per the revised shift codes.
@ExcelDemy
Dear Sir, i have also question and need your help, i try to change some items on shift codes (settings sheet) with a duplicate, but the problem when checking on the roster, it doesnt follow the sequence anymore, kindly correct the formula, =IF(OR($C8="",E$7=""),"",IF(D8="",C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1)))), really appreciate it.
For example on shift codes the original is D1,A1,N1,O1,O2,D2,A2,N2,O3,O4, the rosters run perfectly according to that sequence.
but if i change and put like this D1,A1,N1,O1, and D1 again or O1 or A1 (duplicate shift), the roster not showing the correct sequence as per the revised shift codes.
Dear@@eraltolentino9669,
Thank you for your question. Regarding your question on duplicate shifts not showing the correct sequence. You asked an interesting question and guess what, the Exceldemy Forum is here to provide the answers to your questions, and it’s free of cost.
All you need to do is sign up in the Exceldemy Forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need.
Exceldemy Forum: exceldemy.com/forum/
Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems.
Make sure to stay connected with Exceldemy!❤🥳.
Regards,
Exceldemy
Dear Sir,I have also question and need ur help🙏
If my employee roster template's day
is from top to the bottom Not left to the right.
The formula I have no idea 😢
Thank you so much
Dear @mingyicheng5446,
Thank you for your question. We appreciate your interest in rosters. Regarding the layout of your roster. A horizontal layout for a roster can display more information and minimize scrolling. Now having a vertical layout is also fine. However, the formulas applied here are especially made for this roster. If you need help with your roster, then you can look up the Exceldemy Forum.
All you need to do is sign up in the forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need.
Exceldemy Forum: exceldemy.com/forum/
Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems.
Make sure to stay connected with Exceldemy! 🎉❤.
Regards,
Exceldemy
Thanks, can you help me to make a planing of my group work for 28 days for 28 days on
Dear @GSMHMD,
Thank you for your question. We appreciate your interest in rosters. Regarding your question on planning your group work for 28 days. You asked an interesting question and guess what, the Exceldemy Forum is here to provide the answers to your questions, and it’s free of cost.
All you need to do is sign up in the Exceldemy Forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need.
Exceldemy Forum: exceldemy.com/forum/
Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems. Make sure to stay connected with Exceldemy!❤️🥳.
Have a good day.
Regards,
Exceldemy
hwo to add more employee?
Dear @user-zy9ne2eg2p,
Thanks for your feedback. Regarding your question on how to add more employees.
If you download the Excel file from the video description or from the link given below, you’ll find a “Settings” worksheet. This worksheet contains the source data for the roster. In column G of this worksheet, there is an “Employee Name” column. Just add the names to the “Employee Name” column.
Now, if you head over to the Roster worksheet and click on any drop-down in the “Employee” column you’ll find the newly added names appear on that list. Feel free to download the Excel file, you can see I have added a couple of names.
Excel file: www.exceldemy.com/wp-content/uploads/2024/01/Make-a-Roster.xlsm
Make sure to stay connected with ExcelDemy!🥳❤. Have a good day.
Regards,
ExcelDemy
Hi Sir, How to Make a shift for 21 Employee
Schedule For 1 People = 5 Shift and 2 Off
WeekDay :
Shift 1 = 2 People
Shift 2 = 9 People
Shift 3 = 2 People
Shift 4 = 4 People
Shift 5 = 2 People
Weekend :
Shift 1 = 2 People
Shift 2 = 4 People
Shift 3 = 1 People
Shift 4 = 4 People
Shift 5 = 2 People
Dear @EzStory,
Thank you for your question. We appreciate your interest in rosters. Regarding your question on how to make a shift for 21 Employees. You asked an interesting question and guess what, the Exceldemy Forum is here to provide the answers to your questions, and it’s free of cost.
All you need to do is sign up in the Exceldemy Forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need.
Exceldemy Forum: exceldemy.com/forum/
Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems. Make sure to stay connected with Exceldemy! 🎉❤.
Have a good day.
Regards,
Exceldemy Team!