欢迎访问辉煌国际网站!
设为首页 | 加入收藏 | 会员入口

辉煌国际娱乐 校园短笛 辉煌国际登入 教学管理 高级论坛 幼教天地 家教导航 班队聚焦 继教广角 安全伴我行
名师团队 后勤管理 电教平台 生活指南 法制天地 精彩美文 学生作业 数学广角 英语沙龙 体卫艺之窗
您现在的位置:扶余市实验小学 >> 电教平台
日期与时间函数
作者:zws 文章来源:本站原创 点击数: 更新时间:2015-4-25 19:30:29

在数据表的处理过程中,日期与时间的函数是相当重要的处理依据。而excel在这方面也提供了相当丰富的函数供大家使用。

(一)取出当前系统时间/日期信息

用于取出当前系统时间/日期信息的函数主要有NOWTODAY

语法形式均为函数名()。

(二)取得日期/时间的部分字段值

如果需要单独的年份、月份、日数或小时的数据时,可以使用HOURDAYMONTHYEAR函数直接从日期/时间中取出需要的数据。具体示例参看图1

比如,需要返回2001-5-30 12:30 PM的年份、月份、日数及小时数,可以分别采用相应函数实现。

YEAR(E5)=2001

MONTH(E5)=5

DAY(E5)=30

HOUR(E5)=12

 

1

此外还有更多有用的日期/时间函数,可以查阅附表。下面我们将以一个具体的示例来说明excel的文本函数与日期函数的用途。

三、示例:做一个美观简洁的人事资料分析表

1、 示例说明

在如图2所示的某公司人事资料表中,除了编号、员工姓名、身份证号码以及参加工作时间为手工添入外,其余各项均为用函数计算所得。

 

2

在此例中我们将详细说明如何通过函数求出:

1)自动从身份证号码中提取出生年月、性别信息。

2)自动从参加工作时间中提取工龄信息。

2、身份证号码相关知识

在了解如何实现自动从身份证号码中提取出生年月、性别信息之前,首先需要了解身份证号码所代表的含义。我们知道,当今的身份证号码有15/18位之分。早期签发的身份证号码是15位的,现在签发的身份证由于年份的扩展(由两位变为四位)和末尾加了效验码,就成了18位。这两种身份证号码将在相当长的一段时期内共存。两种身份证号码的含义如下:

115位的身份证号码:1~6位为地区代码,7~8位为出生年份(2)9~10位为出生月份,11~12位为出生日期,第13~15位为顺序号,并能够判断性别,奇数为男,偶数为女。

218位的身份证号码:1~6位为地区代码,7~10位为出生年份(4)11~12位为出生月份,13~14位为出生日期,第15~17位为顺序号,并能够判断性别,奇数为男,偶数为女。18位为效验位。

3、 应用函数

在此例中为了实现数据的自动提取,应用了如下几个excel函数。

1IF函数:根据逻辑表达式测试的结果,返回相应的值。IF函数允许嵌套。

语法形式为:IFlogical_test, value_if_true,value_if_false

2CONCATENATE:将若干个文字项合并至一个文字项中。

语法形式为:CONCATENATE(text1,text2……)

3MID:从文本字符串中指定的起始位置起,返回指定长度的字符。

语法形式为:MID(text,start_num,num_chars)

4TODAY:返回计算机系统内部的当前日期。

语法形式为:TODAY()

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

语法形式为:DATEDIF(start_date,end_date,unit)

6VALUE:将代表数字的文字串转换成数字。

语法形式为:VALUE(text)

7RIGHT:根据所指定的字符数返回文本串中最后一个或多个字符。

语法形式为:RIGHT(text,num_chars)

8INT:返回实数舍入后的整数值。语法形式为:INT(number)

4、 公式写法及解释(以员工Andy为例说明)

说明:为避免公式中过多的嵌套,这里的身份证号码限定为15位的。如果您看懂了公式的话,可以进行简单的修改即可适用于18位的身份证号码,甚至可适用于1518两者并存的情况。

1)根据身份证号码求性别

=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"","")

公式解释:a. RIGHT(E4,3)用于求出身份证号码中代表性别的数字,实际求得的为代表数字的字符串

b. VALUE(RIGHT(E4,3)用于将上一步所得的代表数字的字符串转换为数字

c. VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用于判断这个身份证号码是奇数还是偶数,当然你也可以用Mod函数来做出判断。

d. =IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"","")及如果上述公式判断出这个号码是偶数时,显示"",否则,这个号码是奇数的话,则返回""

2)根据身份证号码求出生日期

=CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2),"/",MID(E4,11,2))

公式解释:a. MID(E4,7,2)为在身份证号码中获取表示年份的数字的字符串

b. MID(E4,9,2) 为在身份证号码中获取表示月份的数字的字符串

c. MID(E4,11,2) 为在身份证号码中获取表示日期的数字的字符串

d. CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2),"/",MID(E4,11,2))目的就是将多个字符串合并在一起显示。

3)根据参加工作时间求年资(即工龄)

=CONCATENATE(DATEDIF(F4,TODAY(),"y"),"",DATEDIF(F4,TODAY(),"ym"),"个月")

公式解释:

a. TODAY()用于求出系统当前的时间

b. DATEDIF(F4,TODAY(),"y")用于计算当前系统时间与参加工作时间相差的年份

c. DATEDIF(F4,TODAY(),"ym")用于计算当前系统时间与参加工作时间相差的月份,忽略日期中的日和年。

d. =CONCATENATE(DATEDIF(F4,TODAY(),"y"),"",DATEDIF(F4,TODAY(),"ym"),"个月")目的就是将多个字符串合并在一起显示。

5. 其他说明

在这张人事资料表中我们还发现,创建日期:31-05-2001时显示在同一个单元格中的。这是如何实现的呢?难道是手工添加的吗?不是,实际上这个日期还是变化的,它显示的是系统当前时间。这里是利用函数 TODAY 和函数 TEXT 一起来创建一条信息,该信息包含着当前日期并将日期以"dd-mm-yyyy"的格式表示。

具体公式写法为:="创建日期:"&TEXT(TODAY(),"dd-mm-yyyy")<BR>

至此,我们对于文本函数、日期与时间函数已经有了大致的了解,同时也设想了一些应用领域。相信随着大家在这方面的不断研究,会有更广泛的应用。

附二、日期与时间函数

函数名

函数说明

语法

DATE

返回代表特定日期的系列数。

DATE(year,month,day)

DATEDIF

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

DATEDIF(start_date,end_date,unit)

DATEVALUE

函数 DATEVALUE 的主要功能是将以文字表示的日期转换成一个系列数。

DATEVALUE(date_text)

DAY

返回以系列数表示的某日期的天数,用整数 1 31 表示。

DAY(serial_number)

DAYS360

按照一年 360 天的算法(每个月以 30 天计,一年共计 12 个月),返回两日期间相差的天数。

DAYS360(start_date,end_date,method)

EDATE

返回指定日期 (start_date) 之前或之后指定月份数的日期系列数。使用函数 EDATE 可以计算与发行日处于一月中同一天的到期日的日期。

EDATE(start_date,months)

EOMONTH

返回 start-date 之前或之后指定月份中最后一天的系列数。用函数 EOMONTH 可计算特定月份中最后一天的时间系列数,用于证券的到期日等计算。

EOMONTH(start_date,months)

HOUR

返回时间值的小时数。即一个介于 0 (12:00 A.M.) 23 (11:00 P.M.) 之间的整数。

HOUR(serial_number)

MINUTE

返回时间值中的分钟。即一个介于 0 59 之间的整数。

MINUTE(serial_number)

MONTH

返回以系列数表示的日期中的月份。月份是介于 1(一月)和 12(十二月)之间的整数。

MONTH(serial_number)

NETWORKDAYS

返回参数 start-data end-data 之间完整的工作日数值。工作日不包括周末和专门指定的假期

NETWORKDAYS(start_date,end_date,holidays)

NOW

返回当前日期和时间所对应的系列数。

NOW( )

SECOND

返回时间值的秒数。返回的秒数为 0 59 之间的整数。

SECOND(serial_number)

TIME

返回某一特定时间的小数值,函数 TIME 返回的小数值为从 0 0.99999999 之间的数值,代表从 0:00:00 (12:00:00 A.M) 23:59:59 (11:59:59 P.M) 之间的时间。

TIME(hour,minute,second)

TIMEVALUE

返回由文本串所代表的时间的小数值。该小数值为从 0 0.999999999 的数值,代表从 0:00:00 (12:00:00 AM) 23:59:59 (11:59:59 PM) 之间的时间。

TIMEVALUE(time_text)

TODAY

返回当前日期的系列数,系列数是 Microsoft excel用于日期和时间计算的日期-时间代码。

TODAY( )

WEEKDAY

返回某日期为星期几。默认情况下,其值为 1(星期天)到 7(星期六)之间的整数。

WEEKDAY(serial_number,return_type)

WEEKNUM

返回一个数字,该数字代表一年中的第几周。

WEEKNUM(serial_num,return_type)

WORKDAY

返回某日期(起始日期)之前或之后相隔指定工作日的某一日期的日期值。工作日不包括周末和专门指定的假日。

WORKDAY(start_date,days,holidays)

YEAR

返回某日期的年份。返回值为 1900 9999 之间的整数。

YEAR(serial_number)

YEARFRAC

返回 start_date end_date 之间的天数占全年天数的百分比。

YEARFRAC(start_date,end_date,basis)

  • 上一篇文章:

  • 下一篇文章:
  •   图片文章
      最新推荐
    推荐文章Excel函数教程十一——财务函数
    推荐文章Excel函数教程十——工程函数
    推荐文章excel函数学习九——统计函数
    推荐文章excel函数学习八——查询与引用函数
    推荐文章excel函数学习七——日期与时间函数
    推荐文章excel函数学习六——文本函数
    推荐文章excel函数学习五——逻辑函数
    推荐文章excel函数学习四——数学和三角函数
    推荐文章excel函数学习三——函数的分类
    推荐文章excel函数学习二——使用函数的步骤
      热点关注
    推荐文章Excel函数教程十一——财务函数
    推荐文章Excel函数教程十——工程函数
    推荐文章excel函数学习九——统计函数
    推荐文章excel函数学习八——查询与引用函数
    推荐文章excel函数学习七——日期与时间函数
    推荐文章excel函数学习六——文本函数
    推荐文章excel函数学习五——逻辑函数
    推荐文章excel函数学习四——数学和三角函数
    推荐文章excel函数学习三——函数的分类
    推荐文章excel函数学习二——使用函数的步骤
    | 设为首页 | 加入收藏 | 联系站长 | 友情链接 | 版权申明 |
    版权归属:辉煌国际 ┋吉林省松原市扶余市工商路
    网站设计与维护:扶余市易讯网络 0438-5870123 QQ:350747077 Email:syfysx@163.com
    互联网网站信息备案号: 吉ICP备11005727号-1