excel时间公示
篇一:EXCLE日期时间计算公式
常用函数公式及操作技巧之三:
时间和日期应用
——通过知识共享树立个人品牌。
自动显示当前日期公式
=YEAR(NOW()) 当前年
=MONTH(NOW()) 当前月
=DAY((NOW())) 当前日
如何在单元格中自动填入当前日期
Ctrl+;
如何判断某日是否星期天
=WEEKDAY(A2,2)
=TEXT(A1,"aaaa")
=MOD(A1,7)<2
某个日期是星期几
比如2007年2月9日,在一单元格内显示星期几。
=TEXT(A1,"aaa") (五)
=TEXT(A1,"aaaa") (星期五)
=TEXT(A1,"ddd") (Fri)
=TEXT(A1,"dddd") (Friday)
什么函数可以显示当前星期
如:星期二 10:41:56
=TEXT(NOW(),"aaaa hh:mm:ss")
求本月天数
设A1为2006-8-4 求本月天数
A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
也有更簡便的公式:=DAY(EOMONTH(NOW(),0))
具箱。 需加載分析工
当前月天
数: =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-DATE(YEAR(TODAY()),MONTH(TODAY()),1)
用公式算出除去当月星期六、星期日以外的天数
=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&":"&DATE(YEAR(NOW()),MONTH(NOW
())+1,0))),7)>1))
显示昨天的日期
每天需要单元格内显示昨天的日期,但双休日除外。
例如,今天是7月3号的话,就显示7月2号,如果是7月9号,就显示7月6号。
=IF(TEXT(TODAY(),"AAA")="一
",TODAY()-3,IF(TEXT(TODAY(),"AAA")="日
",TODAY()-2,TODAY()-1))
=IF(TEXT(TODAY(),"AAA")="一",TODAY()-3,TODAY()-1) 关于取日期
怎么设个公式使A1在年月日向后推5年,变成2011-7-15
=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))
=EDATE(A1,12*5)
如何对日期进行上、中、下旬区分
=LOOKUP(DAY(A1),{0,11,21,31},{"上旬","中旬","下旬","下旬"})
如何获取一个月的最大天数
"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01 日期格式转换公式
将 “01/12/2005” 转换成“20050112”格式
=RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)
=
YEAR($A2)&TEXT(MONTH($A2),"00")&TEXT(DAY($A2),"00") 该公式不用设置数据有效性,但要设置储存格格式。
也可以用下列两方法:
1、先转换成文本, 然后再用字符处理函数。
2、[数据]-[分列] [日期]-[MDY]
将“2005年9月”转换成“200509”格式
先用公式:=text(a1,"yyyymm")+0然后将单元格格式为常规。 将“2005-8-6”格式转换为“20050806”格式
用公式:=TEXT(A1,"YYYYMMDD")
反之,将20050806转为日期2005-8-6格式,可用公式:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
另四种公式:
=text(a1,"0000-00-00")显示:
2005-08-06
=--TEXT(A1,"#-00-00"),把单元格设置为日期格式 显示:2005-8-6
=TEXT(20050806,"0000-00-00")*1,单元格设置日期
型 显示:2005-8-6
=
VALUE(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2))显示:2005-8-6
将“20060501”转换为“2006-05-01”格式
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
将“199306”转换为“1993-6”
公式1:=LEFT(A3,4)&"-"&RIGHT(A3,2)*1
公式2:=--TEXT(A3*100+1,"#-00-00") 公式2需要设置单元格格式,自定义:e-m
公式3:=TEXT(TEXT(A3&"01","0000-00-00"),"e-m")
把198405转换成1984.05
一、查找—1984,替换—1984.
二、如果全部是年月的话,我个人建议,
1、采取辅助=mid(xxxxxx,1,4) & "." & right(xxxxxx,2)
2、选中这列,用数据中的分列。然后?????
三、单元格格式/数字/自定义,类型下面输入:####"."##
将文本“2004.01.02” 转换为日期格式:2004-1-2 =DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))
将2005-8-6转换为2005年8月6日格式
=TEXT(A1,"yyyy""年""m""月""d""日"";@")
象22怎样转换成22日?转成当年当月的日子
公式为:=date(year(now()),month(now()),22)
将“2006年5月”转换成“2006年05月”
公式为:=TEXT(A8,"yyyy""年""mm""月"";@")
也可以这样处理:选中单元格,设置单元格公式-数字-自定义,将yyyy“年”m“月”改为:yyyy“年”mm“月”,即可。但这方法打印出来显示为:2006/5/
将“1968年6月12日”转换为“1968/6/12”格式 =YEAR(A1)&"/"&MONTH(A1)&"/"&DAY(A1)显示:1968/6/12 =TEXT(A1,"yyyy/mm/dd") 显示:1968/06/12 将“1968年6月12日”转换为“1968-6-12”格式
=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1) 显示:1968-6-12 =TEXT(A1,"yyyy-mm-dd") 显示:1968-06-12 将1993-12-28的日期格式转换成1993年12月 =CONCATENATE(YEAR(A1),"年",MONTH(A1),"月")
=YEAR(A1)&"年"&MONTH(A1)&"月"
也可以自定义格式 [$-404]e"年"m"月"
将“1978-5-2”包含年月日的日期转换成“197805”只有年月的格式
=year(A1)&text(month(A1),"00")
要将“99.08.15” 格式转换成“1999.08.15”如何做
选中列,数据菜单中选分列,分列过程中“格式”选“日期YMD”,结束。 要保持2005/8/6格式
当输入2005/8/6后系统自动变成2005-8-6,要保持2005/8/6格式,可以使用强制文本(前面加'号)或使用公式=TEXT(A1,"YYYY/MM/DD")。也可以用另一种公式:
=IF(ISERROR(TEXT(A1,"yyyy/mm/dd")),TEXT(A1,"0000!/00!/00"),TEXT(A1,"yyyy/mm/dd"))
将“二○○三年十二月二十五日”转为“2003-12-25”格式,
1、可以用数组公式将中文日期转化为日期系列数
{=14610+MATCH(SUBSTITUTE(A3,"元","一
"),TEXT(ROW($14611:$55153),"[DBNum1]yyyy年m月d日"),0)}
该公式速度较慢。
2、改进后的公式,速度要快的多:
{=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),"[DBNum1]0000"),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),"元","一"),TEXT(ROW($1:$366),"[DBNum1]m月d日"),0)),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),"元","一"),TEXT(ROW($1:$366),"[DBNum1]m月d日"),0)))}
要设置为1900年的日期格式。
日期格式转换
如A列是月份数为8,B列是日期数为18,如何在C列显示“8月18日” =A1&"月"&B1&"日"
反之,要将C列的“8月18日” 直接分别到D、E列,显示月份和日期, 月数份=LEFT(C5,FIND("月",C5)-1)
日期数=MID(C5,FIND("月",C5)+1,FIND("日",C5)-FIND("月",C5)-1)
也可分别用公式:
=month(--c5)
=day(--c5)
日期格式转换问题
输入的日期是:04-07-26. 与另一格的"001"合并,合并出来
是:040726001.
=TEXT(A1,"YYMMDD&qu(来自:WwW.CssYq.com 书业 网:excel时间公示)ot;)&"001"
要想自动取得“编制日期:XXXX年X月X日”
可在该单元格输入 ="编制日期:"&TEXT(TODAY(),"yyyy年m月d日")
? 2011
原创作品,转贴请注明作者和出处,留此信息。
篇二:Excel中时间计算的公式和基本原理
Excel中时间计算的公式和基本原理
1. 如何在excel中实现时间的计算?
如:如何计算2004年5月24日到1975年3月3日之间相隔多少天,急切想知道
A:假定,将单元格A1设置为没有小数点的数字格式,在A1单元格中输入,回车,即得到结果:
A1="2004-5-24"-"1975-3-3"=10675
来源:http://club.excelhome.net/thread-46832-1-1.html
********
2. 时间格式:13:00:00 单元格格式定义
Excel公式表示为:时间:*13:30:55,或自定义:[$-F400]h:mm:ss AM/PM 时间格式单元格可以直接求和。参见:
********
3. 更进一步学习:Excel时间计算的几个问题详解
在Excel中经常涉及到进行时间计算,例如下表是从北京到上海的某列车车次时刻表,我们可以在Excel中计算出该次列车在相邻两站之间的运行时间、在某站的停车时间以及从始发站出发到达某站时的总运行时间。在计算时间的过程中,可能会涉及到下面的几个问题。
可以搜搜“列车时刻表 filetype:xls”找实例文章。
********
4. 时间加减计算
问题的提出:
日期时间1——2005-5-15 9:10:45
日期时间2——2005-9-16 11:10:25
计算:日期时间2-日期时间1
结果表达:“XX(天) h:mm:dd”
问题分析及思路:
形如“2005-5-15 9:10:45”的日期时间数据可以用日期时间序列值表示。这个值是小数,其中整数部分表示天数,小数部分表示时间(可以转换成“时-分-秒” )。 用截尾取整函数TRUNC提取整数表示天数,用日期时间“2005-5-15 9:10:45”的序列值减去整数部分,其差表示时间的序列值。再用TXET函数把表示时间的序列值表示成“h:mm:dd”时间格式。
日期时间2-日期时间1=TRUNC()&" "&TXET()
解决方案:
假定
A2中输入日期时间1“2005-5-15 9:10:45”
B2中输入日期时间2“2005-9-16 11:10:25”
在C2中编辑公式——
=IF(OR(A2="",B2=""),"",TRUNC(B2-A2)&"
"&TEXT(B2-A2-TRUNC(B2-A2),"h:mm:ss"))
公式释义:
如果(IF)A2或B2是空白单元格(""),则显示空字符("");否则返回两个日期时间差的天数(TRUNC(B2-A2))和(&)时间差[时-分-秒: TEXT(B2-A2-TRUNC(B2-A2),"h:mm:ss")]。
假设A2单元格是起始时间,B2是结束时间。在其他如C2单元格写下公式:
1、时:分:秒格式(不计算天数):
=TIME(HOUR(B2-A2),MINUTE(B2-A2),SECOND(B2-A2))
2、天时分秒格式:
=CONCATENATE(DATEDIF(A2,B2,"d"),"天",HOUR(B2-A2),"时",MINUTE(B2-A2),"分",SECOND(B2-A2),"秒")
3、时分秒格式(将天数转换成小时):
=CONCATENATE(DATEDIF(A2,B2,"d")*24+HOUR(B2-A2),"时",MINUTE(B2-A2),"分",SECOND(B2-A2),"秒")
4、全都换算成秒:
=DATEDIF(A2,B2,"Y")*24*3600+HOUR(B2-A2)*3600+MINUTE(B2-A2)*60+SECOND(B2-A2)
***
CONCATENATE
功能:将多个字符串合并成一个,作用相当于“&”,如A1中ab,B1中cd,那么:CONCATENATE(A1,B1)返回值为:abcd
相当于:A1&B1
***
5. [英] DATEDIF function
Excel隐藏函数,在帮助和插入公式里面没有。
简要说明: 返回两个日期之间的年\月\日间隔数
语法
DATEDIF(start_date,end_date,unit)
Start_date 为一个日期,它代表时间段内的第一个日期或起始日期。End_date 为一个日期,它代表时间段内的最后一个日期或结束日期。Unit 为所需信息的返回类型。
Unit 返回
"Y" 时间段中的整年数。
"M" 时间段中的整月数。
"D" 时间段中的天数。
"MD" start_date 与 end_date 日期中天数的差。忽略日期中的月和年。"YM" start_date 与 end_date 日期中月数的差。忽略日期中的日和年。"YD" start_date 与 end_date 日期中天数的差。忽略日期中的年。
具体实例参见http://wenku.baidu.com/view/481d96114431b90d6c85c70d.html ********
6.时间计算的基本原理:
据此,可以看出,理论上,Excel时间计算的基本原理为:1天为时间数值1,1小时为1/24,1分钟为1/24/60,1秒为1/24/60/60,等等,依此类推。但在实际计算中,因数字精确度问题,可能略有误差,不过一般可以忽略不计。
即:按19位小数精度表示天、小时、分、秒的数值:
1DD=1/1=1.0000000000000000000,天
1HH=1/24=0.0416666666666667000, 时
1MM=1/24/60=0.0006944444444444440, 分
1SS=1/24/60/60=0.0000115740740740741.秒
对于时间的求和求差,可以输入 时:分:秒 格式的时间差值,进行加减运算,也可以直接套入表示天数的整数和小时分秒的小数来表示,或直接用公式来对小时、分、秒各自对应的分数和相应的倍数来求和。 初始值和结果值可以设定为:YYYY-MM-DD HH:MM:SS 的格式,方便阅读。
P.S.: Excel中带小数点的数值表示为 时:分:秒 格式的快速方法:设定时间格式,选择第一个带星号*的格式即可(实际为[$-F400]h:mm:ss AM/PM)。
篇三:excel中几个时间计算公式
假设b2为生日
=datedif(B2,today(),"y")
DATEDIF函数,除Excel 2000中在帮助文档有描述外,其他版本的Excel在帮助文档中都没有说明,并且在所有版本的函数向导中也都找不到此函数。但该函数在电子表格中确实存在,并且用来计算两个日期之间的天数、月数或年数很方便。微软称,提供此函数是为了与Lotus 1-2-3兼容。
该函数的用法为“DATEDIF(Start_date,End_date,Unit)”,其中Start_date为一个日期,它代表时间段内的第一个日期或起始日期。End_date为一个日期,它代表时间段内的最后一个日期或结束日期。Unit为所需信息的返回类型。
“Y”为时间段中的整年数,“M”为时间段中的整月数,“D”时间段中的天数。“MD”为Start_date与End_date日期中天数的差,可忽略日期中的月和年。“YM”为Start_date与End_date日期中月数的差,可忽略日期中的日和年。“YD”为Start_date与End_date日期中天数的差,可忽略日期中的年。比如,B2单元格中存放的是出生日期(输入年月日时,用斜线或短横线隔开),在C2单元格中输入“=datedif(B2,today(),"y")”(C2单元格的格式为常规),按回车键后,C2单元格中的数值就是计算后的年龄。此函数在计算时,只有在两日期相差满12个月,才算为一年,假如生日是2004年2月27日,今天是2005年2月28日,用此函数计算的年龄则为0岁,这样算出的年龄其实是最公平的。
身份证号提取年龄
=DATEDIF(--TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(CA1)<>15)*2),"#-00-00"),TODAY(),"y") 用excel计算退休日期
假设A列是姓名,B列是性别,C列(要设为日期型数据)是出生日期,D列(要设置为常规型)是年龄,E列(设置为日期型)是你要知道的退休那天的日期: A1到E1分别是:姓名 性别 出生日期 年龄 退休日 则:D2=year(now())-year(C2) 往下复制到所有即可
E2=if(b2="男",DATE(YEAR(C2)+60,MONTH(C2),DAY(C2)),DATE(YEAR(C2)+55,MONTH(C2),DAY(C2))) 往下复制到所有即可
如何在excel表里显示当前时间
其实不方便用函数来显示当前日期和时间的,原因是你的这些文件或报表肯定是要留档的,那样的话,下一次打开时,日期时间就会变成打开时的了。所以有两种办法供你参考:
1、不用公式:在做完表时,在日期时间单元格中按“Ctrl-;”即可获得当时的日期时间;
2、使用公式:用=now()而非=date(),=date()只有日期,然后进行菜单“工具->选项”,选择“重新计算”页,选中“人工重算”,勾不勾选“保存前自动重算”看自己的需要和想法了,如果勾选了,那日期时间那总是最后一次保存的日期时间,不勾选的话,如果你的表格中有公式记得准备存前按F9
篇四:Excel中关于日期的计算公式的运用
Excel中关于日期的计算公式的运用
方法1:在A1单元格输入前面的日期,比如“2004-10-10”,在A2单元格输入后面的日期,如“2005-6-7”。接着单击A3单元格,输入公式“=DATEDIF(A1,A2,"d")”。然后按下回车键,那么立刻就会得到两者的天数差“240”。
提示:公式中的A1和A2分别代表前后两个日期,顺序是不可以颠倒的。此外,DATEDIF函数是Excel中一个隐藏函数,在函数向导中看不到它,但这并不影响我们的使用。
方法2:任意选择一个单元格,输入公式“="2004-10-10"-"2005-6-7"”,然后按下回车键,我们可以立即计算出结果。
计算工作时间——工龄——
假如日期数据在D2单元格。
=DATEDIF(D2,TODAY(),"y")+1
注意:工龄两头算,所以加“1”。
如果精确到“天”——
=DATEDIF(D2,TODAY(),"y")&"年"&DATEDIF(D2,TODAY(),"ym")&"月"&DATEDIF(D2,TODAY(),"md")&"日"
二、计算2003-7-617:05到2006-7-713:50分之间相差了多少天、多少个小时多少分钟
假定原数据分别在A1和B1单元格,将计算结果分别放在C1、D1和E1单元格。
C1单元格公式如下:
=ROUND(B1-A1,0)
D1单元格公式如下:
=(B1-A1)*24
E1单元格公式如下:
=(B1-A1)*24*60
注意:A1和B1单元格格式要设为日期,C1、D1和E1单元格格式要设为常规.
三、计算生日,假设b2为生日
=datedif(B2,today(),"y")
DATEDIF函数,除2000中在帮助文档有描述外,其他版本的Excel在帮助文档中都没有说明,并且在所有版本的函数向导中也都找不到此函数。但该函数在电子表格中确实存在,并且用来计算两个
日期之间的天数、月数或年数很方便。微软称,提供此函数是为了与Lotus1-2-3兼容。
该函数的用法为“DATEDIF(Start_date,End_date,Unit)”,其中Start_date为一个日期,它代表时间段内的第一个日期或起始日期。End_date为一个日期,它代表时间段内的最后一个日期或结束日期。Unit为所需信息的返回类型。
“Y”为时间段中的整年数,“M”为时间段中的整月数,“D”时间段中的天数。“MD”为Start_date与End_date日期中天数的差,可忽略日期中的月和年。“YM”为Start_date与End_date日期中月数的差,可忽略日期中的日和年。“YD”为Start_date与End_date日期中天数的差,可忽略日期中的年。比如,B2单元格中存放的是出生日期(输入年月日时,用斜线或短横线隔开),在C2单元格中输入“=datedif(B2,today(),"y")”(C2单元格的格式为常规),按回车键后,C2单元格中的数值就是计算后的年龄。此函数在计算时,只有在两日期相差满12个月,才算为一年,假如生日是2004年2月27日,今天是2005年2月28日,用此函数计算的年龄则为0岁,这样算出的年龄其实是最公平的。