ExcelBanter - Refer to Date/Time Range

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Refer to Date/Time Range (https://www.excelbanter.com/excel-worksheet-functions/143921-refer-date-time-range.html)

Refer to Date/Time Range

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

=IF(AND(WEEKDAY(A3,2)<6,HOUR(A3)=9),TRUE,FALSE)
Add this in a column on row 3 - then drag down

Quote:

Originally Posted by Molasses26 (Post 499274)

Refer to Date/Time Range

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

"Molasses26" wrote:

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

Refer to Date/Time Range

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

"Molasses26" schrieb im Newsbeitrag
...
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!!

Refer to Date/Time Range

Perfect. This is exactly what I need!
Thanks!

"Toppers" wrote:

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

"Molasses26" wrote:

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

Refer to Date/Time Range

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!

"Erny" wrote:

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

"Molasses26" schrieb im Newsbeitrag
...
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!!