• 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

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.
 

Attachments

  • To identify Peak Shoulder Off Peak.xlsx
    159.2 KB · Views: 10
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
 

Attachments

  • To identify Peak Shoulder Off Peak.xlsx
    211.1 KB · Views: 12
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
 
Back
Top