Track employees location on schedule in Excel


I’m going to show you how to keep track
of employees location on your schedule quite often when I’m making schedules
for firms they have a number of employees in a number of locations they
must keep track of the employees hours and make sure that the locations
always have coverage so what I’ve done here I’ve created a schedule of four
days on and three days off with the schedule you must have all your
employees working and you must have all the location covers all the employees
the employees here there’s a drop-down list and I have I’ve named my employees
all the colors I could think of and basically how I did the drop-down list
is I just did data validation and did a list and what I’ve done here is I’ve
made a short form for the list so you can see here what I’ve done is left the
first three letters of the name here and that’s how I have got that so how I’ve
done the dates here is I have created the dates as custom schedules as custom
formatting for the for the schedule so I’ve just taking this date and I’ve
added one to it and I’ve gone where I clicked and I’ve gone a custom format on
the dates to for it to be to two decimal or two digits here and then to get the
days of the week what I’ve done the same thing as I have the same days and I have
asked it to be three digits which gives what’s give you the abbreviation for the day now
what I have done here is I have done the weekday function of the date which will
produce one two seven and seven being Saturday and one being Sunday and then I
have conditionally formatted the Saturday and Sunday to be gray thing
they kind of stand out here I will include the VBA code I’ve previously
discussed this on our other videos on how to create this three on three off
schedule that’ll automatically when you run schedule
and I will include their told in the description of my video and so let’s go
on to this so now we have besides besides our sheet and our scene with our
people in it then we have a location sheet and so I’ve put in locations 1 2
or 3 ok so let’s let’s select location 4 and
let’s put it in for a number of people here so let’s say let’s put location 4
here and maybe a little bit here now when you’re when you’re finished putting
locations and you don’t know whether you’ve got everything covered or not so
what you’re gonna do is go back to your location sheet and then what I’m gonna
do is I’m gonna click the transfer button and what its gonna do is it’s
going to put in the person or that’s the first three letters of the person’s name
who are in each of these locations now you can you’re able to see whether your
locations are covered for every day the other thing that you could be using this
for instead of locations is you could be putting any pieces of equipment and if
you’re trying to track whether this truck this look this equipment is used
enough to verify or to make it worthwhile you could just keep track of
here and then do statistics on how many times it’s being used in the month but
this is this is how you can track where the locations are well that they have
been used on so now let’s go talk about the code I will of course include the
code in the description of my video and so so I have I’ve made this transfer
routes and what I’ve declared here it is I have I have declared this range as
what I would called it as route number and then I have declared
find range as a range search column as an integer and search column is on the
shape one it’s the days of the month that we’re looking for okay so what the
code is doing is it is searching it’s searching every day of the schedule to
find out location for each location is located on the schedule and then it’s
returning the person’s name to who’s assigned to that location on that day
and it will keep going until there is no more days left on the schedule and it
will keep going if you notice my last row in here I don’t have and I have n so
it’s looking for an end here and when it runs comes to the word and that will
quit looking for anymore so that’s how you keep track of
locations for individuals on your team please subscribe

Leave a Reply

Your email address will not be published. Required fields are marked *