Change from Hour to hour and minute

[tonym07]

Hello

Im new to excel and wondering would it be possible to change the hour formula to hour and minute?

example first column which has hour {0;7;22} can it be changed to {0:30;7:30:22:30} as in the energy market it always starts at 7:30 not 7 for example,



LOOKUP(HOUR(B2),{0;7;22},{"Off peak";"Shoulder";"Off peak"}),LOOKUP(HOUR(B2),{0;7;14;20;22},{"Off peak";"Shoulder";"Peak";"Shoulder";"Off peak"})),"")



Thank you

 

[Special-K99]

This formula generates an error.

You can't have

LOOOKUP(...),LOOKUP()

This appears to be part of a longer formula.
Please post the complete formula.

 

[tonym07]

This formula generates an error.

You can't have

LOOOKUP(...),LOOKUP()

This appears to be part of a longer formula.
Please post the complete formula.

Sorry yes it is a longer formula

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

 

[Special-K99]

Try

=IFERROR(IF((LEFT(TEXT(B2,"ddd"))="S")+ISNUMBER(MATCH(INT(B2),$D$2:$D$14,0)),LOOKUP(TEXT(B2,"hh:mm"),{"00:00","00:30","07:30","22:30"},{"Off peak";"Off peak";"Shoulder";"Off peak"}),LOOKUP(TEXT(B2,"hh:mm"),{"00:00","00:30","07:30","14:30","22:30"},{"Off peak";"Off peak";"Shoulder";"Peak";"Shoulder";"Off peak"})),"")

 

[tonym07]

Try

=IFERROR(IF((LEFT(TEXT(B2,"ddd"))="S")+ISNUMBER(MATCH(INT(B2),$D$2:$D$14,0)),LOOKUP(TEXT(B2,"hh:mm"),{"00:00","00:30","07:30","22:30"},{"Off peak";"Off peak";"Shoulder";"Off peak"}),LOOKUP(TEXT(B2,"hh:mm"),{"00:00","00:30","07:30","14:30","22:30"},{"Off peak";"Off peak";"Shoulder";"Peak";"Shoulder";"Off peak"})),"")

Thank you for your help

Just couple of thing 20:30 has peak when it should be Shoulder and from 22:30 has Shoulder when it should be off peak

thank you so much for your help

1/10/2019 20:30 Peak Shoulder
1/10/2019 21:00 Peak Shoulder
1/10/2019 21:30 Peak Shoulder
1/10/2019 22:00 Peak Shoulder
1/10/2019 22:30 Shoulder Off peak
1/10/2019 23:00 Shoulder Off peak
1/10/2019 23:30 Shoulder Off peak

 

[tonym07]

Hello Special-K99

I found the error

=IFERROR(IF((LEFT(TEXT(B2,"ddd"))="S")+ISNUMBER(MATCH(INT(B2),$D$2:$D$14,0)),LOOKUP(TEXT(B2,"hh:mm"),{"00:00","00:30","07:30","22:30"},{"Off peak";"Off peak";"Shoulder";"Off peak"}),LOOKUP(TEXT(B2,"hh:mm"),{"00:00","00:30","07:30","14:30","20:30","22:30"},{"Off peak";"Off peak";"Shoulder";"Peak";"Shoulder";"Off peak"})),"")

20:30 was missing so i added it and all is ok formula works now ,

thank you so much