Calendar in Excel with Data Saving

Reading time: 3 minutes
Last Updated on 13/05/2023 by Frédéric LE GUEN

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.

Demo Calendar Excel

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

Unblock file with macros

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 Calendarautomatically 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:

  1. Double-click an employee's name
  2. Automatically all cells corresponding to the working days of the current month will fill with the value specified in the Settings sheet.
Employee Double Click

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.

Remove Employee Name

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.

Leave a Reply

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

Calendar in Excel with Data Saving

Reading time: 3 minutes
Last Updated on 13/05/2023 by Frédéric LE GUEN

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.

Demo Calendar Excel

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

Unblock file with macros

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 Calendarautomatically 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:

  1. Double-click an employee's name
  2. Automatically all cells corresponding to the working days of the current month will fill with the value specified in the Settings sheet.
Employee Double Click

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.

Remove Employee Name

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.

Leave a Reply

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