Automatic Daily Roster of Medical Team
Hello, Am new to excel. i am looking for a way to automate the shift rotation for the 14 Registrars in the attached file on a monthly basis.
I don't know if there is an excel Macro to automatically generate 2 people per day (A&E , Ward).
For weekends, same people on Saturday also covers Sunday.
Need the Shift shared equally.
Thank you
Hello nifecafe and Welcome to Excel Forum.
This proposal employs four helper columns which may be moved and/or hidden for aesthetic purposes.
AH7:AH20 are populated with the numbers 1:14
Column AK is populated with dates.
Column AL displays the A&E assignments and is populated using: =IF(WEEKDAY(AK7,1)=1,AL6,IF(MOD(SUM(AL6,3),14)=0,14,MOD(SUM(AL6,3),14)))
Column AM diplays the ward assignments and is populated using: =IF(WEEKDAY(AK7,1)=1,AM6,IF(MOD(SUM(AL7,1),14)=0,14,MOD(SUM(AL7,1),14)))
The formula that populates the Monthly Shift Rotation schedule is: =IFERROR(INDEX($AL$6:$AM$6,AGGREGATE(15,6,(COLUMN($AL$6:$AM$6)-COLUMN($AK$6))/($AL$7:$AM$68=$AH7)/($AK$7:$AK$68=C$5),1)),"")
Note that row 5 is populated with dates instead of numbers.
Note that AI7:AI20 are not needed but was left to show that overall the assignments will be shared equally as R1 and R14 will have the weekend shift May 2nd and 3rd.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks a lot JeteMc. Really appreciate your detailed feedback.
What am trying to further achieve is to auto-populate the shift rotation table based on the available number of people per time.
Take for instance, instead of the 14 people, we now have 9 people. And we want to equally share the work equally per weekdays and per weekends.
Take a look at this and see if it will work.
The modified formula for AL7 and down is: =IF(WEEKDAY(AK7,1)=1,AL6,IF(MOD(SUM(AL6,IF(ISEVEN(AL$4),3,2)),AL$4)=0,AL$4,MOD(SUM(AL6,IF(ISEVEN(AL$4),3,2)),AL$4)))
The modified formula for AM7 and down is: =IF(WEEKDAY(AK7,1)=1,AM6,IF(MOD(SUM(AL7,1),AL$4)=0,AL$4,MOD(SUM(AL7,1),AL$4)))
Change the value in cell AL4 to test.
Let us know if you have any questions.
Last edited by JeteMc; 04-08-2020 at 09:33 AM.
Wow! Brilliant. Thats the way i want it.
But i noticed that the Staff 2 and 5 were not populated when inputted value 6 in cell AL4.
Please help check.
Thanks
In addition, help automate below functions as well:
1. the SNR Registrars roster is shared equally among a given number n of available people. i.e the weekday is equally shared and the Weekends equally shared as well. Available people can be reduced or increased.
2. the Consultants cells are populated based on the departments on Cells C4 to AG4. See attached breakdown.
Thank you so much
By "help automate", what do you mean? You seem be presenting this as a requirement. Presumably thee worksheet shows your attempt at doing it yourself?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi AliGW,
By automate mean, should be populated once the given details are provided and condition met.
For instance, when the number of SNR Registrars is given as 3, the cells will re-adjust automatically by sharing the work schedule by Weekdays and Weekends.
While Consultants cells will be populated using the given dept that will be manually provided.
Thank you
I was referring to the word "help" - it looks as if you want someone to do it for you. The idea is that we provide assistance with your attempts at implementing solutions to problems, not build entire solutions for you. We have the commercial services section (paid) if you wish to hire someone to create solution. If you wish to continue in thee free section, then you need to met your helpers halfway by showing that you have tried to adapt what you have already learnt from the thread. Have you done this?
Yes i get your point.
If you wish to continue in thee free section, then you need to met your helpers halfway by showing that you have tried to adapt what you have already learnt from the thread. Have you done this? Yes i have. Will share same to meet my helper halfway.
Thank you AliGW
In response to post #5:
1. Please paste the following into cell AL7 and double click the fill handle to copy down:Formula:
Please Login or Register to view this content.
2. Please paste the following into cell AM7 and double click the fill handle to copy down: =IFERROR(1/(1/MOD(SUM(AL7,1),AL$4)),AL$4)
Let us know if you have any questions.
Thanks JeteMC.
Works very well now.
You are the best!
Thanks once again.
Well appreciated.
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks