两次之间的Excel IF AND公式 [英] Excel IF AND formula between two times

问题描述

我想有一个公式，该公式将告诉我一个单元格中的时间是否在其他单元格中两个独立的vlaues之间，如果是，则返回一个值.

I would like to have a formula which will tell me if a time in a cell is between 2 separate vlaues in other cells and to return a value if so.

我已经创建了以下代码，但这根本没有返回任何值.

I have already created the below code but this is not returning any values back at all.

=IF(AND(F4>=$R$1,F4<P1),"Night Shift",IF(AND(F4>=$P$1,F4<$Q$1),"AM Shift",IF(AND(F4>=$Q$1,F4<$R$1),"PM Shift","")))

在此示例中，单元格值为(P1 = 06:00，Q1 = 14:00，R1 = 22:00).F4中的值为00:31:38.

In this example the cell values are (P1 = 06:00, Q1 = 14:00, R1 = 22:00). The value in the F4 is 00:31:38.

任何帮助将不胜感激.

推荐答案

您的第一个 AND 需要稍作调整.

Your first AND needs to adjust a little.

Excel将TIME视为整整1天的一小部分.所以 00:31:38 尽管您的意思是第二天从 22:00 开始，但是Excel不知道这一点，因此它不会比大> 22:00

Excel sees TIME as a fraction of 1 whole day. So 00:31:38 though you meant it to be the next day from 22:00, Excel does not know that and as such will not see it greater than 22:00

我们也不需要测试夜班.如果时间不在其他时间中，则这是唯一剩下的选项:

We also do not need to test for the Night Shift. It is the only option left if the time is not in one of the others:

=IF(F4<>"",IF(AND(F4>=$P$1,F4<$Q$1),"AM Shift",IF(AND(F4>=$Q$1,F4<$R$1),"PM Shift","Night Shift")),"")

您还可以创建一个像这样的小表:

You could also create a small table like such:

0            6:00       14:00       22:00
Night Shift AM Shift    PM Shift    Night Shift

然后使用HLOOKUP返回正确的值:

Then use a HLOOKUP to return the correct value:

=HLOOKUP(F4,O1:R2,2,TRUE)

这篇关于两次之间的Excel IF AND公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！