比较时间在excel [英] compare time in excel
问题描述
Excel专家,需要您的帮助来计算检查列,详情如下。
列A |列B |列C |列D |检查
12/07/2012 23:59:06 | 2012/07/12 |星期四| 23:59:06 | ?
- | = TEXT(A2,yyyy / mm / dd)| = TEXT(A3, DDDD)| = TEXT(A3, HH:MM:SS)|
正如你所看到的,我在列B中提取日期,我在列中提取日期C,我有时间在第D列中提取。
如果满足以下条件,列检查应为true,否则为false:
- 如果日是星期六或星期天,那么应该是真的
- 如果日是星期一到星期五,时间是> 6:30 pm,时间< 07:30 am应该是真的。
这是我建立的公式似乎不起作用。 +((D2> TIME(18,0,0)) * AND(D2< TIME(7,0,0)))),TRUE,FALSE)
我几乎所有的事情都变得真实。任何帮助将不胜感激。
在excel中写一些类似 A OR B OR C
2010年,你可以写: OR(A,B,C)
。
在你的情况下是:
= OR(C2 =Saturday,C2 =Sunday,D2> TIME(18,0,0 ),D2< TIME(7,0,0))
我想你还需要使用时间(D),分钟(A2),第二(A2),(C) )
Excel experts, need your help to compute the check column, details below.
Column A | Column B | Column C | Column D | Check
12/07/2012 23:59:06 | 2012/07/12 | Thursday | 23:59:06 | ?
- | =TEXT(A2, "yyyy/mm/dd") | =TEXT(A3,"dddd")|=TEXT(A3,"HH:mm:ss")|
As you can see, I am extracting the date in column B, I am extracting the day in Column C, I have the time being extracted in Column D.
Column Check should be true if the below condition is met and false otherwise:
- If day is Saturday or Sunday then should be true
- If day is Monday to Friday and Time is > 6:30 pm and time < 07:30 am should be true.
This is the formula I have build which does not seem to work.
=IF(OR(C2="Saturday",C2="Sunday")+OR((D2>TIME(18,0,0)*AND(D2<TIME(7,0,0)))),TRUE,FALSE)
I get true for pretty much everything. Any help will be greatly appreciated.
To write something like A OR B OR C
, in excel 2010, you can simply write: OR(A, B, C)
.
In your case, it would be:
=OR(C2="Saturday",C2="Sunday",D2>TIME(18,0,0), D2<TIME(7,0,0))
I think you also need to use a proper time in column D instead of text:
=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
这篇关于比较时间在excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!