求excel函数。例如,在7:30-8:00打卡的,统一为8:00,在8:00-8:30打卡的,统一
答案:2 悬赏:60
解决时间 2021-02-21 16:14
- 提问者网友:爱了却不能说
- 2021-02-21 09:29
求excel函数。例如,在7:30-8:00打卡的,统一为8:00,在8:00-8:30打卡的,统一
最佳答案
- 二级知识专家网友:笑迎怀羞
- 2021-02-21 10:41
=IF(AND(A1-"7:30">=0,A1-"8:00"<=0),"8:00",IF(AND(A1-"8:00">=0,A1-"8:30"<=0),"8:30",""))
追问:为什么我加上8:30-9:00的时候,会显示错误呢,是不是同样加上以上函数,把区间更改下就可以了呢?还是这个函数不允许写超过三个呢?比如:7:30-8:00(8:00),8:01-8:30(8:30),8:31-9:00(9:00),9:01-9:30(9:30),以这样的规律一直写到12:30,以上函数是否管用?
追答:2003不能超过7层,可以用下面公式:
=LOOKUP(A1,--{"7:30","8:01","8:31","9:01","9:31","10:01","10:31","11:01","11:31","12:01"},{"8:00","8:30","9:00","9;30","10:00","10:30","11:00","11:30","12:00","12:31"})
追问:对了,谢谢你!
追问:为什么我加上8:30-9:00的时候,会显示错误呢,是不是同样加上以上函数,把区间更改下就可以了呢?还是这个函数不允许写超过三个呢?比如:7:30-8:00(8:00),8:01-8:30(8:30),8:31-9:00(9:00),9:01-9:30(9:30),以这样的规律一直写到12:30,以上函数是否管用?
追答:2003不能超过7层,可以用下面公式:
=LOOKUP(A1,--{"7:30","8:01","8:31","9:01","9:31","10:01","10:31","11:01","11:31","12:01"},{"8:00","8:30","9:00","9;30","10:00","10:30","11:00","11:30","12:00","12:31"})
追问:对了,谢谢你!
全部回答
- 1楼网友:思契十里
- 2021-02-21 10:53
=CEILING(A1,"0:30")
向下一个半点靠拢吗
向下一个半点靠拢吗
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯