how do I write an IF/other statement to cut off time data?

[Daren]

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)?

 

[Dave F]

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

Dave

 

[Daren]

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]

=IF(OR([cell with time in it]*24>7.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

 

[Daren]

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.

=IF(OR([cell with time in it]*24>7.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.

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]

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

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.

=IF(OR([cell with time in it]*24>7.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.

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?

:

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.


:

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)?

 

[Daren]

It just says the formula contains an error then it takes me back u p to the
square parentheses.

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

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.

=IF(OR([cell with time in it]*24>7.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.


:

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?

:

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.


:

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)?

 

[Dave F]

The square brackets are an indication that you need to put the appropriate
cell reference there. Eliminate the square brackets and re run your formula.

Dave
--
Brevity is the soul of wit.

It just says the formula contains an error then it takes me back u p to the
square parentheses.

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

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.

:

=IF(OR([cell with time in it]*24>7.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.


:

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?

:

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.


:

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)?

 

[David Biddulph]

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)*24>7.5,MOD(A1,1)*24<=15.5),"Include","Exclude")
--
David Biddulph

=IF(OR([cell with time in it]*24>7.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.

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?

 

[Daren]

I took out the square brackets and ran the formula as you wrote it, but Excel
now says I've entered too many arguments. Suggestions please?

The square brackets are an indication that you need to put the appropriate
cell reference there. Eliminate the square brackets and re run your formula.

Dave
--
Brevity is the soul of wit.

It just says the formula contains an error then it takes me back u p to the
square parentheses.

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


:

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.

:

=IF(OR([cell with time in it]*24>7.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.


:

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?

:

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.


:

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)?

 

[Daren]

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?

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)*24>7.5,MOD(A1,1)*24<=15.5),"Include","Exclude")
--
David Biddulph

=IF(OR([cell with time in it]*24>7.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.

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?

:

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.


:

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)?

 

[romelsb]

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

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)*24>7.5,MOD(A1,1)*24<=15.5),"Include","Exclude")
--
David Biddulph

=IF(OR([cell with time in it]*24>7.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.

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?

:

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.


:

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)?

 

[David Biddulph]

Ah, you've just fallen victim to one of the oldest traps in computing. If
you look at the =MOD(A2,1)*24 term, with enough decimal places visible,
you'll see that it says 15.5000000000582, rather than 15.5. This is because
the original time is stored as a fraction of a day, and 15.5/24 is
0.6458333333333...
The latter number can neither be expressed exactly in decimals, nor in
binary. When you've got a binary approximation to that and multiply it back
by 24, it doesn't come quite back to the number you first thought of.
It is always dangerous looking for exact equality in a computer's
representation of a loating point number. Integers are OK, but for
non-integer you'll run into problems with anything other than 0.5, 0.25,
0.125, etc. and multiples thereof.
--
David Biddulph

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

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)*24>7.5,MOD(A1,1)*24<=15.5),"Include","Exclude")
--
David Biddulph

=IF(OR([cell with time in it]*24>7.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.


:

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?

:

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.


:

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)?

 

[romelsb]

excuse me David...am i the victim or Mr. Daren or the guy who responsibly
suggest the formula....Im trying to clarify not trying to be another annoyed
viewing victim...please reiterate....more power....
--
"Bright minds are blessed to those who share them.."-rsb.

Ah, you've just fallen victim to one of the oldest traps in computing. If
you look at the =MOD(A2,1)*24 term, with enough decimal places visible,
you'll see that it says 15.5000000000582, rather than 15.5. This is because
the original time is stored as a fraction of a day, and 15.5/24 is
0.6458333333333...
The latter number can neither be expressed exactly in decimals, nor in
binary. When you've got a binary approximation to that and multiply it back
by 24, it doesn't come quite back to the number you first thought of.
It is always dangerous looking for exact equality in a computer's
representation of a loating point number. Integers are OK, but for
non-integer you'll run into problems with anything other than 0.5, 0.25,
0.125, etc. and multiples thereof.
--
David Biddulph

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

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)*24>7.5,MOD(A1,1)*24<=15.5),"Include","Exclude")
--
David Biddulph

=IF(OR([cell with time in it]*24>7.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.


:

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?

:

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.


:

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)?

 

[romelsb]

maybe precisely
=IF(AND(round(MOD(A1,1)*24,2)>7.5,round(MOD(A1,1)*24,2)<=15.5),"Include","Exclude")...a modified integer version

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

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)*24>7.5,MOD(A1,1)*24<=15.5),"Include","Exclude")
--
David Biddulph

=IF(OR([cell with time in it]*24>7.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.


:

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?

:

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.


:

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)?

 

[Roger Govier]

I thought David explained rather well the problems with decimal
representations of binary numbers.

The conversion could be avoided if the formula used were
=IF(AND(A1>Time(7,30,0),A1<=TIME(15,30,0),"Include","Exclude")


--
Regards

Roger Govier

excuse me David...am i the victim or Mr. Daren or the guy who
responsibly
suggest the formula....Im trying to clarify not trying to be another
annoyed
viewing victim...please reiterate....more power....
--
"Bright minds are blessed to those who share them.."-rsb.

Ah, you've just fallen victim to one of the oldest traps in
computing. If
you look at the =MOD(A2,1)*24 term, with enough decimal places
visible,
you'll see that it says 15.5000000000582, rather than 15.5. This is
because
the original time is stored as a fraction of a day, and 15.5/24 is
0.6458333333333...
The latter number can neither be expressed exactly in decimals, nor
in
binary. When you've got a binary approximation to that and multiply
it back
by 24, it doesn't come quite back to the number you first thought of.
It is always dangerous looking for exact equality in a computer's
representation of a loating point number. Integers are OK, but for
non-integer you'll run into problems with anything other than 0.5,
0.25,
0.125, etc. and multiples thereof.
--
David Biddulph

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


:

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)*24>7.5,MOD(A1,1)*24<=15.5),"Include","Exclude")
--
David Biddulph

=IF(OR([cell with time in it]*24>7.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.


:

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?

:

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.


:

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)?