EXCEL如何用函数判断二个时间的大小？

wolf

当我的上班时间小于7：00，返回7：00，大于7：00小于7：30返回7：30，大于7：30小于8：00返回8:00,该怎么用函数来表示呢，我用IF函数试过了，得到的值一直都不对，就连比较大小上都会出现错误，在线等，感谢各位大神

原文地址：https://www.zhihu.com/question/457880699

队长是我

先看一下知友的问题：EXCEL如何用函数判断二个时间的大小？


首先我们要思考单元格里的时间是文本还是数值，其次要注意函数公式的写法，容易出错的知识点还是蛮多的。
正常情况下，单元格默认的格式是常规。


如果是你手动在单元格输入时间，如下图，这个时候单元格格式会自动变成自定义。


这种情况下时间是可以直接比较大小的，因为时间就是数字。当把单元格格式改成数值，我们就可以看到原来时间都有对应的数值，我们都知道数字是可以直接比较大小的。


知友说IF函数试过了，得到的值一直都不对。因为if函数是单条件判断函数，这里明显是多条件判断，要用ifs函数才行。另外还要看你的时间格式对不对，如果是文本格式，肯定不得行，因为文本是不能比较大小的。


接下来，在单元格输入ifs函数，发现出现了错误，这个时候就纳闷了。因为公式里的时间不能直接写“7:00”。


要把时间转换成数值的形式放到公式里，excel中任何时间转换成数字，都是在0-1这个范围，下图中的数字代表的是天，6:00:34转换成数值就是0.25天，6:23:34转换成数值就是0.27天。看知友的问题，可以知道条件是以30分钟为界限来进行判断，那么1天有多少个30分钟呢？1天24小时，48个30分钟。


所以把公式改成这样就可以进行判断了。公式里乘14是因为7:00是从00:00开始计算，到7:00一共有7个小时，14个30分钟。


这里还有第二种情况，就是我们刚才说的，如果时间是文本怎么办。因为一般从考勤系统导出的时间都是文本格式，这个时候是不能去比较大小，也不能单元格引用的。
出现这种时间是文本的情况，就要用到timevalue函数把文本形式转换成excel序列数，再进行判断。




其实大家也可以从单元格内容靠左还是靠右进行一个判断，文本内容在单元格中都是靠左的，数值格式、常规格式等单元格内容是靠右的。
这时候我们再更改单元格格式，数值就会变成时间，但这个时间就不是文本格式的时间了，是可以直接比较大小的时间。

继续前进

一、时间的本质

此问题还是要回归到时间的本质，它就是一个整数部分为零的小数。如果你输入或者得到时间的方法与格式没有问题，是可以直接比较大小的。
当你输入7:00时（注意，这里的冒号是英文状态下的标点符号），系统默认是自定义格式，也就是时间格式。


我们更改数据格式为常规，可以看到它实际上就是一个小数：


重点来啰，那么他是怎么得来的呢？实际上，时间就是以1天为整体1的一个分数值，而1天为24小时，所以6:28实际换算成小数则为：


=(6+28/60)/24上面公式中的6，是6小时，28是28分，因为时间是60进制，所以转化成小时要除以60，两者相加即是将时间转化成小时表示。/24，则是计算此时间在一天中占的比例。
至此，你明白时间与数值之间的转化了吗？
二、回答题主的问题

1、向半小时进位

题主的描述有可能产生歧义，就是依此类推的话，可以理解成不到半小时的向半小时进位。这时可以用ceiling函数来实现：


=CEILING(A1,0.5/24)2、if大法

有可能你把这个时间的比较想得简单了，比如：


这样是得不到正确结果的。
这样写才行：


当然，你开始得到的是一大堆的小数，用格式刷将B列的结果刷成与A列一样的格式即可。
=IF(A1<7/24,7/24,IF(A1<7.5/24,7.5/24,8/24))3、利用min函数



这里其实就是找大于目标时间点的最小时间
但上述公式对于大于8:00的单元格得不到正确结果，所以只好再加个判断：


再将格式转变成时间即可
其他方法还有，在此不一一列举，只是想说明，条条大路通罗马，但前提是你得知道时间的本质。
点赞+关注是个好习惯呀，如有其他疑问，请评论区留言

大力水手

无界限，向上取整CEILING
=CEILING(A2,1/24/2)
左界限，用MAX，
=MAX("7:00",CEILING(A2,1/24/2))
右界限，用MIN
=MIN("9:00",CEILING(A2,1/24/2))
左右界限，用MEDIAN
=MEDIAN("7:00",CEILING(A2,1/24/2),"9:00")

卡卡

1、用vlookup公式，首先建立一个4*2的数组，如下：

	A	B
1	0:00	7:00
2	7:00	7:30
3	7:30	8:00
4	8:00	23:59
5		=VLOOKUP(A5,A1:B4,2)

在A5单元格内填写变化时间，B5单元格内写公式=VLOOKUP(A5,A1:B4,2)，即可计算。需要将所有单元格格式设置为时间。
2、不用建立数组，直接在vlookup公式中设置数据集。
如果在A1单元格内填写变化时间，B1单元格内写公式=VLOOKUP(A1,{0,0.291666666666667;0.291666666666667,0.3125;0.3125,0.333333333333333},2)，即可计算。需要将所有单元格格式设置为时间。

清风寡欲

用if()函数与and()函数嵌套，用hour()与minute()函数分别提取小时与分钟，再用time()函数比较
按照上面的条件：
小于7：00，返回7：00，
大于7：00小于7：30返回7：30
大于7：30小于8：00返回8:00
如下图


1.在B2单元格输入公式：
=IF(TIME(HOUR(A2),MINUTE(A2),0)<=TIME(7,0,0),TIME(7,0,0),IF(TIME(HOUR(A2),MINUTE(A2),0)<=TIME(7,30,0),TIME(7,30,0),IF(TIME(HOUR(A2),MINUTE(A2),0)<=TIME(8,0,0),TIME(8,0,0),"其他")))
下拉即可完成
2.公式解析：
用hour()函数与minute()函数提取小时与分钟，因为提取的是数字，没法进行时间比较
所以用time()函数转化为时间，就像日期一样
都用time（）函数进行比较
条件一：小于7：00
条件二：大于7：00小于7：30
条件三：大于7：30小于8：00
所以要用if三层嵌套