I've tried various methods and formulas but can't seem to get this right, mostly due to peak hours being on two different time slots.
My provider charges different rates based on time and day.
Peak Weekday Hours: 07:00-11:00, 17:00-21:00 >>>> (all other times are off-peak)
Off-Peak Hours (Weekday): 00:00-10:59, 11:01-17:00, 21:01-11:59
Weekends: Off-Peak
Please help me with a formula that checks if a date falls on a weekday and time is within peak hours, then return 1/true/P, otherwise, leave 0/false/OP
Closest formula I've come up with:
=IF(OR(I2<$M$2,I2>=$M$3),"P",IF(AND(I2>=$M$3,I2<$M$4),"AM",IF(AND(I2>=$M$5,I2<$M$6),"OP","")))
=IF(AND(WEEKDAY(H2,2)<=5,WEEKDAY(H2,2)>=1,H2-INT(H2)>=0.25,H2-INT(H2)<=0.875),"P","OP")
Bookmarks