As companies utilise hybrid work, it is key that staff schedules are maintained effectively. There are a plethora of ways this can be done from simple excel files to complex schedule trackers. In this project, though the use of Excel, a staff tracker has been created.
Introduction
There are occasions when the use of technology cannot be maximised. This could be for an array of reasons such as issues with compatibility, a preference to maintain status quo or due to variations in digital literacy levels. In such cases, the application of technology must be measured.
Whilst this can be frustrating, it can also pose an unique learning process for those with an acute interest in technology. It forces one to ensure that the technology is used in such a way that the end user does not experience major changes to their task whilst ensuring other aspects of the task is improved. For example, the update of key data in a file used by a team can be automated whilst the interface and key aspects remain as is.
It should be noted that, as with anything, there is a cost to introducing technology, especially at the start. A simple file can take minutes to construct whilst a more advanced file can take longer to do so.
However, if one was given free given free rein, how would various tasks be complied?
With hopes of making this a series, various tasks will be explored and whether they could be challenged in a more effective manner. The hope of such projects is to highlight my capabilities whilst also pushing me further to expand my knowledge of various tasks.
Overview
As companies utilise flexible working, managers have faced new challenges. One of these challenges include ensuring they maintain oversight of where employees would be working and who would be in the office.
In one example, I have witness a rough excel file containing information on the team's work from home schedule was circulated on a bi-monthly basis.
However, could there be a more effective way to construct this file?
Understanding the task
A rough excel file containing the schedule may look like this:
| Mon | Tue | Wed | Thu | Fri |
Jack | | | | | |
Harry | | | | | |
Tom | | | | | |
It contains a list of names, the days of the week and space to confirm if an employee is working in the office or not. However, there are limitations to this file.
Limitations
Team leader will need to ensure they are aware of public holidays
Team leader will ensure they are aware of how many individuals are in the office and maintain the numbers effectively.
In addition to these, the file should be able to effectively maintain staff changes.
Creating the schedule
There are different ways the schedule can be crafted. For this purpose, let's use a table with the weekdays running vertically and the names of those in the team running horizontally. To ensure there is a way to keep a track of the number of individuals, let us also include two columns counting the number of staff working at home and at the office.
It should be noted that staff can come and go. As such to keep the process of updating the file simple, it may be wise to add the count columns before the names.
However, given that we are unsure how long the file will be in use and having all possible working days listed would make the file difficult to read, a function filtering the dates may be wise. Therefore the file shows only a month of working days.
This creates a list such as this:
The dates
The user updates the year and month as they progress through the year. To ensure that the user does not include dates which cannot be utilised, a data validation rule is applied upon these cells.
The year has been set between 2023 to 2030.
2030 is a significant overestimation and it is recommended that the file is not used such a duration without regular maintained and updates. Also, if possible, alternative software better aimed at keeping track of schedules should be considered.
The dates are calculated by the formular:
=WORKDAY(A6,0)The day of the week alongside the date is created via the following:
=TEXT(B9,"aaa")The greyed out date is an anchor by which all the corresponding weekdays will be calculated - it will be formulated white so as to ensure that the user is not distracted by the date. It is based upon the following formular:
=DATE(A5,B5,1)Through the above functions, as the user updates either the month or year, the dates in the table will update automatically.
The Team
The name of the team members can be added directly to this worksheet. However, considering possible alternative use of the same data such as storing the dates of staff absences, the names are stored in an alternative tab. This data is then drawn back into the file though the below function:
=TRANSPOSE(Team!B3:B6)Whilst this formula is relatively simple, the range covered by the function will require updating if the size of the team changes.
Working schedule
Staff will typically have different status, working from home, office and different types of leave. This data is unlikely to deviate and as such data validation is used to ensure that the data is registered correctly. The options added to the list are stored in a different tab and adjustments to the validation and this list will be required if further options emerge or options are removed.
Counting staff
Now the data is ready, a function to calculate the number of staff in the office can be added. This is a relatively simple function written as follows:
=COUNTIF(F8:I8,"Office")A conditional formatting is added on to this so as to highlight low staff numbers instantly.
Importing public holidays
There are a plethora of ways to import public holidays and will differ between jurisdictions. Alas, for the United Kingdom, an easy to use file could not be located. As there is a handful of dates, these can be manually added to the file.
However, if one did wish to use Excel functions, one method could be to use the data provided by General Blue Corporation. It has a list of dates but the dates are not recognisable via Excel. Therefore the data needs to be processed, prior to use. There are different ways to process the data including deleting the unwanted information.
That said, one should note how others will maintain the file. If columns are deleted, updates will also require the need for the deletion of such columns. In addition, the developer may not be the individual tasked with such updates. As such, the unnecessary data has been left as is.
It should be noted that generally speaking public holidays do not change. However there are occasions when a public holiday may be announced. In such cases, the date must be added to this list.
Excel is unable to process the date as is and therefore the following formula has been used to convert the dates into a more readable format for Excel. The format of the date may differ and if so, the format of the dates can be revised.
=DATEVALUE(MID(B4, FIND(" ", B4) + 1, LEN(B4) - FIND(" ", B4) - LEN(", ") - 4) & " " & LEFT(B4, FIND(" ", B4) - 1) & ", " & RIGHT(B4, 4))Using this data, a conditional formatting can be added to grey out the days where staff will not be working.
Clear data
Finally a simple macro is added to enable the quick reset of the file. It serves to save time but also ensures that the file does not become corrupt though the use of incorrect processes to clear the file for a new month.
Additional Features
Filters
Whilst one would like to believe that each cell would be updated in turn, typically there may be an established schedule. As such to ensure that the file is able to accommodate easy updates, the filters have been enabled.
Column width
To improve the view of the file, a macro can be added to automatically adjust the width of the columns.
This can be achieved through the following code:
Colour Palette
Many companies now utilise style guides. If a company does so it is wise to use these colours to maintain a degree of professionality. In this example, the following colour palette has been used to recreate this.
Conclusion
This was a project exploring how a staff schedule could be created in Excel with additional features. Attempts have been made to ensure that the file is easy to use for those who are not IT literate whilst ensuring data is clear and consistent and easy to update.
To view this file, visit https://github.com/Lostinworlds/Excel/blob/main/Automated%20email.xlsm
Please be aware all names used in this file are fictional.
Comments