Formula
 

I currently have a field that subtracts the difference between an arrival and
departure time. I have also created an additional 4 fields to see a time
frame for the time difference.

I placed the formulat =IF(W4<=15,1,0) in the 15 column, but it doesn't seem
to be working correctly, just placing a 1 in the columns, regardless if it is
over or under 15. I think it has to do with formatting it in proper time
format, but don't know how to do that. Can you help?

XL stores times as fractional days, so 15:00:00 = 0.625, and 00:15:00 =
0.0104166666666667.

I'm not sure what exactly you're trying to do, but if you're trying to
see if the difference is less than 15 minutes, try:

=IF(W4<="0:15:00",1,0)

or, equivalently

=--(W4<="0:15:00")

where the "--" coerces the TRUE/FALSE value to 1/0 respectively.

You could also use an arithmetic formula (using 1440 minutes in a day):

=--(W4<=15/1440)

or a time function:

=--(W4<=TIME(0,15,0))



In article ,
Metalteck wrote:

I currently have a field that subtracts the difference between an arrival and
departure time. I have also created an additional 4 fields to see a time
frame for the time difference.

I placed the formulat =IF(W4<=15,1,0) in the 15 column, but it doesn't seem
to be working correctly, just placing a 1 in the columns, regardless if it is
over or under 15. I think it has to do with formatting it in proper time
format, but don't know how to do that. Can you help?

reading this in line with your reply in the original thread .. try
=IF($W3<=(AY$1/1440),1,0) formula in the 15 column - fill down

in the 30 column
=IF(AND($W3<=(AZ$1/1440),$W3=(AY$1/1440)),1,0)
fill across to the 45 & 60 and then down

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Metalteck" wrote in message
...
I currently have a field that subtracts the difference between an arrival
and
departure time. I have also created an additional 4 fields to see a time
frame for the time difference.

I placed the formulat =IF(W4<=15,1,0) in the 15 column, but it doesn't
seem
to be working correctly, just placing a 1 in the columns, regardless if it
is
over or under 15. I think it has to do with formatting it in proper time
format, but don't know how to do that. Can you help?

I've already go the time calculated in column W. I'm just trying to get it to
reflect in the proper column. Right now, if w3 is :30
This is what I get.

w3....................ay.............az..........b a............bb
15 30 45 60
0:30..................0................1.......... .1..............1
0:10..................1................1.......... .1..............1

The formula I use in 15 is =--(W3<=15/1440)
In 30 is =--(W3<=30/1440) and so on...

Can you help me figure it out so that only 1 check mark is placed the time
instead of all of them.

Hi

what happens when you try the formulas i gave you in my previous post
---repost
=IF($W3<=(AY$1/1440),1,0) formula in the 15 column - fill down

in the 30 column
=IF(AND($W3<=(AZ$1/1440),$W3=(AY$1/1440)),1,0)
fill across to the 45 & 60 and then down
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Metalteck" wrote in message
...
I've already go the time calculated in column W. I'm just trying to get it
to
reflect in the proper column. Right now, if w3 is :30
This is what I get.

w3....................ay.............az..........b a............bb
15 30 45 60
0:30..................0................1.......... .1..............1
0:10..................1................1.......... .1..............1

The formula I use in 15 is =--(W3<=15/1440)
In 30 is =--(W3<=30/1440) and so on...

Can you help me figure it out so that only 1 check mark is placed the time
instead of all of them.

when I use the formulas you gave me
when the time differnce is 0:30, I get 0s in all 4 slots
In the fields that have no time in either start or end times, all columns
have 1's.

Hi

if you like, send me your workbook ... julied_ng at hcts dot net dot au
.... be warned however, it is nearly 2am here so i won't be up for much
longer :)
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Metalteck" wrote in message
...
when I use the formulas you gave me
when the time differnce is 0:30, I get 0s in all 4 slots
In the fields that have no time in either start or end times, all columns
have 1's.