Converting a number to a time period HELP

B

Bobsled

Thanks to this forum i discovered the LOOKUP function:) Now i need
help in taking a number (1-11) and for a given number convert it to a
time period.

Example 1 = 8:00-4:00, 2 = 8:30-5:00

I need to describe the whole range of periods 1-11. Does anybody have
a function solution:confused:

Thanks for the HELP:) :cool:
 
T

Trevor Shuttleworth

One way:

=IF(AND(A1>=1,A1<=11),VLOOKUP(A1,{1,"08:00-16:00";2,"08:30-16:30";3,"09:00-17:00";4,"t1-t2";5,"t1-t2";6,"t1-t2";7,"t1-t2";8,"t1-t2";9,"t1-t2";10,"t1-t2";11,"t1-t2"},2,FALSE),"error")

Regards

Trevor
 
E

Earl Kiosterud

Bobsled,

Since your numbers conveniently start at 1, and are consecutive, we can
cheat a little, and use:

=CHOOSE(A1,"08:00-16:00","8:30-17:00", ... )

But this isn't considered good programming practice. It's error-prone, and
if anything changes, someone has to wade into this formula and make changes.
It will be someone else, since you will have long been promoted for your
craftiness. Your legacy.

A general way is to make a table:

1 8:00-16:00
2 8:30-17:00
etc

Then use VLOOKUP:

=VLOOKUP(A1,Table,2,FALSE)

Now if the data changes, the table simply needs to be updated.
 
B

Bobsled

Thanks to the great folks on the forum I've got a good schedulin
program working for my wife. Trevor the formula worked like a champ!:
:)

Thank
 
T

Trevor Shuttleworth

You're welcome. Thanks for the feedback. I trust your wife will be pleased
with your efforts.
 
Top