Tip for creating contract expiration notifications in Excel
Tip for creating contract expiration notifications in Excel: Managing the expiration dates of employment contracts is a complex task for large organizations. In this article, Buffcom.net shares a tip for creating contract expiration notifications in Excel to help human resources personnel easily track the status of employee contracts.
For example, let’s say you have a list of employees with their contract expiration dates as shown in the image below. To create expiration notifications, follow these steps:
Step 1
First, select the entire Contract Expiration Date column. Then, go to the Home tab on the toolbar. Next, select Conditional Formatting under the Styles section. A scroll bar will appear, select New Rule.
Tip for creating contract expiration notifications in Excel
Step 2
At this point, the New Formatting Rule window will appear. Under Select a Rule Type, choose Use a formula to determine which cells to format. Then, under Format values where this formula is true, enter the following formula:
=DATEDIF(TODAY(),D2,”D”)<30
With this formula, the notification time will be 30 days before the contract expiration.
Where:
- DATEDIF function counts the number of days between two dates.
- D2 is the cell that contains the Contract Expiration Date value on the worksheet.
- 30 is the desired notification time (can be changed as needed).
Tip for creating contract expiration notifications in Excel
Step 3
After entering the formula, select the Format option to choose the notification color.
Tip for creating contract expiration notifications in Excel
At this point, the Format Cells window will appear. Select the Fill tab and then choose the notification color under the section. Finally, press OK to complete the process.
Tip for creating contract expiration notifications in Excel
The result will highlight the three employees whose contracts will expire within the next 30 days, as shown in the image below.
Tip for creating contract expiration notifications in Excel
Thus, this article has instructed you on how to create contract expiration notifications in Excel. We hope this article is useful to you in your work. Wishing you success!
