日常工作中，我们总会用到excel表格，在使用的过程中，又总是碰到各种excel函数，这是最令人头痛的一块，数百个函数，因无法全部掌握，导致使用的过程中存在各种各样的问题，下面我为大家挑选出在企业工作中，最常用也最实用的十个excel函数，也就是十大明星函数，逐步精讲。

那么这十个明星函数都有哪些呢

1、VLOOUP函数

2、IF函数

3、SUMIF函数

4、COUNTIF函数

5、文本与日期拆分组合函数

6、SUBTOTAL函数

7、MATCH函数

8、INDEX函数

9、INDIRECT函数

10、OFFSET函数

今天精讲文本与日期拆分与组合函数用法：文本拆分与组合函数（LEFT、MID、RIGHT、CONCATENATE、PHONETIC)，日期拆分与组合函数（YEAR、MONTH、DAY、DATE)，另附加EDATE函数用法

1、文本拆分函数

LEFT函数：从单元格中文本左侧开始提取字几符数

MID函数：从单元格文本中间第几个字符开始提取几个字符

Right函数：从单元格中文本右侧开始提取字符个数

写法：=LEFT(A2,1),从A2单元格的文本右侧提取一个字符=MID(A2,2,1),从A2单元格文本第二个字符开始，提取一个字符

=RIGHT(A2,1),从A2单元格的文本右侧提取一个字符

2、文本组合函数

1）CONCATENATE函数

写法：CONCATENATE（文本1，文本2，文本3······）

多个文本中间用逗号隔开（逗号需在英文状态下输入）

2）PHONETIC函数

写法：PHONETIC(reference)

将某个单元格区域连接起来

连接顺序：先左后右，先行后列，先上后下

注：phonetic函数不支持数字、日期、时间、逻辑值、错误值、任何公式生成的值

3、日期拆分函数

YEAR(日期)，提取日期中的年份

MONTH(日期)，提取日期中的月份

DAY(日期)，提取日期中的具体某一天

如上图，分别在F2,G2,H2中输入 =YEAR(E2)，=MONTH(E2)，=DAY(E2)，然后下拉填充

4、日期组合函数

DATE函数，DATE(年，月，日），中间逗号隔开

5、附加：EDATE函数，在原有日期，加几个月

EDATE(日期，月数）

我们在用Excel给公司员工记录详细信息的时候，往往需要输出身份证号码、性别和出生年月等消息，一个一个的输入很是麻烦，有什么快速输入的方法呢？今天我们就一起学习一下吧。

提取性别：

首先我们看下面的表格是员工的姓名和身份证号码。

接下来我们需要输入性别和年龄，怎么快速输入性别呢？我们在性别列输入=IF(MOD(MID(K3,17,1),2),"男","女")

就可以了，回车后性别就自动提取出来了。

提取出第一个性别后，我们可以按住第一个单元格的右下角。拖住不放拉到最后，就快速的把所有人的性别信息提取出来了。非常方便。

需要注意的是由于我的身份证号码位置在K3，所以在上面使用了K3，大家可以找到自己对应的列来修改，不要直接复制，可能会产生错误。后面的17是指找到身份证号的17位用来确定男女，1是男，2是女，这个是我们国家用来区分男女的规则。

提取出生年月：

和刚才提取性别一样，我们在出生年月列输入公式 =MID(k3,7,4)&"年"&MID(k3,11,2)&"月"&MID(k3,13,2)&"日" 输入后，出生年月就自动提取出来了。

这里的K3依旧是我身份证号码所在位置，大家把K3修改成你表格中身份证号码所在位置就可以，其余的都不用修改，这个公式的意思是：MID函数作用为从指定的字符串中截取出指定数量字符，我们的公式中的“MID(K3,7,4)”,第一个参数表示K3单元格中的身份证号的所在列，7表示从第7位开始计算，4表示从第7位开始共截取几位数字.年份是4位，所以我们写4。我们可以刚刚才一样的操作，按住单元格右下角拉到最后，所有人的出生年月就提取出来了。

如果你需要 2012-08-08 这样格式的出生年月，可以用公式 =TEXT(MID(K3,7,8),"0000-00-00")

提取年龄：

出生年月和性别提取结束以后，就剩下提取年龄了。和上两个相同，我们需要在年龄所在列输入公式

=YEAR(NOW())-IF(LEN(K3)=18,MID(K3,7,4),19&MID(K3,7,2))

这个公式里，IF(LEN(K3)=18的意思是，如果这个号码是18位，当然，现在15位的身份证可能已经没有了，保险起见，大家可以加上这个用来以防万一。其余的就不用讲解了，基本上和刚才的差不多。

注意事项：

需要提醒大家注意的是，上面的所有公式中K3的出现是因为我的身份证号码所在的位置是表格的K3。大家在复制公式的时候，只需要修改K3就可以了，其余的可以不变。

其实，公式在Excel中非常重要，利用身份证号也可以提取很多东西，例如生肖、星座等等，由于使用的并不是很多，所以就不在这里写了。希望水煮鱼科技的这个文章可以帮助到你。

Hi，大家好，不知道大家有没有这样的情况呢，在做人事档案时，面对着一串长长的身份证号码，却一时算不出员工的年龄及生肖，这时不得不打开计算机来计算出员工年龄，再上网搜一下员工的生肖。这样不仅费时费力，而且事倍功半，今天就跟大分享一个Excel根据身份证号码自动提取出生日期、年龄及生肖的方法（如下图）：

第一步：根据身份证号码提取出生日期，在B2栏输入工式：=TEXT(MID(A2,7,8),"0-00-00")，回车，出生日期就被自动提取出来了（如下图）。

第二步：根据身份证号码自动计算出员工年龄，在C2栏输入工式：=DATEDIF(TEXT(MID(A2,7,8),"#-00-00"),TODAY(),"Y")，回车，员工的年龄就被自动计算出来了（如下图）。

第三步：根据提取出来的出生日期计算员工生肖，在D2栏输入工式：=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(B2)-4,12)+1,1)，回车，员工的生肖就被自动计算出来了（如下图）。

最后一步，选中BCD列的第二行，按住鼠标左键不放向下拖动进行填充。

你学会了吗。

首先来看下此图表，这个是咱们办公人员经常遇到的图表，可是如何根据身份证号来提取性别和出生年月呢？下面我就给大家讲解下。

先来说下性别的提取，因为我们知道身份证号的第17位数字是判断男女性别的，所以根据这个特性，我们就抽取第17位数字，看能不能被2整除，如果可以整除则代表男性，否则女性。（为什么要被2整除，因为第17位数字只能是1或2，1代表男性，所以意味着可以被2整除）

所以我们可以在F列写下以下公式：=IF(MOD(MID(E3,17,1),2),"男","女")

看到这个公式大家也许就不明白到底是什么意思了？因为这里面涉及到了3个函数，那么看下图片就能给你解释清楚了！这个公式可以简单理解是=IF（MOD（A3：B3），男，女）首先来说明下MOD函数，是指两数相除取余数，公式是MOD(A3：B3)，所以在上面的公式中，我们可以把A3代替为MID（E3,17,1）而B3就是2，代表的是这两个数相除，取余数。后面的男女则是如果是1显示结果为男，否则为女。

那么MID函数和IF函数又是怎么回事呢？

MID函数是提取所需要字符的函数，在咱们的例子里代表的是提取E3列，第17为数字，后后面的1代表的是只提取1为，如果是2则代表的是提起第17位和18位，以此类推。

比如MID（A1,2,4）其中A1里的值是123456，而这个函数代表的是提取123456里面的第2位开始取4个字符，显示结果就是2345。所以弄懂这个函数，对于提取出生年月是不是就易如反掌了。

而IF函数则是判断真假的函数，没有它则后面的全是白说，因为这个函数除以2之后会有取余数的判断，是1还是非1都是这个IF函数来判断的。

今天先讲这么多，下次给大家讲IF函数和嵌套。关注我让你学到更多，有知识的小火炬，传递更多知识给你们。

今天下午在写这篇身份号码提取出生日期的文字教程的时候，总感觉有种莫名的东西触动着内心，随着Excel的版本越来越高，里面所用到了功能也越来越智能话了。

　　每一个人都有身份证号码，我们今天要学习提取出生一起那么我们就必须得弄清楚身份证号码的构成。我们用ABCDEFHJ这几个英文字母来举例：AA BB CC DDDD EE FF HH I J，在这当中AA两位数字代表所在的省份，字母BB表示市，字母CC表示县，DDDD就是我们的出生年份，EE出生月份，FF即是出生日，HH表示个人代码，I 表示性别（男单女双），J表示系统校验码。

　二代身份证号码都是18位，7-14位就表示我们的出生日期。

　　小编负责人事管理，此前在汇总员工信息表时，提取出生日期的时候更多的是使用Excel函数公式来完成的。

　　在上小编以前所出的教程，要实现excel提取出生日期，至少有三种方法可以完成。

今天在接触一种新的，更加快速的方法。除开以前的函数公式和固定宽度的两种实现方法。

　　1、在B2数据单元格内输入A2单元格内身份证号码所对应的出生日期：19590621

　　2、然后单击B3数据单元格，按下快捷键CTRL+E，便可以迅速提取A列中所有的身份证号所对应的出生日期，无论多少一次性提取。

3、没有第三步。完

小编建议你的电脑上面如果还没有office 2013，那么强烈推荐你去换上2013版。相对于以往的版本来说这个版本有很多东西能让们的工作效率提高。同时也希望大家继续关注小编。

关于身份证号的操作，相信大家并不陌生，但是对于用到的函数，大家可能一直在犯糊涂，今天我们要学习的内容就是关于身份证号相关的函数……

一、提取出生年月。

方法：

1、选定目标单元格。

2、输入公式：=TEXT(MID(C3,7,8),"00-00-00")。

3、Ctrl+Enter填充。

备注：

如果对提取的出生年月没有格式要求，可以使用公式：=MID(c3,7,8）来实现。

二、自动填充性别。

方法：

1、选定目标单元格。

2、输入公式：=IF(MOD(MID(C3,17,1),2),"男","女")

3、Ctrl+Enter填充。

三、自动填充年龄。

方法：

1、选定目标单元格。

2、输入公式：=DATEDIF(D3,TODAY(),"y")。

3、Ctrl+Enter填充。

四、计算退休时间。

方法：

1、选定目标单元格。

2、输入公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。

3、Ctrl+Enter填充。

4、设置单元格格式为：日期，并选择日期格式。

备注：

此处的计算方法是以男60岁，女50岁为退休原则进行计算的。如果男同志60岁，女同志55岁退休，男同志比女同志多工作60个月（5年），加上女性55年*12个月。则计算公式为：=EDATE(D3,MOD(MID(C3,17,1),2)*60+660)。

上次给大家分享了《2017年最全的excel函数大全（5）——逻辑函数》，这次分享给大家日期和时间函数（上）。

DATE 函数

返回特定日期的序列号

描述

DATE 函数返回表示特定日期的连续序列号。

用法

DATE(year,month,day)

DATE 函数用法具有下列参数：

ü Year：必需。year 参数的值可以包含一到四位数字。Excel 将根据计算机正在使用的日期系统来解释 year 参数。默认情况下，Microsoft Excel for Windows 使用的是 1900 日期系统，这表示第一个日期为 1900 年 1 月 1 日。

提示: 为避免出现意外结果，请对 year 参数使用四位数字。例如，“07”可能意味着“1907”或“2007”。因此，使用四位数的年份可避免混淆。

· 如果 year 介于 0（零）到 1899 之间（包含这两个值），则 Excel 会将该值与 1900 相加来计算年份。例如，DATE(108,1,2) 返回 2008 年 1 月 2 日 (1900+108)。

· 如果 year 介于 1900 到 9999 之间（包含这两个值），则 Excel 将使用该数值作为年份。例如，DATE(2008,1,2) 将返回 2008 年 1 月 2 日。

· 如果 year 小于 0 或大于等于 10000，则 Excel 返回 错误值 #NUM!。

ü 月：必需。 一个正整数或负整数，表示一年中从 1 月至 12 月（一月到十二月）的各个月。

· 如果 month 大于 12，则 month 会从指定年份的第一个月开始加上该月份数。例如，DATE(2008,14,2) 返回表示 2009 年 2 月 2 日的序列数。

· 如果 month 小于 1，则 month 会从指定年份的第一个月开始减去该月份数，然后再加上 1 个月。例如，DATE(2008,-3,2) 返回表示 2007 年 9 月 2 日的序列号。

ü 日：必需。 一个正整数或负整数，表示一月中从 1 日到 31 日的各天。

· 如果 day 大于指定月中的天数，则 day 会从该月的第一天开始加上该天数。例如，DATE(2008,1,35) 返回表示 2008 年 2 月 4 日的序列数。

· 如果 day 小于 1，则 day 从指定月份的第一天开始减去该天数，然后再加上 1 天。例如，DATE(2008,1,-15) 返回表示 2007 年 12 月 16 日的序列号。

注意: Excel 可将日期存储为连续序列号，以便能在计算中使用它们。1900 年 1 月 1 日的序列号为 1，2008 年 1 月 1 日的序列号为 39448，这是因为它与 1900 年 1 月 1 日之间相差 39,447 天。需要更改数字格式（设置单元格格式）以显示正确的日期。

案例

案例 1

例如：=DATE(C2,A2,B2) 将单元格 C2 中的年、单元格 A2 中的月以及单元格 B2 中的日合并在一起，并将它们放入一个单元格内作为日期。以下案例显示了单元格 D2 中的最终结果。

案例 2根据其他日期计算某个日期

可以使用 DATE 函数创建基于其他单元格中日期的一个日期。例如，可以使用 YEAR、MONTH 和 DAY 函数来创建基于另一个单元格的周年纪念日期。假设，某个员工第一天上班的日期为 2016 年 10 月 1 日，则可以使用 DATE 函数创建他上班 5 周年的纪念日期：

1. DATE 函数会创建一个日期。

2. =DATE(YEAR(C2)+5,MONTH(C2),DAY(C2))

3. YEAR 函数会查找单元格 C2 并从中提取“2012”。

4. “+5”表示加上 5 年，并在单元格 D2 中创建“2017”作为周年纪念日的年。

5. MONTH 函数从单元格 C2 中提取“3”。这将在单元格 D2 中创建“3”作为月。

6. DAY 函数从单元格 C2 中提取“14”。这将在单元格 D2 中创建“14”作为天。

案例 3 将文本字符串和数字转换为日期

有时Excel的日期是无法识别的。这可能是因为数字与典型的日期不相似，也可能因为数据被设置成了文本格式。如果是这种情况，则可以使用 DATE 函数将信息转换成日期。例如，在下图中，单元格 C2 包含采用以下格式的日期：YYYYMMDD。它也被设置成了文本格式。若要将其转换成日期，则可以将 DATE 函数与 LEFT、MID 和 RIGHT 函数配合使用。

1. DATE 函数会创建一个日期。

2. =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))

3. LEFT 会在单元格 C2 中查找并从左起提取前 4 个字符。这将在单元格 D2 中创建“2014”作为转换后日期的年。

4. MID 函数将在单元格 C2 中查找。它将从第 5 个字符开始，然后向右提取 2 个字符。这将在单元格 D2 中创建“03”作为转换后日期的月。因为 D2 的格式设置为 Date，因此“0”不包括在最终结果中。

5. RIGHT 函数会在单元格 C2 中查找，然后从最右侧开始向左提取前 2 个字符。这将在 D2 中创建“14”作为日期的日。

案例 4 按一定的天数加减日期

若要按一定的天数加减日期，只需向值或包含日期的单元格引用加上或减去天数即可。

在以下案例中，单元格 A5 包含我们想加上和减去 7 天（C5 中的值）的日期。

DATEDIF 函数

计算两个日期之间的天数、月数或年数。

描述

计算两个日期之间相隔的天数、月数或年数。警告：Excel 提供了 DATEDIF 函数，以便支持来自 Lotus 1-2-3 的旧版工作簿。在某些应用场景下，DATEDIF 函数计算结果可能并不正确。有关详细信息，请参阅本文中的“已知问题”部分。

用法

DATEDIF(start_date,end_date,unit)

ü Start_date：用于表示时间段的第一个（即起始）日期的日期。 日期值有多种输入方式：带引号的文本字符串（例如 2001/1/30）、序列号（例如 36921，在商用 1900 日期系统时表示 2001 年 1 月 30 日）或其他公式或函数的结果（例如 DATEVALUE(2001/1/30)）。

ü End_date：用于表示时间段的最后一个（即结束）日期的日期。

ü Unit：要返回的信息类型：

其他

l 日期存储为可用于计算的序列号。默认情况下，1899 年 12 月 31 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。

l DATEDIF 函数在用于计算年龄的公式中很有用。

案例

已知问题

“MD”参数可能导致出现负数、零或不准确的结果。若要计算上一完整月份后余下的天数，可使用如下方法：

此公式从单元格 E17 中的原始结束日期 (5/6/2016) 减去当月第一天 (5/1/2016)。其原理如下：首先，DATE 函数会创建日期 5/1/2016。DATE 函数使用单元格 E17 中的年份和单元格 E17 中的月份创建日期。1 表示该月的第一天。DATE 函数的结果是 5/1/2016。然后，从单元格 E17 中的原始结束日期（即 5/6/2016）减去该日期。5/6/2016 减 5/1/2016 得 5 天。

DATEVALUE 函数

将文本格式的日期转换为序列号

描述

DATEVALUE 函数将存储为文本的日期转换为 Excel 识别为日期的序列号。 例如，公式=DATEVALUE(1/1/2008) 返回 39448，即日期 2008-1-1 的序列号。 即使如此，请注意，计算机的系统日期设置可能会导致 DATEVALUE 函数的结果会与此案例不同。

如果工作表包含采用文本格式的日期并且要对这些日期进行筛选、排序、设置日期格式或执行日期计算，则 DATEVALUE 函数将十分有用。

用法

DATEVALUE(date_text)

DATEVALUE 函数用法具有下列参数：

ü Date_text 必需。代表采用 Excel 日期格式的日期的文本，或是对包含这种文本的单元格的引用。例如，用于表示日期的引号内的文本字符串 2008-1-30 或 30-Jan-2008。

· 使用 Microsoft Excel for Windows 中的默认日期系统时，参数 date_text 必须代表 1900 年 1 月 1 日和 9999 年 12 月 31 日之间的某个日期。 如果参数 date_text的值在此范围之外， DATEVALUE函数将返回错误值 “#VALUE!。

· 如果省略参数 date_text 中的年份部分，则 DATEVALUE 函数会使用计算机内置时钟的当前年份。 参数 date_text 中的时间信息将被忽略。

其他

l Excel 可将日期存储为序列号，以便可以在计算中使用它们。 默认情况下，1900 年 1 月 1 日的序列号为 1，2008 年 1 月 1 日的序列号为 39,448，这是因为它距 1900 年 1 月 1 日有 39,447 天。

l 大部分函数都会自动将日期值转换为序列数。

案例

DAY 函数

将序列号转换为月份日期

描述

返回以序列数表示的某日期的天数。 天数是介于 1 到 31 之间的整数。

用法

DAY(serial_number)

DAY 函数用法具有下列参数：

ü Serial_number 必需。要查找的日期。应使用 DATE 函数输入日期，或将日期作为其他公式或函数的结果输入。例如，使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本形式输入，则会出现问题。

其他

l Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。

l 无论提供的日期值的显示格式如何，YEAR、MONTH 和 DAY 函数返回的值都是公历值。例如，如果提供的日期的显示格式是回历，则 YEAR、MONTH 和 DAY 函数返回的值将是与对应的公历日期相关联的值。

案例

DAYS 函数

返回两个日期之间的天数

描述

返回两个日期之间的天数。

用法

DAYS(end_date, start_date)

DAYS 函数用法具有以下参数。

ü End_date 必需。 Start_date 和 End_date 是用于计算期间天数的起止日期。

ü Start_date 必需。Start_date 和 End_date 是用于计算期间天数的起止日期。

注意: Excel 可将日期存储为序列号，以便可以在计算中使用它们。 默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39447 天。

其他

l 如果两个日期参数为数字，DAYS 使用 EndDate–StartDate 计算两个日期之间的天数。

l 如果任何一个日期参数为文本，该参数将被视为 DATEVALUE(date_text) 并返回整型日期，而不是时间组件。

l 如果日期参数是超出有效日期范围的数值，DAYS 返回 #NUM! 错误值。

l 如果日期参数是无法解析为字符串的有效日期，DAYS 返回 #VALUE! 错误值。

案例

DAYS360 函数

以一年 360 天为基准计算两个日期间的天数

描述

按照一年 360 天的算法（每个月以 30 天计，一年共计 12 个月），DAYS360 函数返回两个日期间相差的天数，这在一些会计计算中将会用到。 如果财会系统是基于一年 12 个月，每月 30 天，可使用此函数帮助计算支付款项。

用法

DAYS360(start_date,end_date,[method])

DAYS360 函数用法具有下列参数：

ü Start_date、end_date 必需。 用于计算期间天数的起止日期。 如果 start_date 在 end_date 之后，则 DAYS360 函数将返回一个负数。 应使用 DATE 函数输入日期，或者将从其他公式或函数派生日期。 例如，使用函数 DATE(2008,5,23) 以返回 2008 年 5 月 23 日。 如果日期以文本形式输入，则会出现问题。

ü 方法 可选。 逻辑值，用于指定在计算中是采用美国方法 还是欧洲方法。

注意:Excel 可将日期存储为序列号，以便可以在计算中使用它们。 默认情况下，1900 年 1 月 1 日的序列号为 1，2008 年 1 月 1 日的序列号为 39,448，这是因为它距 1900 年 1 月 1 日有 39,447 天。

案例

EDATE 函数

返回用于表示开始日期之前或之后月数的日期的序列号

描述

返回表示某个日期的序列号，该日期与指定日期 (start_date) 相隔（之前或之后）指示的月份数。 使用函数 EDATE 可以计算与发行日处于一月中同一天的到期日的日期。

用法

EDATE(start_date, months)

EDATE 函数用法具有以下参数：

ü Start_date 必需。一个代表开始日期的日期。应使用 DATE 函数输入日期，或将日期作为其他公式或函数的结果输入。例如，使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本形式输入，则会出现问题。

ü Months必需。 start_date 之前或之后的月份数。 months 为正值将生成未来日期；为负值将生成过去日期。

其他

Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。如果 start_date 不是有效日期，则 EDATE 返回 错误值 #VALUE!。 如果 months 不是整数，将截尾取整。

案例

EOMONTH 函数

返回指定月数之前或之后的月份的最后一天的序列号

描述

返回某个月份最后一天的序列号，该月份与 start_date 相隔（之后或之后）指示的月份数。 使用函数 EOMONTH 可以计算正好在特定月份中最后一天到期的到期日。

用法

EOMONTH(start_date, months)

EOMONTH 函数用法具有以下参数：

ü Start_date 必需。一个代表开始日期的日期。应使用 DATE 函数输入日期，或将日期作为其他公式或函数的结果输入。例如，使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本形式输入，则会出现问题。

ü Months 必需。 start_date 之前或之后的月份数。 months 为正值将生成未来日期；为负值将生成过去日期。

注意: 如果 months 不是整数，将截尾取整。

其他

l Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1，而 2008 年 1 月 1 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。

l 如果 start_date 不是有效日期，则 EOMONTH 返回 错误值 #NUM!。

l 如果 start_date 加 months 产生非法日期值，则 EOMONTH 返回 错误值 #NUM!。

案例

HOUR 函数

将序列号转换为小时

描述

返回时间值的小时数。 小时数是介于 0 ...

我们一般在这里学生档案以及员工的身份证信息时，在Excel输入身认证号以后，我们一般还得手动去输入身份证号码内的出生年月，这样无形中增加了我们的工作量，接下来小编就用一组模拟的数据，来演示如何快速的提取身份证号码中的出生年月：

提取方法一：使用函数公式提取，在C2单元格内输入以下公式，并向下填充即可。

=TEXT(MID(B2,7,8),"0-00-00")+0

注意：如果说你输入公式后，结果变成“30597”，那么我们就需要单元格的格式，如图：

解释：这个公式是有“MID函数”“TEXT函数”组成，我们现在身份证号码B2单元格中从它的第7位开始截取，截取的字符长度是8位，得到的计算结果就是“19831008”。然后在通过TEXT函数“TEXT(MID(B2,7,8),"0-00-00")”让它变成“1983-10-08”这样的格式。

经过这样的公式后，就有了日期的格式“1983-10-08”，但是这样的结果还不是真正的日期格式。经过“MID函数”“TEXT函数”等函数计算后得出的数值都是文本格式，一般情况下，不能参与后续的计算处理。

那么，在TEXT函数后面加零是什么意思呢？通常把这种形式叫做加零运算，通过运算，将文本格式的数值变成真正的日期格式，如果将加零换成1*（1乘以），也可以实现将文本格式向数值格式的转换的。

提取方法二：使用基础操作“数据分列”的方法来提取。

第一步：选择B列数据区域，单击【数据】【分列】，在弹出【文本分列向导 第1步，共3步】对话框中，选择“固定宽度”，单击下一步。

在【文本分列向导 第2步，共3步】对话框数据预览窗口中，单击第6位和第7位之间的刻度，再单击第14位和第15位之间的刻度（也就是留出出生日期的宽度），单击【下一步】。

在【文本分列向导 第3步，共3步】对话框中，依次点击最左侧列，点选【不导入此列（跳过）】。点击最右侧列，点选【不导入此列（跳过）】。点击中间列，点选【日期】。目标区域选择C2，单击完成。

这样，所有的出生日期就马上可以提取出来了。

学会了吗？这样是不是就减轻了你的工作压力~~

身份证是居民的身份象征，在各个领域都要用到身份证。身份证中可以提取许多的相关信息，然而大多数人都不知道如何应用身份证号来提取有用信息。下面，小编教大家用Excel提取出生日期、性别、年龄及户口所在地等信息。

判断身份证号码是否是18位

输入身份证号码时需要将单元格格式设置为文本。工作时有可能会将身份证号码输错，多输入或者少输入。所以，首先要判断身份证号码是否是18位，再提取身份证信息。也可以在计算公式中判断，但输入公式太多，容易出错。

公式“=IF(LEN(A3)=18,"是","否")”返回“是”即身份证号码为18位，反之则不是18位，输入错误。

LEN函数：得到字符串的长度。=LEN(A3)返回18表示A3单元格字符串长度是18。

出生日期

身份证号码是：622924199605211012第7位开始往后的4位是出生的年，第11位往后两位是出生的月份，第13位往后两位是出生日期。

公式“=1*MID(A3,7,4)”提取年份，从A3单元格第7个数字开始截取4个数字。

MID函数：一个字符串中截取出指定数量的字符，MID(A3,7,4)返回1996表示从A3单元格第7个数字开始截取4个数字。

=1*MID(A3,7,4)表示将MID(A3,7,4)提取的文本数据转为数值数据。

公式“=1*MID(A3,11,2)”提取月份，从A3单元格第11个数字开始截取2个数字

公式“=1*MID(A3,13,2)”提取日期，从A3单元格第13个数字开始截取2个数字

出生日期“=DATE(C3,D3,E3)”

或者直接用公式

“=DATE(1*MID(A3,7,4),1*MID(A3,11,2),1*MID(A3,13,2))”提取出生日期

年龄

实岁计算

=TRUNC((TODAY()-E3)/365)，当前日期减出生日期再除以365，然后用TRUNC函数去掉结果的小数部分

TRUNC函数：作用是去掉结果的小数部分。

TODAY函数：取得当前系统日期，不需要参数。

虚岁计算

=YEAR(TODAY())+1-YEAR(F3)

YEAR函数：返回某个日期的年份，YEAR(F3)返回1996表示出生年份是1996年。

实岁换算成虚岁：在本人生日到来之前，虚岁=实岁+2；在本人生日到来及以后，虚岁=实岁+1。

性别

方法一

身份证号码第17位是单数代表男性，双数代表女性。

=IF(ISEVEN(MID(A3,17,1))=TRUE,"女","男")

ISEVEN函数：ISEVEN(MID(A3,17,1))=TRUE表示身份证号码第17位是双数，为女性；ISEVEN(MID(A3,17,1))=FALSE表示身份证号码第17位是单数，为男性。

方法二

=IF(MOD(MID(A3,17,1),2)=1,"男","女")

MOD函数：两个数值表达式作除法运算后的余数，MOD(MID(A3,17,1),2)表示第17位数除以2，返回1则为男性，反之则为女性。

户口所在地

地区编码：身份证地区编码信息可直接在网上查找

城市

=VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)

LEFT函数：一个字符串的左边截取出指定数量的字符，LEFT(A2,4)表示从A2单元格最左边开始，截取4个字符

VLOOKUP函数：一个查找函数

VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

=VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)表示在地区编码!A2:B3559区域内匹配身份证前4位，采用模糊查找返回“地区编码!A2:B3559”的第二列。

注意：此处必须用1*LEFT(A2,4)将LEFT(A2,4)提取的文本数据转化为数值数据才能用VLOOKUP函数查找

区/县

=VLOOKUP(1*LEFT(A2,6),地区编码!A2:B3559,2,0)

=B2&C2

或者直接提取户籍信息：

=VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)&VLOOKUP(1*LEFT(A2,6),地区编码!A2:B3559,2,0)

以上就是用Excel提取出生日期、性别、年龄、及户口所在地等信息的具体方法，视频操作见于之前发的文章。感谢您的阅读！

工具：office  excel

步骤：

1、打开office  excel，Ctrl+;同时按下这两个键，就会自动生成当前日期。

2、提取“年”。这个操作使用日期函数中的YEAR函数。鼠标选中B4单元格，在英文状态下输入“=YEAR(A4)”，按下“Enter”键即可输出日期中的“年份”。

3、提取“月”。这个操作使用日期函数中的MONTH函数。鼠标选中C4单元格，在英文状态下输入“=MONTH(A4)”,按下“Enter”键即可输出日期中的“月份”。

4、提取“日”。这个操作使用日期函数中的DAY函数。鼠标选中D4单元格，在英文状态下输入“=DAY(A4)”,按下“Enter”键即可输出日期中的“日份”。

5、将鼠标移至右下角。会发现鼠标变为黑体的“+”号。鼠标向下拉，即可快捷方便地输出余下的日期中的“年份”。

（本文内容由百度知道网友泡影果果616贡献）