This is probably gonna sound really lame...
Posted by Ian McLeod on November 08, 2001 11:56 AM
I've been having trouble all day getting two fields to perform a simple operation.
Originally, I had:
=IF((C22-C21)>8,(C22-C21)-8,0)
The function was to determine overtime hours, with C22 (hour they first arrived) being subtracted from C21 (hour they left), in military time format. Yet for some reason, although the field has no trouble performing the simple calculation, the moment I throw in the > sign, the resulting test number is some kind of ridiculously low fraction (0.5 something or other). So naturally, it's never true.
What am I doing wrong, and how do I fix it?
Posted by Barrie Davidson on November 08, 2001 12:06 PM
Ian, if you want the formula's value to be time, try using this instead:
=IF((C22-C21)*24>8,(C22-C21)-8/24,0)
Regards,
BarrieBarrie Davidson
Posted by Ian McLeod on November 08, 2001 12:20 PM
It's still doing weird things. :) I entered the same data in a nearby field, with a simple D22-D21 calculation. In a 23:00-09:00 calculation, it returns 14.0 (the all-important cell 23 is configured custom to "h.m" format). For some reason though, when I checked it through the function wizard, it returns FALSE on the calculation.
Posted by Barrie Davidson on November 08, 2001 12:27 PM
Not sure what the problem might be. I just tested the same formula using 23:00 in cell D22 and 09:00 in cell D21. The formula
=IF((D22-D21)*24>8,(D22-D21)-8/24,0)
returned 6:0 (the cell was formatted to h:m).
Try the above formula again and let me know the results. One question for you, does any of the shifts you are looking at span midnight (22:00 to 07:00 for example)? Or (the light bulb goes on) is that the example you are using in D22 and D21?
BarrieBarrie Davidson
Posted by Mark W. on November 08, 2001 12:54 PM
Use =MOD(MAX(8/24,C21-C22),8/24) and format as [h]:mm (nt)
Posted by iml on November 08, 2001 2:58 PM
night shift
Daniel,
How do I go about doing this? And does it make any difference if the files have the same macro but otherwise all have different data in them?
Thanks for your help,
Kevin Why don't you create an add-in which contains the various macros and forms you need. Then store this somewhere where everyone has access. You can then make changes just to the add-in without having to change every single file. HTH,
Posted by IML on November 08, 2001 3:08 PM
Ghost post
With full credit to my theivery from Mark, you could deal with overnighters with
=MOD(MAX(8/24,C21-C22*(C21 > C22),C21+1-C22),8/24)
Posted by IML on November 08, 2001 3:15 PM
Re: Ghost post
Better make that
=MOD(MAX(8/24,(C21-C22)*(C21 > C22),(C21+1-C22)*(C21 < C22)),8/24)
Posted by Mark W. on November 08, 2001 4:03 PM
Use Datetime values for overnighters -- not time values...
...then no formula change is needed. As before,
use =MOD(MAX(8/24,C21-C22),8/24) formatted as [h]:mm
Posted by IML on November 09, 2001 7:27 AM
Good point (nt)
