Converting a number to a time period HELP

[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

Thanks for the HELP

[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


"Bobsled" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> Thanks for the HELP
>
>
> --
> Bobsled
> ------------------------------------------------------------------------
> Bobsled's Profile:
> http://www.excelforum.com/member.php...o&userid=37303
> View this thread: http://www.excelforum.com/showthread...hreadid=571832
>

[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.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------
"Bobsled" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> Thanks for the HELP
>
>
> --
> Bobsled
> ------------------------------------------------------------------------
> Bobsled's Profile:
> http://www.excelforum.com/member.php...o&userid=37303
> View this thread: http://www.excelforum.com/showthread...hreadid=571832
>

[Bobsled]

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

Thanks

[Trevor Shuttleworth]

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


"Bobsled" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks to the great folks on the forum I've got a good scheduling
> program working for my wife. Trevor the formula worked like a champ!
>
>
> Thanks
>
>
> --
> Bobsled
> ------------------------------------------------------------------------
> Bobsled's Profile:
> http://www.excelforum.com/member.php...o&userid=37303
> View this thread: http://www.excelforum.com/showthread...hreadid=571832
>