Refer to Date/Time Range

[Guest]

I have a column with a months worth of date/time entries that are in 15
minute increments and I want to create a formula that refers to a range that
is NOT a weekend day and is greater than 9:00 and less than 21:15.
This will identify a PEAK usage time-frame each day.

Sample Data:
4/2/2007 8:00
4/2/2007 8:15
4/2/2007 8:30
4/2/2007 8:45
4/2/2007 9:00
4/2/2007 9:15
4/2/2007 9:30
4/2/2007 9:45
4/2/2007 10:00

I've tried usinge the WEEKDAY() function in combo with the HOUR() and
MINUTE() functions but I just can't seem to come up with anything that works!

Any suggestions will be greatly appreciated!!

 

[Guest]

Does this help .....

=IF(AND(WEEKDAY(A1,2)<6,TIME(HOUR(A1),MINUTE(A1),0)>TIME(9,0,0),TIME(HOUR(A1),MINUTE(A1),0)<TIME(21,15,0)),"Peak usage"," ")

 

[Erny]

One way could be to use "Conditional formatting" of the Date column with the
following formula:

=(WEEKDAY(A1)>1)*(WEEKDAY(A1)<7)*(A1-INT(A1)>0,37)*(A1-INT(A1)<0,89)=1

and specify the background colour as ... when the condition is true (maybe
you will need to adjust the values to 0,38 and 0,88 if you don't wish to
include the limits.
Please note that I am using a French version and assume that the equivalent
for the French ENT(A1) should be in English version INT(A1), as it is meant
to use only the integer part...

Hope it helps,
Erny

 

[Guest]

Perfect. This is exactly what I need!
Thanks!

Does this help .....

=IF(AND(WEEKDAY(A1,2)<6,TIME(HOUR(A1),MINUTE(A1),0)>TIME(9,0,0),TIME(HOUR(A1),MINUTE(A1),0)<TIME(21,15,0)),"Peak usage"," ")

 

[Guest]

I am still working with this one. I get an error when I copy the formula
directly over to my spreadsheet but I'm still looking into it because I have
a similar application where it would be useful to do this as well.
Merci!