某公司的职员编号中包含部门名称缩写、进厂日期和序号，编号效果如图7.128所示。

图7.128 职工信息表

其中，部门编号与部门名称的对应关系见表7.4。

现要求利用编号中的入厂日期计算每个职工的年假天数。年假天数的计算方法见表7.5。

解题步骤

本例的问题相当复杂，因此可以分多个步骤来书写公式，将一个公式拆分成多个公式书写更容易理解公式的计算过程，同时也不易出错。具体步骤如下。

1.在E1：H1区域分别输入“进厂日期”、“完善日期”、“进厂月数”、“年假天数”。

2.在E2单元格输入以下公式，此公式用于提取两个“-”之间的字符，提取结果见图7.129。

图7.129 提取进厂日期

职员编号中的日期没有完整的年份，因此需要通过公式来完善日期。

3.在F2单元格输入以下公式：

有了完整的入职日期后就可以计算职工的入职时间了，单位为月。

4.在G2单元格输入以下公式：

有了入职时间，接下来就可以通过IF函数计算职工可享用年假的天数。

5.在H2单元格输入以下公式：

在以上4个公式的协同工作下，职工的年假天数会自动产生，效果如图7.130所示。

图7.130 分四步计算出年假天数

6.选择E2：H2，然后按下填充柄向下拖到第12行，从而让公式计算所有职工的年假天数。

假设只用一个公式计算年假天数，那么公式会相当长，不管是新手还是老手都不建议直接一步完成。如果要求只能在单列中完成，那么可以定义名称，通过名称简化公式，操作步骤如下。

1.删除E：H区域，然后在E1中输入“年假天数”。

2.选中E2单元格，按组合键＜Ctrl+F3＞打开“名称管理器”。

3.单击“新建”按钮，弹出“新建名称”对话框，然后将名称设置为“时间”，将引用位置设置为以下公式：

4.单击“确定”按钮保存设置，然后返回工作表界面。

5.在E2单元格输入以下公式：

6.双击E2单元格的填充柄，将公式向下填充到E12，最终效果如图7.131所示。

图7.131 单个公式计算年假天数

知识扩展

1.公式“=TRIM（MID（SUBSTITUTE（A2，＂-＂，REPT（＂ ＂，99）），99，99））”的含义是将职员编号中的“-”替换成99个空格，然后用MID函数从替换后的字符串中的第99位开始提取99个字符，从而得到空格与日期组成的字符串，最后用Trim函数去除空格，只保留日期。

公式中的REPT函数用于将指定的字符重复显示若干次，第一参数是要重复显示的字符，第二参数用于控制重复次数。例如“=REPT（＂AB＂，3）”的计算结果为“ABABAB”。

SUBSTITUTE函数用于将字符串中的部分字符替换成新字符，在本例中的作用是将“-”替换成99个空格。

由于替换后的日期前后各有99个空格，因此使用MID函数从替换后的字符串中第99位开始提取99个字符只能提取到空格和日期，不包含前面的部门编号和后面的序号，此时去除所有空格就只剩下日期了。

2.职工编号中的日期包含两种：一种是2000年以后的，第一个数字是0或1；另一种是2000年以前的，第一个数字是9，基于此规律，使用IF函数判断第一个数字是否等于9即可，如果等于9则在日期前添加19，否则添加20。

此处要注意的一点是LEFT函数提取出来的数字是文本格式的，因此和数字9比较时需要将数值9也转换成文本，在9的前后添加半角引号

3.DATEDIF函数计算两个日期的时间差时，两个日期都必须是日期格式的，“20150314”这种格式的数值不是日期格式，因此需要使用TEXT函数转换成“0000-00-00”这种格式后再参与计算。

4.当IF函数要求两个条件同时成立才算满足条件时，需要使用AND函数限制这两个条件。