• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup unique cell with date and time combined

bosco_yip

Excel Ninja
Another option,

1] Assume your holiday list in range D2:D11

2] In B2, formula copy down :

=IFERROR(IF((LEFT(TEXT(A2,"ddd"))="S")+ISNUMBER(MATCH(INT(A2),$D$2:$D$11,0)),LOOKUP(HOUR(A2),{0;7;22},{"Off peak";"Shoulder";"Off peak"}),LOOKUP(HOUR(A2),{0;7;14;20;22},{"Off peak";"Shoulder";"Peak";"Shoulder";"Off peak"})),"")

Regards
 
Top