Many companies use Excel to record their employee's activities. For sure, it's easy and the grid simplifies the design and the filling of the data.
But while filling data is extremely simple, saving data from one month to the next one, is not so easy to do. To help you for saving your calendar information in Excel, we have created the following workbook.
Contents of the workbook
The workbook consists of 3 worksheets
- The calendar
- Parameters
- Data history
Each new addition or modification to the calendar generates a record in the Historical Data sheet
All the automation is generated by VBA (don't change the code to avoid failure).
Download the workbook
To download the workbook containing the calendar, click on this link.
Unblock workbook macros
Microsoft recently blocked all files from the Internet containing VBA for workbooks.
To make the file usable, proceed as follows
How does the calendar work?
Each time a cell is updated, the information is immediately saved in the Historical Data sheet.
The Historical Data sheet is fundamental in the use of the workbook and for the program in VBA. If you modify the structure (add or remove columns), the program will not work at all.
Which data can you fill in the cells?
There is no control over the nature of the data entered. You may as well enter
- Whole numbers
- Decimal numbers
- Hourly numbers
- Or letters.
Add a new employee.
As soon as you enter a new employee in column A of the Calendar, automatically the formula that sums up the working days is added in column B.
Autofill data for an employee.
If you need to enter the same value for each employee, you can use the following procedure:
- Double-click an employee's name
- Automatically all cells corresponding to the working days of the current month will fill with the value specified in the Settings sheet.
If data is already present on the row, an alert message will ask if you want to overwrite the existing data.
Delete values
As soon as you delete the values from one or more cells, the data is also deleted from the Historical Data sheet.
Delete all employee data for the selected month
You can delete all of an employee's data by simply removing their name from column A.
Change an employee's name
If you want to correct an employee's name on the Calendar sheet, automatically the employee's name will also be corrected on the Historical Data sheet.
Can I change the colors, the font, and the width of the columns?
You can change any formatting of the calendar with no impact on the execution of the program.
- Color of the employee column, Date's row
- Font of any cell
- Width of any column of the calendar
You can also change the color of weekends and holidays by changing the colors of conditional formatting.
BUT, if you apply a specific color for a day, this information will not be saved. Only the value of the day is saved.
What is forbidden to do?
- Change VBA Program
- Rename worksheets
- Adding rows or columns that change the structure of the Calendar (column of the employees, row for the calendar days)
- Change calendar layout (swapping rows and columns)
- Edit the Historical Data sheet
NOTE: If you take the initiative to change the VBA program or the workbook structure, the risk of the workbook not working is very high. In no way, we can not be held responsible for malfunctions found following a change in the code or structure of the binder.
