Sorry if this a repost

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I got a connection timeout and Im not sure if the post made it. If so please pardon..

I need help determining if a transaction falls during a certain time period
A1=transaction start dat
B2=transaction start tim
C1=transaction stop dat
D1=transaction stop tim

Transactions that start and end beteen 8 & 5 are peak. All other times and a select few holidays, that are contained in look up table, are off peak. What formula combination can I use in E1 to determine and dispaly peak/off peak
Thanks
Richard
 
Hi
if I understood you correctly try
=IF(AND(C1=A1,B2>=TIME(8,0,0),B2<=TIME(17,0,0),D1>=TIME(8,0,0),D1<=TIME
(17,0,0)),"Peak","no peak")

I made the assumption that start and stop time should be on the same
day to be a peak.
To add holidays try the following: assumption the holiday dates are
stored in F1:F100
=IF(AND(C1=A1,COUNTIF(F1:F100,A1)=0,B2>=TIME(8,0,0),B2<=TIME(17,0,0),D1
=TIME(8,0,0),D1<=TIME(17,0,0)),"Peak","no peak")
Click to expand...
 
Actually....it could cross days----If any of the transaction falls within the peak time, then it is a peak transaction. But with that exception your formula is doing what I want. What would the modification be
Thanks!
 
Hi
try the following
=IF(AND(COUNTIF(F1:F100,A1)=0,B2>=TIME(8,0,0),B2<=TIME(17,0,0),D1=TIME(
8,0,0),D1<=TIME(17,0,0)),"Peak","no peak")

not sure what should happen if A1 does not fall on a holiday but C1
does?. This is not covered by the above formula
 
Back
Top