#258

In this tutorial, you can learn how to make an automated multiple shift Duty Roster / Shift Schedule for Staff in Excel. This will be a fantastic tutorial maybe you have not seen this type of tutorial for creating a Duty roster. So stay tuned and learn.
Purchase & Download : bit.ly/41nh3PO
For more templates Visit: sahacomputer.co.in
~:Related Video Links:~
Previous Video: • #257-How to use INDEX ...
Next Video: • #259-How to use GETPIV...
Related Video Links: • #163-How to Create an ...
***********************************************************
~:Support Our Work Financially:~
Premium Download : sahacomputer.c...
***********************************************************
~:Join us on Social Media :~
🔔Subscribe to Our Channel: bit.ly/2TTblTG
Visit Website: www.sahacompute...
Join Us on Patreon: / scec
Facebook: / scecpkt
Twitter: / manojsaha2016
***********************************************************
~:Hire Me:~
If want me to help you to build Excel-based applications, you can Hire ME!
Contact me: [e-mail]
***********************************************************
~:Sponsored:~
***********************************************************
saha computer, duty roster, shift planning, shift roster, automatic duty roster excel, automatic shift schedule excel, duty roster in excel, duty roster kaise banate hain, employee roster, shifts planner, staff roster, roster, how to make duty roster in excel, duty roster format in excel, shift roster 24x7 excel, duty roster shift schedule, hr, staff duty roster in excel, duty roster excel, employee shift schedule, how to make schedule in excel, kitchen duty roster

[@pipraiyar]

Wow, what a wonderful tutorial for novices like me. I could make it easily....Thanks

[@SahaComputer]

You're very welcome!

[@KC_47.]

Your explanation is AWESOME..!! I can understand with my basic knowledge of Ms Excel.. You even give option for the formula writing.. 🙇🏻‍♂️

[@SahaComputer]

Glad to hear that

[@khawajarizwan7006]

THANKS VERY USEFULL

[@SahaComputer]

You are welcome

[@rupinderwalia4000]

your tutorial is wonderfully. i just have a question if we have different people with different rotations we have to input all that differently but how to manage it when the rotations for all the employees is 12 weeks circle and then rotation again repeat for 1-12 and then so on for the whole year. How to go about it

[@SahaComputer]

Give a example of rotation style.

[@rupinderwalia4000]

@@SahaComputer employee 1 is permanent full time starts from week 1 On tuesday working tues- friday week 1; Week 2 Works M-thursday week 3 (Monday-Sat); week 4 tues-fri; week 5 (m-fri)week 6 (m-saturday)l week 7 tuesday-thursday; week 8M-friday; week 9 m-sat; week 10 tues- thursday; week 11 M-friday; week 12-M-sat. After this week one starts again and repeats till week 12 and then again till the weeks. there will be 4 other employees which are full time but some start on wed, some on monday Employee 2 is a 0.79 part time starts week 1- tues-friday; Week 2- Mon-thurs; week 3- mon,tues, fri, sat; week 4- tues-friday; week 5 mon,tues, wed; week 6mon, tues, fri, sat; weeks 7 tues-friday; week 8 mon, tues, wed; week 9 mon, frid, sat; week 10 wed, thurs, frid; week 11 mon- thurs; week 12 monday-sat Employee 3 is 0.84 part time starts week 1- M-friday; week 2- mon, tues, frid, sat; week 3- tues, wed, thurs; week 4 mon, tues, friday sat; week 5mon, tues frid, sat; week 6 tues-friday; week 7 monday-thurs; week 8 mon, tues, frid, sat; week 9 tues- frid; week 10 mon-frid; week 11 mon, tues, frid, sat; week 12 tues-thur

[@murasame03]

Great video sir. Is it possible when copying to the following sheet, only copy the values. Not the whole formula? Thank you.

[@SahaComputer]

You cannot copy whole sheet without formula. But what you can do is first copy the sheet then select the whole sheet and then paste as value.

[@maimunsamah1547]

Sir, good explanation. I try to follow but I stuck at Data validation. What code i should press?

[@SahaComputer]

Sorry for the late reply. but I cannot understand your problem.

[@SahaComputer]

Hi! Viewers, If you face any problem let me know. If you want to purchase the project file click the link. Purchase & Download : rzp.io/l/CxZ2XIB

[@user-ny6nq9ek4g]

Sir I have a quick question only, for example in FEB 1 IS M1, FEB 2 IS M2, FEB 3 IS M3, FEB 4 IS M3, if feb 3 & feb 4 is same and FEB 5 is M4, why on feb 5 up to end of feb only M3 is pop up. may i know what is the problem. thank you

[@SahaComputer]

You have to make all settings in Settings sheet and no duplicate legend can be used in settings sheet.

[@xinnyt2919]

Nice template. Dear Sir, can you also please show one that has automated timesheet calculation incorporated into this shift duty roster?

[@SahaComputer]

Can you explain the requirement.

[@TheHasnatjamal]

Great Tutorial Sir, Is it possible to make a shift roaster for e.g. 3nos of employees 12hrs of shift (one person Morning Shift, one in night shift n one in General Shift), on Sunday no General Shift but only Morning & Night Shift. Thank You

[@SahaComputer]

Yes, you can do that in this template. Create two shift types, in 1st list type only G2 and in the second list Type M1, N1. Now try the shift in roster sheet.

[@sudeepghosh4516]

Sir need an excel formula for working continuous hours of work for more than 24 hrs For example Mr. X punched in morning at 08:00am on 01st April and punched out at 02nd April 02:00 pm. Need to calculate the time of work

[@sarahhosking4249]

is the template is still available to be able to add shifts as well as sick absences and different types of leave @SahaComputer

[@SahaComputer]

Yes you can add different types of leaves.

[@eraltolentino9669]

Sir instead of using D1,D2,D3,D4, M1,M2,M3,M4,is it possible to use D,D,D,D,OFF,M,M,M,M,OFF just like this as a code? Thank you

[@SahaComputer]

No, Not possible

[@eraltolentino9669]

@@SahaComputer , any formula or conditional formatting to allowed that code? thank you in advance.

[@sunil4lifeline]

sir,please share th codings , u hv used in this sheet

[@farhanyaqoob3242]

I am getting the same error while creating Dependent Dropdown list, i corrected the formula same you shown in the video but still can't make the dropdown list. Will you or any one can help on it ? =OFFSET(Settings!$I$1,1,MATCH(Master!$C8,Settings!$I$1:$M$1,0)-1,COUNTA(OFFSET(Settings!$I$1,1,MATCH(Master!$C8,Settings!$I$1:$M$1,0)-1,100,1)),1)

[@sriyasanagala2923]

FACING THE SAME ISSUE SPENT 4HRS PLS HELP

[@wedeshallo]

after inserting new module in vb, you have pasted a code. Where can i get that code?

[@gouravsharma2089]

Problem facing during dynamic depending drop down list Kindly help

[@SahaComputer]

How can I help you?

[@simuislam5752]

Good video

[@SahaComputer]

Glad you enjoyed

[@arunkumarkrishnan2917]

Great tutorial @Saha Computer Education Centre appreciate your efforts. I have a question, how can I include leaves like Annual Leaves and Maternity leaves in the roster? We allow our staff to submit their leave applications in advance, so it will show in the following months' roster(s) when the new roster is generated. This way we can see how many staffs on duty and on leave on daily basis. Please help thank you.

[@SahaComputer]

Not possible, in this roster. But I have created a new template where you can add leaves, holidays and weekends according to your need.

[@pinkieponkiepie]

@@SahaComputer Where is this :)

[@SahaComputer]

I will post the video.

[@prashantzade34]

@@SahaComputer How can I get this new one template where I can add leaves, holidays and WO randomly?

[@SahaComputer]

I have a template that includes leaves, holidays & WO, If you wish purchase, email me [e-mail]

[@enrouteguideentertainment2047]

Wonderful work sir, I have a problem,please solve it. 1day =3 shifts, 1shift=20 personal. How to create day duty roster with duty rotation please suggest me. Thank you

[@SahaComputer]

How many employee do you have?

[@rajboddu4264]

Hello Sir i have problem where 5 resources rotational shifts not working

[@SahaComputer]

what problem are you facing?

[@sultanmia3895]

Nice ❤️

[@SahaComputer]

Thanks 🤗

[@a.alizadeh3240]

Thanx

[@SahaComputer]

Welcome

[@vargheseabraham2154]

What is call day ? When are the week-off''s in first seven days ? Kindly reply ! Where can I download this ?

[@SahaComputer]

Download link given in description box.

[@SunilKumar-su7jw]

How to set rotational weekoff's ( OFF) for an employee. for example in this month employee1 have weekoff on sat and sun for nxt month it should be on mon and tue. How set this .Pls hlp

[@SahaComputer]

Thant cannot be done in this roster that has to be customized. You can contact me for custmization. Fees applicable.

[@SunilKumar-su7jw]

@@SahaComputer can you provide me your phone number or email id.

[@SahaComputer]

I have replied to your email.

[@wym9541]

How to mark the sick leave days or vacation days after this formulas sir. ?

[@SahaComputer]

I have a ready made template on that if you wish to purchase you can contact me [e-mail]

[@wym9541]

​@@SahaComputer yes sir I would like to

[@SahaComputer]

I created a template with leaves. If you are interested I can show you only.

[@chris6182007]

When an employee is on vacation and there must be an other employee in his shift? can it be calculated?

[@SahaComputer]

No it will not calculate in this template. But anyway I will try to create one so that it can be calculated. Can you suggest on which basis any other employee will given on that shift. Is that extra employee fro that week or who are willing to do extra shift or any other logic.

[@chris6182007]

@@SahaComputer there is a certain number of employees. So if someone is on vacation, someone else must replace him from the remaining employees. No extra employee.

[@chris6182007]

So far no answer. I purchased your program and i have some problems

[@SahaComputer]

sorry for the late, please tell your problem. Previously you mention about a problem that is not included in this template and I do not claim that that in the video.

[@SachinKumar-zo2ik]

I want make it with employee leave details. Automatically another employee sd take over in leave period ​@@SahaComputer

[@MrMuzzay]

How to Ctrl+Shift+Enter in the Name manager formula tab?

[@SahaComputer]

You cannot do that.

[@adrianahazou7061]

Is it possible to have each week and/or month start on a Saturday? Our weeks are Saturday to Friday at my place of work.

[@SahaComputer]

You can do weekly, fortnight and monthly which ever you want. just you have to do is: just create the rotation style in settings sheet, rest of the work will be done automatically. See the settings sheet demo. You can also see video No: 258, 267

[@Raj-cf6fe]

Will this work for a 6 weeks On an 6 Weeks Off schedule?

[@SahaComputer]

No it will not work.

[@Raj-cf6fe]

​@@SahaComputer Can you make an excel program upon payment?

[@SahaComputer]

Can you give some more details of requirement.

[@Raj-cf6fe]

@@SahaComputer Can i hav ur email to contact

[@SahaComputer]

@Raj-cf6fe [e-mail]

[@cwatiousszara]

How can I create master shift

[@SahaComputer]

Link given in description box, it is Member video.

[@kakolishelaighor4100]

Good vedio

[@SahaComputer]

Thanks

[@gideonenamben1871]

Hi

[@SahaComputer]

hi

[@sivans2260]

is there any formula for Sat/Sun weekoff for Employe-A and oncall for Employee-B

[@SahaComputer]

You have make changes shift list for this in the setting sheet.

[@sivans2260]

@@SahaComputer Can you please share the formula if possible

[@jyotidip0077]

Hi Sir, this is really helpful video. But I need your support make our roster in but different manner. The logic I am sharing. Below. Can u please make video based on the following requirement? 1. Total Staff 1200. 2. Female is 20% of the total staff. 3. Male staff having one week day and next week night shift 4. Female staff having only day shift. 5. Each day 14.28% should be weekly off. 6. Day & night shift MP availability balancing should 50% day and 50% night. 7. 26 working days and one total 4weekly off in a month. Thanks and best regards, Jyotidip

[@SahaComputer]

I will try to but it will take time.

[@sheldonlynch1659]

Hi, how can I get to download this excel sheet for direct use? I like this sheet and would like to download it.Please advices

[@SahaComputer]

Purchase link is given in the description box.

[@amirsuleman5596]

@@SahaComputer how much the price in us $

[@SahaComputer]

It will cost $20

[@amirsuleman5596]

Ok I will Purchase one copy but I need a little modification according to my duty .how I can contact you to give you the modification I want and give you more details about my establishment And send you a copy of my manual duty timetable

[@SahaComputer]

You can contact me through email: [e-mail]

[@amirsuleman5596]

Hello sir in minute 15/40 seconds I couldn’t do the step how can I get the source when I after select list press f3 it will not com help me please

[@SahaComputer]

You have to create name range for month list in settings sheet, then only you can get.

[@amirsuleman5596]

Hello sir thank you very much for your reply to resolve this problem by selecting the months list and then I success but I am facing same problem to make the drop list for shift start Data validation I select least in source I couldn’t get the list when I press F3 for that the drop list I couldn’t do it how I can resolve this problem thank you very much

[@SahaComputer]

There is only two dropdown list i.e Month & Year. I cannot understand for which cell you are talking about.

[@amirsuleman5596]

My My problem was shift start I can’t make drop-down list for this cell D8

[@SahaComputer]

Watch this video: kzbin.info/www/bejne/bHncpmuDrMyaatU

[@SuccesSynergy]

How can i add more shifts?

[@SahaComputer]

You have to add more columns in Settings sheet and make correction in the Named range of Schedule Type and Schedule Starts.

[@SuccesSynergy]

@@SahaComputer ok. Is there a way to edit this into a 10 hour shift schedule. My company operates 24/7/365.

[@SahaComputer]

Yes, It can be done but I need to know the shift rotation pattern of the employee. You can write me in details in [e-mail]

[@blackopsdr1]

Grettings Friend, Awesome video! I'm having an issue with your instruction at 49:17. I copied the formula, replace the =1 for =2, change the color formatting but when I click apply the new formatting doesn't translate to the form, please help!

[@SahaComputer]

There maybe issue with Named Range.

[@blackopsdr1]

@@SahaComputer how? am literally duplicating the formula and some of the color formatting works just not all of them

[@SahaComputer]

But I am sure that there is a mistake.

[@blackopsdr1]

@@SahaComputer I don't know what it was but I started the project from scratch and somehow it worked! thank you very much my friend, Greetings from the Caribbean!

[@karthickr7149]

what is call day?

[@SahaComputer]

It is just a name of a shift. If you don't require this then delete and create your own.

[@nitinkumar-lt7eq]

hello sir, i am having a problem in this code =IF(OR($C8="",$D8="",G$7=""),"",IF(F8="",E8,INDEX(setting_shifts,IF(MATCH(F8,setting_shifts,0)>COUNTA(setting_shifts),1,MATCH(F8,setting_shifts,0)+1))))

[@SahaComputer]

=IF(OR($C8="",$D8="",F$7=""),"",IF(E8="",D8,INDEX(Settings_Shift,IF(MATCH(E8,Settings_Shift,0)>=COUNTA(Settings_Shift),1,MATCH(E8,Settings_Shift,0)+1)))) This formula is according to my file.

[@nitinkumar-lt7eq]

@@SahaComputer i also did the same according to my file ,but it is not #### result

[@SahaComputer]

if you have done same as the video and do not change any column or row then it will work else there will reference issue.

[@kcf311]

is this going to work on macbookpro?

[@SahaComputer]

I have not tested on Macbook, but you can see the artcle below. It says that it will work. learn.microsoft.com/en-us/office/vba/api/overview/office-mac

[@fenderoslav]

Formula for selecting shift type (shift - A to E) is not working... =offset(Podesavanja!$R$1,1,match....here is marked $R$1,1,match like error

[@SahaComputer]

First you have to understand, how to create dependable dropdown list. Here is the playlist link you will find the video here. kzbin.info/aero/PLpsGVOobEOlDDJKjb2A6sjcGDzg5D6Lkb

[@sudeepghosh4516]

Pls help

[@tommynguyen02]

I sent you an email a few days ago. Can you check as well as your spam box? Thank you.

[@SahaComputer]

Let me check

[@fahmaq]

i have emailed you for an order. can you please check and reply

[@SahaComputer]

I have not received.

[@fahmaq]

@@SahaComputer i will send it to you again today.

[@SahaComputer]

Can we connect today.