我有3张excel
表1包含24小时格式的列到达时间
Arrival-Time
11:00
22:00
05:00
09:00
表2包含24小时格式的列出发时间
Departure-time
14:00
23:00
18:00
10:00
表3包含24小时格式的计划时间列
Planned-time
15:00
18:00
3:00 - 12:00
12:00至16:00
现在我想要一个公式，如果计划时间在到达时间和出发时间之间，那么将列交付更新为是否否 .
但不幸的是，我的计划时间栏包含时间范围（3：00-12：00）和固定时间（3:00） .
那么如何编写通用公式来更新DELIVERY列？因为我的计划时间列包含时间范围和固定时间 .
1 回答
我相信它可以以更整洁的方式完成（你可以将其中一些组合成更大的公式）但我已经得到了以下内容 .
没有经过详尽的测试，但我认为它可以满足需要 .
理论是找出你有什么样的输入，然后相应地处理它，我认为你有时钟类型和范围变量的方式 .
Therefore first two "helper" columns are Clock-type (B2):
=IF(RIGHT(A2,1)="M","12","24")then Range (C2):
`= NOT（ISERROR（FIND（ “ - ”，A2）））”
I then have four columns to calculate the start and end of ranges in both 12 hour and 24 hour clocks (sorry but these are long!), D2 is:
=IF(MOD(IF(RIGHT(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"),2)="AM",VALUE(LEFT(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"),LEN(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"))-2)),VALUE(LEFT(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"),LEN(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"))-2))+0.5)*2,1)=0,IF(RIGHT(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"),2)="AM",VALUE(LEFT(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"),LEN(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"))-2)),VALUE(LEFT(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"),LEN(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"))-2))+0.5)-0.5,IF(RIGHT(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"),2)="AM",VALUE(LEFT(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"),LEN(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"))-2)),VALUE(LEFT(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"),LEN(SUBSTITUTE(LEFT(A2,FIND("-",A2)-2),".",":"))-2))+0.5))E2 is:
=IF(MOD(IF(RIGHT(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"),2)="AM",VALUE(LEFT(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"),LEN(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"))-2)),VALUE(LEFT(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"),LEN(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"))-2))+0.5)*2,1)=0,IF(RIGHT(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"),2)="AM",VALUE(LEFT(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"),LEN(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"))-2)),VALUE(LEFT(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"),LEN(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"))-2))+0.5)-0.5,IF(RIGHT(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"),2)="AM",VALUE(LEFT(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"),LEN(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"))-2)),VALUE(LEFT(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"),LEN(SUBSTITUTE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2),".",":"))-2))+0.5))F2 is:
=VALUE(LEFT(A2,FIND("-",A2)-2))G2 is:
=VALUE(MID(A2,FIND("-",A2)+2,LEN(A2)-FIND("-",A2)+2))If you're still awake we then move onto working out if the types hit your criteria. H2 (24hrs and Not a Range) is:
=AND(A2>=Sheet1!A2,A2<=Sheet2!A2)I2 (12hrs and Not a Range) is:
=AND(IF(RIGHT(A2,2)="AM",VALUE(LEFT(A2,LEN(A2)-2)),VALUE(LEFT(A2,LEN(A2)-2))+12)>=Sheet1!A2,IF(RIGHT(A2,2)="AM",VALUE(LEFT(A2,LEN(A2)-2)),VALUE(LEFT(A2,LEN(A2)-2))+12)<=Sheet2!A2)J2 (24hrs and Is a Range) is:
=OR(AND(F2>=Sheet1!A2,F2<=Sheet2!A2),AND(G2>=Sheet1!A2,G2<=Sheet2!A2))K2 (12hrs and Is a Range) is:
=OR(AND(D2>=Sheet1!A2,D2<=Sheet2!A2),AND(E2>=Sheet1!A2,E2<=Sheet2!A2))Now we come to your Delivery column, I've gone with TRUE / FALSE rather than "Yes" / "No", this just uses the types and the results above in order to use the right one for the right scenario:
=IF(B2=24,IF(C2,J2,H2),IF(C2,K2,I2))As I've said, not been tested but hopefully if I have made mistakes (which is quite possible in all that) you should be able to take the logic and then make any adjustments needed.