Creating Peak and Off Peak Timebands

I have written a formula to split peak from off peak time, peak is only Monday to Friday, the result is only returning data for off peak, please see formula below;

  If ( (time({STAT_RM_TRANSACTIONS.TRIPDATETIME})>=timevalue(03,00,00) AND time({STAT_RM_TRANSACTIONS.TRIPDATETIME})<=timevalue(09,29,59))
          AND  (
          (time({STAT_RM_TRANSACTIONS.TRIPDATETIME})>=timevalue(15,30,00) AND time({STAT_RM_TRANSACTIONS.TRIPDATETIME})<=timevalue(17,59,59))
               )  AND      
          datepart("w",{STAT_RM_TRANSACTIONS.TRIPDATETIME}) IN [2,3,4,5,6] //weekdays
 
         ) THEN "Peak"

ELSE "Off Peak"
judy pritzAsked:
Who is Participating?
 
David Johnson, CD, MVPConnect With a Mentor OwnerCommented:
Change your 2nd AND to OR
 you have
if (A >=3 AND A<=9)
AND
A >= 15 AND A<=17)

if A = 15
you get False AND True which means FALSE
 
 
If ( (time({STAT_RM_TRANSACTIONS.TRIPDATETIME})>=timevalue(03,00,00) AND time({STAT_RM_TRANSACTIONS.TRIPDATETIME})<=timevalue(09,29,59))
          OR  (
          (time({STAT_RM_TRANSACTIONS.TRIPDATETIME})>=timevalue(15,30,00) AND time({STAT_RM_TRANSACTIONS.TRIPDATETIME})<=timevalue(17,59,59))
               )  AND      
          datepart("w",{STAT_RM_TRANSACTIONS.TRIPDATETIME}) IN [2,3,4,5,6] //weekdays 
 

Open in new window

0
 
judy pritzAuthor Commented:
Thank you, I knew it was something simple, sometimes you just need a fresh pair of eyes.
0
 
David Johnson, CD, MVPOwnerCommented:
Logic problems are always the hardest to find. Have a great day.
0
 
David Johnson, CD, MVPOwnerCommented:
logic error fixed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.