ISNumber problem???

Posted by Tony on May 30, 2000 1:06 AM

I've got stuck on a formula.

I'm trying to indicate whether a series of times and days can produce one result.

I need to specify whether a time is off peak or peak (& W/E):

eg. 0800-1800 is peak, 1801-0759 is off peak. (Mon- Fri).

The problem arises when days are involed.

Fri 0001 - Sun 0000 is Weekend (W/E).

Is there a formula that is intelligent enough to do this or is this a Macro application.

I've been fiddling with "=IF(ISNUMBER.." but can't do above.

Help?

Tony

Posted by JAF on May 30, 2000 5:13 AM

Tony

Assuming you have one cell containing both date and time, the following should give you what you need:
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),"Weekend",IF(AND(HOUR(A1)>=8,HOUR(A1)&LT;=18),"Peak","Off Peak"))

If you have the date and time in seperate columns, you'll need to adjust the cell reference for the WEEKDAY and HOUR parts of the formula accordingly.

HTH - JAF