Currently Being ModeratedAug 11, 2012 8:08 AM (in response to KPRTX)
You could use this form for the test to see if two values are within a fixed difference in either direction:
=IF(ABS(G-H))>0.8, "Check", (H-G)*24)
Currently Being ModeratedAug 11, 2012 8:41 AM (in response to Jerrold Green1)
An extra right parenthesis slipped in there. Should be:
=IF(ABS(G-H)>0.8, "Check", (H-G)*24)
Currently Being ModeratedAug 11, 2012 10:28 AM (in response to Jerrold Green1)
Thanks, that is much simpler than my version... I don't think I undersatnd how the absolute function works, or maybe I don't understand the absolute number concept. It clearly is a simpler function.
Currently Being ModeratedAug 11, 2012 10:44 AM (in response to KPRTX)
Thanks for the feedback. The ABS (absolute) function is a simple impementation of the math operator that strips the sign from the value. All results are positive.
Currently Being ModeratedAug 11, 2012 11:03 AM (in response to Jerrold Green1)
One more dumb question that I should be able to figure out on my own but I can't get it. I've decided its difficult to work with the response 'check' so I'm trying to change it to the actual returned number (H-G)*24 but have it return in Red or italics. Any ideas? THanks!
=IF(ABS(G-H)>0.8, "Check", (H-G)*24)
Currently Being ModeratedAug 11, 2012 11:40 AM (in response to KPRTX)
You can use Conditional Format to do that. First you must rework your expression to add a flag to indicate the condition you want to act on. One pretty good way is to add a space character to the beginning or end of a string, and detect that in conditional format.
Here's an example:
=IF(ABS(G-H)>0.8, " "&(H-G)*24, (H-G)*24)
In Conditional Format, test for "Text Starts With" and give it a "space" in the comparison box.
I can't check that for you on my version because I'll get a value type mismatch error.
Currently Being ModeratedAug 12, 2012 5:03 PM (in response to Jerrold Green1)
I can confirm that Jerry's formula works in Numbers '08.
Moving on to the second part of your question:
"As of yet I haven't thought of way to auto correct the time zone error, but this will bring it to my attention so I can manually check it. If it only erred in one direction in a standard numerical value I could add it, but it varries -1 to +2. Farther left it has a city designation (not in the photo) that I can use to figure out the error. Any easy idea's on making an auto correct function would be cool too, just remember I'm not so good at this so take it easy on me! "
Auto-correcting the times to a common time zone might provide an alternate route to your end result.
Numbers will need the time zone information to make the necessary correction. I'm working on a couple of assumptions, based on your descriptions above, what time zones these are and how your table is set up.
1. All times in column G are in the same time zone (for my example, the Central zone)
2 Times in column H may be in any of four time zones, Eastern, Central, Mountain or Pacific.
3 You want to convert the times in H to the same 'instant' in the Central zone.
'Local' times are in column H, those times converted to CST (or CDT) are in column I. Formula shown above the table, and in a version without the use of column headers as cell references, below:
=H+(LOOKUP(D,Table 2::A,Table 2::B)/24)
Table 1 is your main table. Table 2 is the two column table to the right, listing the time offset needed to for each time zone.
A larger lookup table could be used to lookup the time zone for each city, then use that result to lookup the offset for that zone.
Currently Being ModeratedAug 13, 2012 11:24 AM (in response to Barry)
Thanks Barry, I'll give it a shot and see if I can get it to work!
Currently Being ModeratedAug 13, 2012 1:59 PM (in response to Barry)
Thanks for the idea Barry. Assumption 1 was incorrect however. After review one of MY assumptions was also incorrect that it ranged from -1 to +2 (off the standard MST). After reviews the numbers its actually +/-2 time zones (technically 3, but it doen't ever happen), and it can start in any time zone and finish in anytime zone. And actually it doesn't have to be corrected to a time zone, we are just computing the total time passed, so in reality it doesn't matter which time zone, as long as they are changed to the same time zone. So I came up with a long and arduous idea to change it based again on J, which can be explained as something like a smart average. Instead of dealing with the time zone, I only deal with the difference between the average and actual. Then I can define how many zones off it is and correct it. Basically testing the four options (+1,+2,-1,-2) and adjusting as appropriate.
One more error I haven't mentioned yet is also happening, although very rare. If the time clock goes from PM to AM, Numbers cant calculate it properly and comes up with a large negative number (like -22). This problem happens 1 in 300, so I plan on just dealing with it manually, so that explains the wierd check at the very end of the formual.
I feel pretty good at my formula, unfortunately I can't get it to work. I think its a syntax error, but I'd love to have someone else have a look at it and see if they can figure it out.
I'll post the theory first and then the actual after it.
IF(TIME < CREDIT - 1.8, TIME-2,IF(TIME < CREDIT -.8, TIME-1, IF(TIME > CREDIT + 1.8, TIME +2, IF(TIME > CREDIT +.8, CREDIT +1,IF(ABS(TIME)>02.8, "CHECK "&TIME, TIME)))))
TIME= Time calculation between H and G
Credit= J or the average Time expect for that section.
=IF(((H2-G2)*24)<(J2-1.8), ROUND(((H2-G2)*24)-2),2), IF(((H2-G2)*24)<(J2-0.8), ROUND((H2-G2)*24)-1,2), IF(((H2-G2)*24)>(J2+1.8),ROUND(((H2-G2)*24)+2,2), IF(((H2-G2)*24)>(J2+0.8),((H2-G2)*24)+1,IF(ABS((H2-G2)*24)>2.8 “CHECK “&ROUND((H2-G2)*24,1),ROUND(H2-G2)*24,2)))))
Again, thanks Barry, and Jerry for all the help. You guys have been wonderful.
Currently Being ModeratedAug 14, 2012 4:17 AM (in response to KPRTX)
Your problem when going from PM to AM is that the day has rolled over, or should have. In iWork, all times include a date as well. This can cause some problems in some cases and can solve a problem in others. If you enter just a Time, the current Date is appended, behind the scenes. Had you entered the entire Date and Time for your data point, you wouldn't have had the negative result, but that's rather inconvenient.
A handy work-around is to use this expression for elapsed time or time difference:
=time2 - time1 + if(time1 > time2, 1, 0)
The result of that expression is that 1 day is added (24 hours) if the day should have rolled over and didn't.
Currently Being ModeratedAug 14, 2012 3:19 PM (in response to Jerrold Green1)
Thanks Jerry, that is such an easy fix!
If you have a moment, I posted my big time zone correction equation in a new thread: https://discussions.apple.com/message/19270700#19270700
I'm feel pretty good about it, but I can't get it to work. I'd love to get some feedback if you have time. It doesn't have your new, over the date line correction, but I'll add it in to mine. Thanks!