员工进入公司两周年_突出显示即将到来的员工服务周年

员工进入公司两周年

Here at Contextures, there aren’t very many employees to keep track of. There’s just me, and I always remember my hire date anniversary, and take myself out for a nice lunch. However, you might have more people on your employee list, and you can use Excel to highlight upcoming employee service anniversaries.

在Contextures，没有太多要跟踪的员工。 只有我一个人，而且我总是记得我的结婚纪念日周年纪念日，并带自己去享用美味的午餐。 但是，您的员工列表上可能会有更多人，并且可以使用Excel突出显示即将到来的员工服务周年纪念日。

员工服务周年 (Employee Service Anniversaries)

In the Excel Table shown below – filled with fake names and hire dates – there are 48 employees. That’s way too many people to manage, without some help!

在下面显示的Excel表中-包含假名和雇用日期-有48名员工。 那是太多人无法管理的方式，而没有任何帮助！

I’ll add a formula to calculate each person’s anniversary date, based on their hire date. Then, I’ll highlight the rows which have upcoming anniversaries.

我将添加一个公式，根据每个人的雇用日期来计算他们的周年纪念日。 然后，我将突出显示即将到来的周年纪念日的行。

输入当年 (Enter the Current Year)

To get the anniversary date for each person, we’ll create a date with the current year, and the month and day from the hire date. I’ve entered the year in a cell at the top of the sheet, and named that cell – CurrYear.

为了获得每个人的周年纪念日，我们将创建一个带有当前年份的日期，以及从雇用日期开始的月份和日期。 我在表格顶部的一个单元格中输入了年份，并将其命名为CurrYear。

You could use a formula to get the year from the current date, but I like to type it on the worksheet. That way, it can be controlled at year end, if you’re finishing up some reports from the previous year, or getting a head start on next year’s reports.

您可以使用公式从当前日期获取年份，但是我想在工作表上键入它。 这样，如果您要整理上一年的报告，或者在明年的报告中抢占先机，则可以在年底进行控制。

计算周年纪念日 (Calculate the Anniversary Date)

Next, I’ll add a formula in column F, to calculate the anniversary dates. I’ll use the DATE function, which has Year, Month and Day as its arguments. To get the year, click on the CurrYear cell, then type a comma separator.

接下来，我将在F列中添加一个公式，以计算周年纪念日。 我将使用DATE函数，该函数具有Year，Month和Day作为其参数。 要获取年份，请单击CurrYear单元格，然后键入逗号分隔符。

=DATE(CurrYear,

= DATE(CurrYear，

Next, use the MONTH function to extract the month from the Hire date. Because the data is in a formatted Excel Table, the field name, instead of a cell reference, is shown when I click on the Hire Date cell:

接下来，使用MONTH函数从“雇用”日期中提取月份。 因为数据在格式化的Excel表中，所以当我单击“雇用日期”单元格时，将显示字段名称而不是单元格引用：

=DATE(CurrYear,MONTH([@[Hire Date]]),

= DATE(CurrYear， MONTH([@ [Hire Date]])) ，

Finally, use the DAY function to extract the day from the Hire date, then add a closing bracket for the DATE function. Again, the field name, instead of a cell reference, is shown when I click on the Hire Date cell:

最后，使用DAY函数从雇用日期中提取日期，然后为DATE函数添加一个右括号。 再次，当我单击“雇用日期”单元格时，将显示字段名称，而不是单元格引用：

=DATE(CurrYear,MONTH([@[Hire Date]]),DAY([@[Hire Date]]))

= DATE(CurrYear，MONTH([@ [雇用日期]])， DAY([@ [雇用日期]]) )

Press the Enter key, and the formula should fill down to the last row in the table.

按Enter键，公式应填入表格的最后一行。

日期范围的命名范围 (Named Ranges for Date Range)

In the worksheet, we’ll use conditional formatting to highlight the upcoming anniversary dates. Instead of hard coding the number of days in the upcoming date range, we’ll add a named range – DaysOut – at the top of the sheet.

在工作表中，我们将使用条件格式突出显示即将到来的周年纪念日。 我们将在工作表顶部添加一个命名范围– DaysOut，而不是对即将到来的日期范围内的天数进行硬编码。

In the screen shot below, cell I3 has been named as DaysOut, and I entered 14 as the number of days.

在下面的屏幕快照中，单元格I3被命名为DaysOut，而我输入的天数为14。

Next, we’ll add named ranges – StartDate and EndDate – at the top of the sheet, to show which date range is highlighted.

接下来，我们将在工作表顶部添加命名范围– StartDate和EndDate，以显示突出显示的日期范围。

In the screen shot below, cell L1 – StartDate – contains a formula to calculate the current date: =TODAY()

在下面的屏幕快照中，单元格L1 – StartDate –包含用于计算当前日期的公式： = TODAY()

Cell L3 – EndDate – add the specified number of days to the current date: =StartDate + DaysOut

单元格L3 – EndDate –将指定的天数添加到当前日期： = StartDate + DaysOut

添加突出显示 (Add the Highlighting)

To add the conditional formatting,

要添加条件格式，

• Select all the cells with employee data, but not the headings -- cells B4:F51 in this example.
  选择所有具有员工数据的单元格，但不要选择标题-在此示例中为单元格B4：F51。
• Check the sheet to see which cell is active, because you’ll refer to that cell in the conditional formatting formula. In this example, cell B4 is active.
  检查工作表以了解哪个单元格处于活动状态，因为您将在条件格式公式中引用该单元格。 在此示例中，单元格B4是活动的。
• On the Excel Ribbon’s Home tab, click Conditional Formatting, then click New Rule.
  在Excel功能区的“主页”选项卡上，单击“条件格式”，然后单击“新建规则”。

• In the New Rule window, at the top, click ‘Use a formula to determine which cells to format’
  在顶部的“新建规则”窗口中，单击“使用公式来确定要格式化的单元格”

• Click in the Formula box, where you’ll add a rule that checks the anniversary date, to see if it is between the StartDate and EndDate. We’ll use the AND function to check both dates, and use a mixed reference to the anniversary date in the active row, locking the column, but not the row:

  单击“公式”框，在其中添加检查周年纪念日的规则，以查看其是否在StartDate和EndDate之间。 我们将使用AND函数检查两个日期，并在活动行中使用对周年纪念日的混合引用，从而锁定该列，但不锁定该行：

  =AND($F4>=StartDate,$F4<=EndDate)

  = AND($ F4> =开始日期，$ F4 <=结束日期)

• Click the Format button, and select a colour to highlight the upcoming anniversaries – I selected bright green.
  单击格式按钮，然后选择一种颜色以突出显示即将到来的周年纪念日–我选择了鲜绿色。
• Click OK, to apply the formatting
  单击确定，以应用格式

The rows with anniversary dates that fall between the StartDate and EndDate are highlighted in green.

周年纪念日日期介于StartDate和EndDate之间的行以绿色突出显示。

You can change the number of days, to change what is highlighted. For example, increase it to 30 days, and more rows might be highlighted.

您可以更改天数，以更改突出显示的内容。 例如，将其增加到30天，然后可能会突出显示更多行。

下载样本文件 (Download the Sample File)

To experiment with the conditional formatting, you can download the sample file from this tutorial. Go to the Sample files pages my Contextures website, and look for CF005 – Highlight Employee Hire Date Anniversaries. The zipped file is in xlsx format, and does not contain any macros.

要尝试使用条件格式，可以从本教程下载示例文件。 转到我的Contextures网站的“样本文件”页面，然后查找CF005 –突出显示“员工雇用日期周年纪念日” 。 压缩文件为xlsx格式，不包含任何宏。

翻译自: https://contexturesblog.com/archives/2014/01/09/highlight-upcoming-employee-service-anniversaries/

员工进入公司两周年