Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |