用EXCEL计算起止时间在各个时间段内的时长

用EXCEL计算起止时间在各个时间段内的时长

EXCELL中，常遇到这样的问题：已知起始时间和结束时间，如何计算该起止时间在指定时间段上的时间长度？

比如：

由于起止时间有多种跨越情况，且有零点转换，用EXCEL的自带公式和函数很难实现。下面这个VBA自定义函数，能够轻松解决上面的难题。

函数名tj(t1,t2,n)

3个参数：t1-开始时间，t2-结束时间，为“时分秒”时间格式，可直接引用单元格

n-整数{1|2|3}，(分别代表峰平谷的时间段)

返回值：以“时分秒”形式返回起(t1)止(t2)时间在参数n所代表的时间段内的时长。

在EXCEL工作表中，打开VBA编辑器，将下列代码作为模块插入，保存后即可在单元格中直接调用，格式开如：=Tj($A2,$B2,1)，返回开始时间A2、结束时间B2在7-11点时间段内的时长。

以下代码，在解决不同问题时，对部分参数适当修改即可实现。

Function Tj(t1, t2, n As Integer)

Dim f(2) As Integer, Ti(2), arr(2, 1) As Date

n = n - 1

arr(0, 0) = TimeValue("7:00:00")

arr(0, 1) = TimeValue("4:00:00")

arr(1, 0) = TimeValue("11:00:00")

arr(1, 1) = TimeValue("8:00:00")

arr(2, 0) = TimeValue("19:00:00")

arr(2, 1) = TimeValue("12:00:00")

s = t2 - t1 '总时长

If s < 0 Then

s = TimeValue("23:59:59") + s + TimeValue("00:00:01")

End If

'------------计算开始时间属于哪一时间段，存储于f(0)，并将其后的时间段存储于f(1)、f(2) Select Case t1

Case arr(0, 0) To arr(1, 0) - TimeValue("00:00:01")

f(0) = 0

f(1) = 1

f(2) = 2

t1_ = arr(0, 1) - (t1 - arr(0, 0)) 't1_用于记录开始时间至该时间段结束点的时长

Case arr(1, 0) To arr(2, 0) - TimeValue("00:00:01")

f(0) = 1

f(1) = 2

f(2) = 0

t1_ = arr(1, 1) - (t1 - arr(1, 0))

Case Else

f(0) = 2

f(1) = 0

f(2) = 1

If t1 > arr(2, 0) Then

t1_ = arr(2, 1) - (t1 - arr(2, 0))

Else

t1_ = arr(2, 0) - arr(2, 1) - t1

End If

End Select

'-------------计算总时长s在各时间段内的时长

arr(f(0), 1) = t1_

i = 0

While (s > 0 And i < 3)

Ti(f(i)) = WorksheetFunction.Min(arr(f(i), 1), s)

s = s - Ti(f(i))

i = i + 1

Wend

Ti(f(0)) = Ti(f(0)) + s '如果s在分配至其他时间段后仍有剩余

Tj = Ti(n) '返回指定时间段时长

If Tj = TimeValue("00:00:00") Then

Tj = ""

End If

End Function

Excel中如何计算日期差

Excel中如何计算日期差： ----Excel中最便利的工作表函数之一——Datedif名不见经传，但却十分好用。Datedif能返回任意两个日期之间相差的时间，并能以年、月或天数的形式表示。您可以用它来计算发货单到期的时间，还可以用它来进行2000年的倒计时。 ----Excel中的Datedif函数带有3个参数，其格式如下： ----=Datedif(start_date,end_date,units) ----start_date和end_date参数可以是日期或者是代表日期的变量，而units则是1到2个字符长度的字符串，用以说明返回日期差的形式（见表1）。图1是使用Datedif函数的一个例子，第2行的值就表明这两个日期之间相差1年又14天。units的参数类型对应的Datedif返回值 “y”日期之差的年数（非四舍五入） “m”日期之差的月数（非四舍五入） “d”日期之差的天数（非四舍五入） “md”两个日期相减后，其差不足一个月的部分的天数 “ym”两个日期相减后，其差不足一年的部分的月数 “yd”两个日期相减后，其差不足一年的部分的天数

表1units参数的类型及其含义 图1可以通过键入3个带有不同参数的Datedif公式来计算日期的差。units的参数类型 ----图中：单元格Ex为公式“=Datedif(Cx,Dx,“y”)”得到的结果（x=2,3,4......下同） ----Fx为公式“=Datedif(Cx,Dx,“ym”)”得到的结果 ----Gx为公式“=Datedif(Cx,Dx,“md”)”得到的结果 现在要求两个日期之间相差多少分钟，units参数是什么呢？ 晕，分钟你不能用天数乘小时再乘分钟吗？ units的参数类型对应的Datedif返回值 “y”日期之差的年数（非四舍五入） “m”日期之差的月数（非四舍五入） “d”日期之差的天数（非四舍五入） “md”两个日期相减后，其差不足一个月的部分的天数 “ym”两个日期相减后，其差不足一年的部分的月数 “yd”两个日期相减后，其差不足一年的部分的天数 假设你的数据从A2和B2开始，在C2里输入下面公式，然后拖拉复制。 =IF(TEXT(A2,"h:mm:ss")

EXCLE日期时间计算公式

(Excel)常用函数公式及操作技巧之三： 时间和日期应用 ——通过知识共享树立个人品牌。 自动显示当前日期公式 =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(TO DAY()),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”格式

EXCEL计算两个日期之间天数的函数

EXCEL计算两个日期之间天数的函数语法 DATEDIF(start_date,end_date,unit) Start_date 为一个日期，它代表时间段内的第一个日期或起始日期。日期有多种输入方法：带引号的文本串（例如"2001/1/30"）、系列数（例如，如果使用1900 日期系统则36921 代表2001 年 1 月30 日）或其他公式或函数的结果（例如，DATEVALUE("2001/1/30")）。有关日期系列数的详细信息，请参阅NOW。 End_date 为一个日期，它代表时间段内的最后一个日期或结束日期。 Unit 为所需信息的返回类型。 Unit 返回 "Y" 时间段中的整年数。 "M" 时间段中的整月数。 "D" 时间段中的天数。 "MD" start_date 与end_date 日期中天数的差。忽略日期中的月和年。 "YM" start_date 与end_date 日期中月数的差。忽略日期中的日和年。 "YD" start_date 与end_date 日期中天数的差。忽略日期中的年。 说明 Microsoft Excel 按顺序的系列数保存日期，这样就可以对其进行计算。如果工作簿使用1900 日期系统，则Excel 会将1900 年1 月1 日保存为系列数1。而如果工作簿使用1904 日期系统，则Excel 会将1904 年1 月1 日保存为系列数0，（而将1904 年1 月2 日保存为系列数1）。例如，在1900 日期系统中Excel 将1998 年 1 月 1 日保存为系列数35796，因为该日期距离1900 年 1 月 1 日为35795 天。请查阅Microsoft Excel 如何存储日期和时间。 Excel for Windows 和Excel for Macintosh 使用不同的默认日期系统。有关详细信息，请参阅NOW。 示例 DATEDIF("2001/1/1","2003/1/1","Y") 等于2，即时间段中有两个整年。 DATEDIF("2001/6/1","2002/8/15","D") 等于440，即在2001 年6 月1 日和2002 年8 月15 日之间有440 天。 DATEDIF("2001/6/1","2002/8/15","YD") 等于75，即在6 月1 日与8 月15 日之间有75 天，忽略日期中的年。 DATEDIF("2001/6/1","2002/8/15","MD") 等于14，即开始日期 1 和结束日期15 之间的差，忽略日期中的年和月。

Excel电子表格计算公式使用方法技巧总结

Excel电子表格计算公式使用方法25条公式技巧总结 对于Excel表格计算公式的方法实在太多，今天就整理了一个公式大全需要对有需要的朋友有些帮助。 1、两列数据查找相同值对应的位置 =MATCH(B1,A:A，0) 2、已知公式得结果 定义名称=EVALUATE(Sheet1!C1) 已知结果得公式 定义名称=GET.CELL(6,Sheet1!C1) 3、强制换行 用Alt+Enter 4、超过15位数字输入 这个问题问的人太多了，也收起来吧。一、单元格设置为文本;二、在输入数字前先输入' 5、如果隐藏了B列，如果让它显示出来？ 选中A到C列，点击右键，取消隐藏 选中A到C列，双击选中任一列宽线或改变任一列宽 将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。 6、EXCEL中行列互换 复制，选择性粘贴，选中转置，确定即可 7、Excel是怎么加密的 （1）、保存时可以的另存为>>右上角的"工具">>常规>>设置 （2）、工具>>选项>>安全性

8、关于COUNTIF COUNTIF函数只能有一个条件，如大于90，为=COUNTIF(A1:A10,">=90") 介于80与90之间需用减，为=COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90") 9、根据身份证号提取出生日期 （1）、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF (LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),"错误身份证号")) （2）、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1 10、想在SHEET2中完全引用SHEET1输入的数据 工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2 11、一列中不输入重复数字 [数据]--[有效性]--[自定义]--[公式] 输入=COUNTIF(A:A,A1)=1 如果要查找重复输入的数字 条件格式》公式》=COUNTIF(A:A,A5)>1》格式选红色 12、直接打开一个电子表格文件的时候打不开 “文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上 13、excel下拉菜单的实现 [数据]-[有效性]-[序列] 14、10列数据合计成一列 =SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1)) 15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH) （1）、根据符合行列两个条件查找对应结果

Excel表格公式使用基本操作及excel表格计算公式大全、使用技巧

Excel 部分函数列表. AND “与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。条件判断AVERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 CONCATENATE 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 DATE 给出指定数值的日期。显示日期 DATEDIF 计算返回两个日期参数的差值。计算天数 DAY 计算参数中指定日期或引用单元格中的日期天数。计算天数 DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。条件统计 FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。概率计算 IF 根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的计算结果。条件计算 INDEX 返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。数据定位 INT 将数值向下取整为最接近的整数。数据计算 ISERROR 用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。逻辑判断 LEFT 从一个文本字符串的第一个字符开始，截取指定数目的字符。截取数据LEN 统计文本字符串中字符数目。字符统计 MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。匹配位置MAX 求出一组数中的最大值。数据计算 MID 从一个文本字符串的指定位置开始，截取指定数目的字符。字符截取 MIN 求出一组数中的最小值。数据计算 MOD 求出两数相除的余数。数据计算 MONTH 求出指定日期或引用单元格中的日期的月份。日期计算 NOW 给出当前系统日期和时间。显示日期时间 OR 仅当所有参数值均为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。逻辑判断 RANK 返回某一数值在一列数值中的相对于其他数值的排位。数据排序 RIGHT 从一个文本字符串的最后一个字符开始，截取指定数目的字符。字符截取 SUBTOTAL 返回列表或数据库中的分类汇总。分类汇总 SUM 求出一组数值的和。数据计算 SUMIF 计算符合指定条件的单元格区域内的数值和。条件数据计算 TEXT 根据指定的数值格式将相应的数字转换为文本形式数值文本转换 TODAY 给出系统日期显示日期 VALUE 将一个代表数值的文本型字符串转换为数值型。文本数值转换VLOOKUP 在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处

超牛Excel表格公式 excel公式计算

excel公式计算第 1 页共 1 页 Excel表格公式大全, 太强大了 1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女代表的是输入身份证号码的单元格。"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”1、求和： =SUM(K2:K56) ----对K2到K56这一区域进行求和； 2、平均数： =AVERAGE(K2:K56) ----对K2 K56这一区域求平均数； 3、排名： =RANK(K2，K$2:K$56) ----对55名学生的成绩进行排名； 4、等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评：=K2*0.3+M2*0.3+N2*0.4 ----假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩； 6、最高分： =MAX(K2:K56) ----求K2到K56区域（55名学生）的最高分； 7、最低分： =MIN(K2:K56) ----求K2到K56区域（55名学生）的最低分； 8、分数段人数统计：（1） =COUNTIF(K2:K56,"100") ----求K2到K56区域100分的人数；假设把结果存放于K57单元格；（2） =COUNTIF(K2:K56,">=95")－K57 ----求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格；（3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ----求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格；（4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ----求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；（5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60) ----求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格；（6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61) ----求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格；（7） =COUNTIF(K2:K56," 第 2 页共 2 页 9、优秀率： =SUM(K57:K60)/55*100 10、及格率： =SUM(K57:K62)/55*100 11、标准差： =STDEV(K2:K56) ----求K2到K56区域(55人)的成绩波动情况（数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化）； 12、条件求和： =SUMIF(B2:B56,"男"，K2:K56) ----假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和； 13、多条件求和：｛=SUM(IF(C3:C322="男",IF(G3:G322=1,1,0)))｝ ----假设C列（C3:C322区域）存放学生的性别，G列（G3:G322区域）存放学生所在班级代码（1、2、3、4、5），则此函数返回的结果表示求一班的男生人数；这是一个数组函数，输完后要按Ctrl＋Shift＋Enter组合键(产生“｛……｝”)。“｛｝”不能手工输入，只能用组合键产生。 14、根据出生日期自动计算周岁：=TRUNC((DAYS360(D3,NOW( )))/360,0) ------假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁。 15、在Word中三个小窍门：①连续输入三个“~”可得一条波浪线。②连续输入三个“-”可得一条直线。连续输入三个“=”可得一条双直线。一、excel 中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如： A1〉1时，C1显示红色 0“条件格式”，条件1设为：公式 =A1=1 2、点“格式”->“字体”->“颜色”，点击红色后点“确定”。条件2设为：公式 =AND(A1>0,A1“字体”->“颜色”，点击绿色后点“确定”。条件3设为：公式 =A1“字体”->“颜色”，点击黄色后点“确定”。 4、三个条件设定好后，点“确定”即出。二、EXCEL中如何控制每列数据的长度并避免重复录入 1、用数据有效性定义数据长度。用鼠标选定你要输入的数据范围，点"数据"->"有效性"->"设置"，"有效性条件"设成"允许""文本长度""等于""5"（具体条件可根据你的需要改变）。还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点"确定"。 第 3 页共 3 页 2、用条件格式避免重复。选定A列，点"格式"->"条件格式"，将条件设成“公式=COUNTIF($A:$A,$A1)>1”，点"格式"->"字体"->"颜色"，选定红色后点两次"确定"。

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单元格。 =DA TEDIF(D2,TODAY(),"y")+1 注意：工龄两头算，所以加“1”。 如果精确到“天”—— =DA TEDIF(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") DA TEDIF函数，除Excel2000中在帮助文档有描述外，其他版本的Excel在帮助文档中都没有说明，并且在所有版本的函数向导中也都找不到此函数。但该函数在电子表格中确实存在，并且用来计算两个日期之间的天数、月数或年数很方便。微软称，提供此函数是为了与Lotus1-2-3兼容。 该函数的用法为“DA TEDIF(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岁，这样算出的年龄其实是最公平的。 本篇文章来源于：实例教程网(https://www.sodocs.net/doc/043809911.html,) 原文链接：https://www.sodocs.net/doc/043809911.html,/bgruanjian/excel/631.html

Excel中最精确的计算年龄的公式

Excel中最精确的计算年龄的公式( 网上搜到的共式大概有这么几种： 1、计算出生日期到某一指定日期（一般选用某年的最后一天入2006年12月31日）的的天数，然后除以360 ，得到一个数值，然后用 int()函数取整，得出需要的年龄。一般使用的公式如下： =IF(C12="","",INT(DAYS360(C12,"2006-12-31")/360)) 聪明一点的人知道使用这个公式， =IF(C12="","",INT(DAYS360(C12,TODAY())/360)) 这个方法，这个公式的弊端在于，一、将每个月默认为30天去计算两个日期之间的天数，二、将每年默认为360天去计算年龄。这种方法显然不精确。 2、年份直接相减 计算周岁 =YEAR(NOW())-YEAR(C12) 计算虚岁 =YEAR(NOW())-YEAR(C12)+1 这种算法的精确程度显而易见，粗略估算还算可以。 3、使用DATEDIF函数 这种方法与第一种方法采用了相同的思路，但是其的精确程度显然比第一种方法要高，这取决于DATEDIF函数本身的精确性。 =IF(C12="","",INT(DATEDIF(C12,"1983-3-20","D")/365)) 或者， =IF(C12="","",INT(DATEDIF(C12,now(),"D")/365)) 但是这种方法强行将一年固定为365天，我们知道通常情况每个四年就有一年是366天所以这种算法也不是很精确。 通过认真分析，我觉得只有结合我们计算年龄的实际方法，才能编制出准确无误的公式。首先分析人们计算年龄的方法，例如，笔者系1983年3月20日生人，如果要在2007年3月23日这天计算他的年龄，通常采用这样的方法。首先，人们会用2007减去1983得出的年龄为24岁，然后再看看他“满没满”24岁，就是看看出生的月份和日期比今天早还是晚，如果出生日期晚于今天则表示没有满，那么他的年龄就应该是2007-1983-1=23岁。如果出生日期早于今天或者就是今天，就说明他已经满了24岁或者正好满24岁，则他的年龄就是 2007-1983=24岁。分析清楚了计算年龄的过程我们再根据这个过程编写公式就很容易了。 综上，我编写了如下公式，在实际应用中将公式中所有的C12替换为，你的所使用的出生日期所在的表格行号列号组合即可。如（A1，B2等等） =IF(MONTH(NOW())MONTH(C12),YEAR(NOW())-YEAR(C12),IF(DAY(NOW())>=DAY(C12),YEAR(N OW())-YEAR(C12),YEAR(NOW())-YEAR(C12)-1))) 公式说明

Excel2020时间计算方法详解

Excel2020时间计算方法详解 一、Excel时间计算的基础 和日期一样，Excel处理时间的方式并不像我们看到的那样—— 时间是由小时、分钟和秒钟组成的，有特殊格式的数据。Excel在 内部把每24小时的时间周期作为一个从0至1的小数存储 说明：把A列中的时间数据复制到C列，然后通过“单元格格式”对话框，将C列的格式设置为“常规”即可看到效果。 从中可以看出，每24小时的周期都从午夜零点(0:00:00)开始，存储为0。正午(12:00:00)则存储为0.5，因为从午夜零点至正午恰 好是一个整天的一半。到下一个午夜零点时(0:00:00)，Excel把它 存储为1。从上一个午夜零点开始至下一个午夜零点开始，正好经 历了24小时，这就是我们所说的，Excel处理时间的一个24小时 周期。 将时间作为小数存储使得Excel可以对时间进行运算。例如,要 计算两个时间之间的间隔长度，执行结束时间对应的小数减去开始 时间对应的小数的操作即可，具体应用下面会有实例介绍。 二、使用公式快速输入时间 如果需要在工作表中输入很多没有规律的时间系列。假如只用输入A列的数字，然后用Excel公式自动转换成需要得到的C列的时 间格式，就可以大大提高输入的效率。 这个问题所需的公式，和处理快速输入日期的问题相似，下面先来看一下。选中C2单元格，输入公式： =TIME(LEFT(A2,2),MID(A2,3,2),RIGHT(A2,2)) 回车后，再选中C2单元格，用公式填充柄复制公式到恰当单元格，如C10单元格。

这个公式的其余部分，可以参照上一节的“快速输入无规律的日期系列”部分理解。后面的操作也完全类似，可以仿照进行。 三、计算出工作的小时数 在一些类似于考勤表的工作表中，常常需要计算工作的小时数，这也就是前面所提到的，两个时间间隔长度的问题。我们在下面的工作表中进行操作。 选中B5单元格，输入公式：=(B4-B3)*24 回车后，显示计算结果8.25，即张路路7月1日的工作时间为8.25小时。为什么要在(B4-B3)之后乘以24呢?前面讲过，Excel在内部将每24小时的时间周期作为一个从0至1的小数存储，所以(B4-B3)得到的只是这两个时间对应的小数差值，这个差值再乘以24就可以让计算结果的单位转换为小时。由此可见，理解了Excel 处理时间的规律，有助于理解公式的含义。 然后选中B5单元格，用公式填充柄复制公式到G5单元格即可。 四、计算出考试的分钟数 上面一个实例，计算两个时间的差值，我们希望计算结果的单位为小时。那么，假如我们希望计算结果的单位为分钟，该用什么公式呢?下面用计算考试的分钟数来说明。 工作表中，我们要计算出每科考试的时间，放到D4:D11单元格中，单位为分钟。 处理的方法有两种，包括不使用函数的方法和使用函数的方法。 1、不使用函数的方法 选中D2单元格，输入公式：=(C4-B4)*24*60 2、使用函数的方法 选中D2单元格，输入公式：=CONVERT((C4-B4)*24,"hr","mn") 回车后可以看到得到的计算结果仍为90。

Excel计算公式 excel公式计算

excel公式计算 Excel计算公式 1. Excel2003最大值，最小值，求和，平均值等 A1写“最大值”，A2公式=MAX(E3:E100) B1写“最小值”，B2公式=MIN(E3:E100) C1写“求和”，C2公式=SUM(E3:E100) D1写“平均值”，D2公式=AVERAGE(E3:E100) 2.在excel表格中不同日期显示不同颜色例如，想再每个月日期为奇数时显示红色，偶数时显示绿色，就是隔天一个颜色，格式----条件格式----公式：=MOD（A1，2）=1，设置单元格格式背景为红色----确定； 3. excel 下拉填充序号 01 02 03 04 05 =TEXT(ROW(A1),"00) 4. EXCEL隔行求和（如从O7单元格开始，到05000，后面每隔16行开始求和）=IF(MOD(ROW()-6,16),"",SUM(OFFSET($O$7,ROW()-22,,16,))) 5. ExcelIf函数有sheet1和sheet2两个页，其中有A，B，C列.A列是人名，B2是语文成绩,B3为数学成绩C为分数. 第一页sheet1已经是填好的表格。现在需要做的是：假如sheet2的A列的人名与sheet1的A列的人名相同，再把“语文成绩”或“数学成绩”输入，则把对应B列的成绩引入到sheet2的C列中来。 =VLOOKUP(A1,SHEET1!A1:C100,2,0) 6. EXCEL里设置时间相加（还是时间）例如在A单元格中输入10：30，在B单元格中输入0：30 - 1 - c输入公式=SUM(A1:B1)C列设置单元格格式为的时间格式或自定义格式h:mm 时间相加，不超过24小时，如果是25就会变成1：00 7. excel函数例如：A1是2011-02-02，在B1显示2012-02-01，如果A1是2011-02-01，就必须显示2012-01-31. B1中输=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1) 单元格A1是1*150*250，怎样在B1单元格中显示为1，B2为150，B3为250 数据----分列----分隔符号：*----确定！ 9.在EXCEL里一个格子里面输入DK123+000~002,拖动时000~002,两个数字可以分别以定值增加！="DK123+"&680+(row(a1)-1)*3&"~"&680+row(a1)*3 10. excel在一组数字前全部加一个符号比如一列数字12，23，54，54，65，,,,,我要在全部加个符号A，变成 A12,A23,A54,A54,A65,,,, 假设A列是12,23,34.....，需要在B列完成你所要的结果，那么在B列的第一行的单元格内键入：=CONCATENATE("A",A1) 中，一个百分数，在大于等于100%的情况下，得满分4分，小于50%得0分，但大于等于50%小于100%情况下=IF(A1>1,4,MAX(0,MIN((A1-0.5)*150,20))) 12. excel用函数提取某个符号前的字符串？比如A1=复合PVC-85/1.5，在B1用什么函数提取“/”之前的值，就是提取出“复合PVC-85”？=LEFT(A1,SEARCH("/",A1)-1) 13. EXCEL自动输出某一范围的随机数值,比如A1到E99,全部自己输出1.10-1.99的随机数 =TEXT(RAND()*(1.99-1.10)+1.10,"0.00")*1 14. EXCEL排序例如 326 我想要结果是236 ；353我想要结果是335 - 2 - =MIN(MID(TEXT(B5,"000"),{1,2,3},1)*1)&LARGE(MID(TEXT(B5,"000"),{1,2,3},1)*1,2)&M AX(MID(TEXT(B5,"000"),{1,2,3},1)*1) 15.用excel求和后等于11.5，总是12 一：把单元格拉大点看看是否有变化？二：看看是否设置了小数点位数为0，工具----选项----编辑----自动设置小数点数为0的勾取掉？三：是否你的公式使用了ROUND或者ROUNDUP函数。 16. Excel表格问题：假设表1有1000个数据，表2有5000个数据，怎样才能知道表1中的数据在表2中有没有出现，有哪些数据出现过？假设在A列，B列输入：=IF(COUNTIF(SHEET2!A:A,A1)>0,"有","") 然后对B列进行非空筛选！ 17. EXCEL设置在 A单元格输入30，在B单元格要变成0:30 =INT(A1/60)&":"&MOD(A1,60) 18. Excel判断比如一个表中，A为各分店商品名称，B为库存量，然后想做一个表，由各分店填好后，复制在一个计表中，一个公式就能统计出各种商品的库存总和？ =SUMIF($A:$A,"a*",$B:$B) 19. excel中有3列数字分别表示时分秒，合并到一列里？ =TEXT(A1&":"&B1&":"&C1,"H:MM:SS") 20. EXCEL里，比如：2个，26箱，50根，136台，去掉后面的字，只要数字？=LEFT(A1,LENB(A1)-LEN(A1) 21. excel 三列穿插变一列在EXCEL表中，如 1 2 3 4 5 6 7

exce1时间差计算

方法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"”，然后按下回车键，我们可以立即计算出结果。 一、计算工作时间、工龄 假如日期数据在D2xx。 =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和E1xx。 C1xx公式如下： =ROUND(B1-A1,0)

D1xx公式如下： =(B1-A1)*24 E1xx公式如下： =(B1-A1)*24*60 注意: A1和B1xx格式要设为日期, C1、D1和E1xx格式要设为常规. 三、计算生日， 假设b2为生日=datedif(B2,today(),"y") DATEDIF函数，除Excel2000中在帮助文档有描述外，其他版本的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日，今天是

Excel函数计算公式大全(完整)

Excel函数计算公式大全(完整) Excel函数计算公式大全(完整) 篇一 一、数字处理 1、取绝对值=ABS(数字) 2、取整=INT(数字) 3、四舍五入=ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式：C2=IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。 2、IF多条件判断返回值 公式：C2=IF(AND(A2 说明：两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2=COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。 2、统计不重复的总人数 公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现

次数变成分母，然后相加。 四、求和公式 1、隔列求和 公式：H3=SUMIF($A$2:$G$2,H$2,A3:G3) 或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 说明：如果标题行没有规则用第2个公式 2、单条件求和 公式：F2=SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法 3、单条件模糊求和 公式：详见下图 说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。 4、多条件模糊求和 公式：C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符* 5、多表相同位置求和 公式：b2=SUM(Sheet1:Sheet19!B2) 说明：在表中间删除或添加表后，公式结果会自动更新。 6、按日期和产品求和 公式：

关于excel计算月份的公式

关于excel计算月份的公式 单位员工的入职日期都是以2011-4-1格式，想计算截止当月的入职月份最佳答案 =DATEDIF(A2,TODAY(),"m") 如果是月份用这个公式就可以了，如果是天的话就把“m”改成“d”年就是“y” 公式意思是，A2到今天，是N个月。 DATEDIF函数[英] 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 日期中天数的差。忽略日期中的年。 实例1： 题目：计算出生日期为1973-4-1人的年龄 公式：=DATEDIF("1973-4-1",TODAY(),"Y") 结果：33

简要说明当单位代码为"Y"时,计算结果是两个日期间隔的年数. 实例2： 题目：计算日期为1973-4-1和当前日期的间隔月份数. 公式：=DATEDIF("1973-4-1",TODAY(),"M") 结果：403 简要说明当单位代码为"M"时,计算结果是两个日期间隔的月份数. 实例3： 题目：计算日期为1973-4-1和当前日期的间隔天数. 公式：=DATEDIF("1973-4-1",TODAY(),"D") 结果：12273 简要说明当单位代码为"D"时,计算结果是两个日期间隔的天数. 实例4： 题目：计算日期为1973-4-1和当前日期的不计年数的间隔天数. 公式：=DATEDIF("1973-4-1",TODAY(),"YD") 结果：220 简要说明当单位代码为"YD"时,计算结果是两个日期间隔的天数.忽略年数差 实例5： 题目：计算日期为1973-4-1和当前日期的不计月份和年份的间隔天数. 公式：=DATEDIF("1973-4-1",TODAY(),"MD") 结果：6 简要说明当单位代码为"MD"时,计算结果是两个日期间隔的天数.忽略年数和月份之差 5、实例6： 题目：计算日期为1973-4-1和当前日期的不计年份的间隔月份数. 公式：=DATEDIF("1973-4-1",TODAY(),"YM") 结果：7 简要说明当单位代码为"YM"时,计算结果是两个日期间隔的月份数.不计相差年数 注意：TODAY()函数获取的是系统当前日期，所有列举的实例为2006-11-7日的计算结果，并不一定和你得到的结果相符。

EXCEL关于时间的公式

5个时间问题8个时间函数全搞定 表格中常常会涉及到与日期有关的项目。通过一些日期相关函数的巧妙组合使用，可轻松满足日期计算中的常见需求。下面我们通过5个具体实例，带大家玩转日期相关函数。 用出生年月快速计算年龄 如图的表格第一列已经列出了“出生年月日”数据，“年龄”一列为空需要填写，不需要手动就可是快速完成。具体方法是在B2单元格中输入如下公式，然后用填充柄填充B列即可。 =DATEDIF(A2,TODAY(),"y")

解释：DATEDIF函数的作用是计算从开始日期到结束日期的时间（天数、月数或年数）。其中A2为开始日期，TODAY()为结束日期，"y"表示信息类型为年数（若要计算月数改为m，计算天数改为d即可）。 根据身份证号计算出生年月 下图的表格中已有身份证号码，出生年月日一列就没必要重新填写了，可自动生成，这是因为身份证号码中就包含了出生年月日的信息。只需在B2单元格输入如下公式并向下填充B 列即可： =TEXT(MID(A2,7,8),"0!/00!/00") 解释：上述函数从A2单元格的第7为开始截取8位数字，然后以日期格式"0!/00!/00"表示出来。

根据身份证号码提取性别 在身份证号码中虽然直接看不出性别，但其中的某些位数却暗含着性别信息，通过简单运算可得知男女性别。我们只需在B2单元格输入如下信息并向下填充该列即可： =IF(MOD(MID(A2,15,3),2),"男","女") 解释：上述函数从A2单元格的第15位开始截取3位数，然后做取模运算，若余数为0则为男，否则为女。 根据身份证号码计算实际年龄 还可以通过身份证号码计算出实际年龄。在B2中构造公式如下，然后依次填充B列单元格即可。 =YEAR(TODAY())-MID(A2,7,4) 解释：其中YEAR(TODAY())代表今年，MID(A1,7,4)取身份证中的年份，两个相减就是年龄。