I have data for start and end times on specific dates. The data in start
date/time is formatted as 10/01/2006 7:30, and in end date/time as 10/01/2006
15:30. When importing the data from another application into Excel, I only
want data returned from 7:30 to 15:30, but the application isn't doing that.
For example, it's pulling data such as 10/01/2006 16:00 for start time and
10/01/2006 18:00 for end time. Is there an IF statement (or other statement)
I can write in these cells to include only cases that start at and between
7:30 AM and end at and between 15:30 (3:30 PM)?

Just select the date/time combinations, right click, format cells and format
as time. The date will be stripped out.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

I have data for start and end times on specific dates. The data in start
date/time is formatted as 10/01/2006 7:30, and in end date/time as 10/01/2006
15:30. When importing the data from another application into Excel, I only
want data returned from 7:30 to 15:30, but the application isn't doing that.
For example, it's pulling data such as 10/01/2006 16:00 for start time and
10/01/2006 18:00 for end time. Is there an IF statement (or other statement)
I can write in these cells to include only cases that start at and between
7:30 AM and end at and between 15:30 (3:30 PM)?

Ok, I've done that and it works. Thanks! Now how do I write the IF
statement to exclude times before 7:30 and ending after 15:30?

"Dave F" wrote:

Just select the date/time combinations, right click, format cells and format
as time. The date will be stripped out.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

I have data for start and end times on specific dates. The data in start
date/time is formatted as 10/01/2006 7:30, and in end date/time as 10/01/2006
15:30. When importing the data from another application into Excel, I only
want data returned from 7:30 to 15:30, but the application isn't doing that.
For example, it's pulling data such as 10/01/2006 16:00 for start time and
10/01/2006 18:00 for end time. Is there an IF statement (or other statement)
I can write in these cells to include only cases that start at and between
7:30 AM and end at and between 15:30 (3:30 PM)?

=IF(OR([cell with time in it]*247.5),([cell with time in
it]*24<=15.5),"Include","Exclude")

XL stores time as fractions of a 24 hour day. 7.5 is the numerical
equivalent of 7:30 AM (i.e., 7.5 hours of the 24 hour day have elapsed).
15.5 is the numerical equivalent of 3:30 PM.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

Ok, I've done that and it works. Thanks! Now how do I write the IF
statement to exclude times before 7:30 and ending after 15:30?

"Dave F" wrote:

Just select the date/time combinations, right click, format cells and format
as time. The date will be stripped out.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

I have data for start and end times on specific dates. The data in start
date/time is formatted as 10/01/2006 7:30, and in end date/time as 10/01/2006
15:30. When importing the data from another application into Excel, I only
want data returned from 7:30 to 15:30, but the application isn't doing that.
For example, it's pulling data such as 10/01/2006 16:00 for start time and
10/01/2006 18:00 for end time. Is there an IF statement (or other statement)
I can write in these cells to include only cases that start at and between
7:30 AM and end at and between 15:30 (3:30 PM)?

I tried your formula, but it says that there's an error. I'm going to try to
fix it, but please continue to assist. Thanks.

"Dave F" wrote:

=IF(OR([cell with time in it]*247.5),([cell with time in
it]*24<=15.5),"Include","Exclude")

XL stores time as fractions of a 24 hour day. 7.5 is the numerical
equivalent of 7:30 AM (i.e., 7.5 hours of the 24 hour day have elapsed).
15.5 is the numerical equivalent of 3:30 PM.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

Ok, I've done that and it works. Thanks! Now how do I write the IF
statement to exclude times before 7:30 and ending after 15:30?

"Dave F" wrote:

Just select the date/time combinations, right click, format cells and format
as time. The date will be stripped out.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

I have data for start and end times on specific dates. The data in start
date/time is formatted as 10/01/2006 7:30, and in end date/time as 10/01/2006
15:30. When importing the data from another application into Excel, I only
want data returned from 7:30 to 15:30, but the application isn't doing that.
For example, it's pulling data such as 10/01/2006 16:00 for start time and
10/01/2006 18:00 for end time. Is there an IF statement (or other statement)
I can write in these cells to include only cases that start at and between
7:30 AM and end at and between 15:30 (3:30 PM)?

What's the error?
--
Brevity is the soul of wit.


"Daren" wrote:

I tried your formula, but it says that there's an error. I'm going to try to
fix it, but please continue to assist. Thanks.

"Dave F" wrote:

=IF(OR([cell with time in it]*247.5),([cell with time in
it]*24<=15.5),"Include","Exclude")

XL stores time as fractions of a 24 hour day. 7.5 is the numerical
equivalent of 7:30 AM (i.e., 7.5 hours of the 24 hour day have elapsed).
15.5 is the numerical equivalent of 3:30 PM.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

Ok, I've done that and it works. Thanks! Now how do I write the IF
statement to exclude times before 7:30 and ending after 15:30?

"Dave F" wrote:

Just select the date/time combinations, right click, format cells and format
as time. The date will be stripped out.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

I have data for start and end times on specific dates. The data in start
date/time is formatted as 10/01/2006 7:30, and in end date/time as 10/01/2006
15:30. When importing the data from another application into Excel, I only
want data returned from 7:30 to 15:30, but the application isn't doing that.
For example, it's pulling data such as 10/01/2006 16:00 for start time and
10/01/2006 18:00 for end time. Is there an IF statement (or other statement)
I can write in these cells to include only cases that start at and between
7:30 AM and end at and between 15:30 (3:30 PM)?

There are a number of reasons why that won't work:

1 If you want to include only times between 7:30 and 15:30 you need AND,
not OR
2 Your OR function has opened the parentheses for this list of arguments,
but closes them after the first argument. You need to delete the
parentheses either side of that next comma.
3 The formatting to time doesn't change the value, so the date part is
still in there.

Try
=IF(AND(MOD(A1,1)*247.5,MOD(A1,1)*24<=15.5),"Incl ude","Exclude")
--
David Biddulph

"Dave F" wrote in message
...
=IF(OR([cell with time in it]*247.5),([cell with time in
it]*24<=15.5),"Include","Exclude")

XL stores time as fractions of a 24 hour day. 7.5 is the numerical
equivalent of 7:30 AM (i.e., 7.5 hours of the 24 hour day have elapsed).
15.5 is the numerical equivalent of 3:30 PM.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

Ok, I've done that and it works. Thanks! Now how do I write the IF
statement to exclude times before 7:30 and ending after 15:30?

"Dave F" wrote:

Just select the date/time combinations, right click, format cells and
format
as time. The date will be stripped out.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

I have data for start and end times on specific dates. The data in
start
date/time is formatted as 10/01/2006 7:30, and in end date/time as
10/01/2006
15:30. When importing the data from another application into Excel,
I only
want data returned from 7:30 to 15:30, but the application isn't
doing that.
For example, it's pulling data such as 10/01/2006 16:00 for start
time and
10/01/2006 18:00 for end time. Is there an IF statement (or other
statement)
I can write in these cells to include only cases that start at and
between
7:30 AM and end at and between 15:30 (3:30 PM)?

Thanks, that worked. Now I need to subtract out the times to only account
for times between 7:30 and 15:30. How do I do that?

"David Biddulph" wrote:

There are a number of reasons why that won't work:

1 If you want to include only times between 7:30 and 15:30 you need AND,
not OR
2 Your OR function has opened the parentheses for this list of arguments,
but closes them after the first argument. You need to delete the
parentheses either side of that next comma.
3 The formatting to time doesn't change the value, so the date part is
still in there.

Try
=IF(AND(MOD(A1,1)*247.5,MOD(A1,1)*24<=15.5),"Incl ude","Exclude")
--
David Biddulph

"Dave F" wrote in message
...
=IF(OR([cell with time in it]*247.5),([cell with time in
it]*24<=15.5),"Include","Exclude")

XL stores time as fractions of a 24 hour day. 7.5 is the numerical
equivalent of 7:30 AM (i.e., 7.5 hours of the 24 hour day have elapsed).
15.5 is the numerical equivalent of 3:30 PM.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

Ok, I've done that and it works. Thanks! Now how do I write the IF
statement to exclude times before 7:30 and ending after 15:30?

"Dave F" wrote:

Just select the date/time combinations, right click, format cells and
format
as time. The date will be stripped out.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

I have data for start and end times on specific dates. The data in
start
date/time is formatted as 10/01/2006 7:30, and in end date/time as
10/01/2006
15:30. When importing the data from another application into Excel,
I only
want data returned from 7:30 to 15:30, but the application isn't
doing that.
For example, it's pulling data such as 10/01/2006 16:00 for start
time and
10/01/2006 18:00 for end time. Is there an IF statement (or other
statement)
I can write in these cells to include only cases that start at and
between
7:30 AM and end at and between 15:30 (3:30 PM)?

excuse me David....Will the <= beside the 15.5 work under an IF(AND logical
conditions...I tried a date with a time 15:30 and the result is "exclude" yet
15:30 = 15.5...pls clarify....thanks


"David Biddulph" wrote:

There are a number of reasons why that won't work:

1 If you want to include only times between 7:30 and 15:30 you need AND,
not OR
2 Your OR function has opened the parentheses for this list of arguments,
but closes them after the first argument. You need to delete the
parentheses either side of that next comma.
3 The formatting to time doesn't change the value, so the date part is
still in there.

Try
=IF(AND(MOD(A1,1)*247.5,MOD(A1,1)*24<=15.5),"Incl ude","Exclude")
--
David Biddulph

"Dave F" wrote in message
...
=IF(OR([cell with time in it]*247.5),([cell with time in
it]*24<=15.5),"Include","Exclude")

XL stores time as fractions of a 24 hour day. 7.5 is the numerical
equivalent of 7:30 AM (i.e., 7.5 hours of the 24 hour day have elapsed).
15.5 is the numerical equivalent of 3:30 PM.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

Ok, I've done that and it works. Thanks! Now how do I write the IF
statement to exclude times before 7:30 and ending after 15:30?

"Dave F" wrote:

Just select the date/time combinations, right click, format cells and
format
as time. The date will be stripped out.

Dave
--
Brevity is the soul of wit.


"Daren" wrote:

I have data for start and end times on specific dates. The data in
start
date/time is formatted as 10/01/2006 7:30, and in end date/time as
10/01/2006
15:30. When importing the data from another application into Excel,
I only
want data returned from 7:30 to 15:30, but the application isn't
doing that.
For example, it's pulling data such as 10/01/2006 16:00 for start
time and
10/01/2006 18:00 for end time. Is there an IF statement (or other
statement)
I can write in these cells to include only cases that start at and
between
7:30 AM and end at and between 15:30 (3:30 PM)?