Lookup unique cell with date and time combined

[Ecel Dumbo]

Hi Friends,

I have attached a spread sheet. I would need to lookup a cell with unique value of date and time combined and then labelling them accordingly.

Any help will be appreciated.

 

[Ramesh]

Please check the attached file and see if this helps...

 

[Ecel Dumbo]

Thank you Ramesh, it worked well

 

[Ecel Dumbo]

HI Ramesh, Sorry after going through the file in detail, There seems to be some issue with the formula. COuld you please check, as I marked the error in red and given desired answers next to it. Could you please check

 

[bosco_yip]

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

 

[Ecel Dumbo]

Thank you Bosco-YIP.....working well now