I thought I could figure a better way to manage our work schedule but it’s turning out more and more complicated for my limited excel skills.
Attached you’ll find our current All Clinic schedule.
I have to be able to coordinate staff training, leave, sick days, appointments etc while still providing a percentage of staff available. For starters, I can’t figure out how to create a ratio.
I need to flag the days where there’s 1 provider to 1 tech (1:1) this would help me coordinate their time to a more convenient week.
Right now I’m calculating the ratios myself. I can’t calculate 1:1.5 techs because it must be a whole number, is not like a provider can have 1 full technician and half of one. On a good day we should have 1 provider to 2 technicians, for the exception of women’s health and internal medicine.
The schedule also needs to show very easily a week outlook. For example if I’m a technician and I want to know which doctor I’m working for in week 3 it should tell me along with my teammates (rn and additional techs) but for now, I’m doing that manually every week and modifying along the week.
In the schedule you see the available techs and number of staff in clinic, that is for me to know how much staff I have at all times (I would know what to expect for a meeting, emergency, etc} Right now the schedule is complicated to browse or to even find an specific tech.
I would like to be able to search for a staff member or a group of people, and see all their requested leave for the year, or how many times they have been on sick leave, or out for training. Let’s say I want to put a team of doc 1, tech 1 & 4 and nurse 1 together in a team for December, can excel just put together those lines kind of a schedule maker or outlook for the month?
I also don’t know how to load the schedule for months. I keep copying the current month and renaming it, I can load all the months but then I encounter issues with deleting a coworker and having to delete that person from all the months, but then I forget that I loaded the new person on 3 months from now, so it gets stupid trying to keep up. Could excel make my life easier?
I seen what you have done with dashboards and it looks amazing, I have committed myself to learning it but I can’t wait until I get good at it, I don’t even know if I could even make all this (schedule) come true. I hope that you could help me, any ideas, examples, anything… it would be truly appreciated. Thank you excel gurus, thank you.
Attached you’ll find our current All Clinic schedule.
I have to be able to coordinate staff training, leave, sick days, appointments etc while still providing a percentage of staff available. For starters, I can’t figure out how to create a ratio.
I need to flag the days where there’s 1 provider to 1 tech (1:1) this would help me coordinate their time to a more convenient week.
Right now I’m calculating the ratios myself. I can’t calculate 1:1.5 techs because it must be a whole number, is not like a provider can have 1 full technician and half of one. On a good day we should have 1 provider to 2 technicians, for the exception of women’s health and internal medicine.
The schedule also needs to show very easily a week outlook. For example if I’m a technician and I want to know which doctor I’m working for in week 3 it should tell me along with my teammates (rn and additional techs) but for now, I’m doing that manually every week and modifying along the week.
In the schedule you see the available techs and number of staff in clinic, that is for me to know how much staff I have at all times (I would know what to expect for a meeting, emergency, etc} Right now the schedule is complicated to browse or to even find an specific tech.
I would like to be able to search for a staff member or a group of people, and see all their requested leave for the year, or how many times they have been on sick leave, or out for training. Let’s say I want to put a team of doc 1, tech 1 & 4 and nurse 1 together in a team for December, can excel just put together those lines kind of a schedule maker or outlook for the month?
I also don’t know how to load the schedule for months. I keep copying the current month and renaming it, I can load all the months but then I encounter issues with deleting a coworker and having to delete that person from all the months, but then I forget that I loaded the new person on 3 months from now, so it gets stupid trying to keep up. Could excel make my life easier?
I seen what you have done with dashboards and it looks amazing, I have committed myself to learning it but I can’t wait until I get good at it, I don’t even know if I could even make all this (schedule) come true. I hope that you could help me, any ideas, examples, anything… it would be truly appreciated. Thank you excel gurus, thank you.
Last edited by a moderator:
