Refer to Date/Time Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Molasses26 - 24 May 2007 00:34 GMT

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!!

Reply to this Message

Toppers - 24 May 2007 12:00 GMT

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"," ")

> 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
[quoted text clipped - 16 lines]
>
> Any suggestions will be greatly appreciated!!

Reply to this Message

Molasses26 - 12 Jun 2007 00:34 GMT

Perfect. This is exactly what I need!
Thanks!

> Does this help .....
>
[quoted text clipped - 20 lines]
> >
> > Any suggestions will be greatly appreciated!!

Reply to this Message

Erny - 24 May 2007 19:31 GMT

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

>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
[quoted text clipped - 18 lines]
>
> Any suggestions will be greatly appreciated!!

Reply to this Message

Molasses26 - 12 Jun 2007 00:38 GMT

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!

> One way could be to use "Conditional formatting" of the Date column with the
> following formula:
[quoted text clipped - 33 lines]
> >
> > Any suggestions will be greatly appreciated!!

Reply to this Message