John Spencer wrote:
[Quoted Text] > Time comes in two flavors - elapsed duration (I worked 3 hours) and instant
> in time (It is 3 AM). Your calculation ends up giving you an instant in
> time (which is never negative).
>
> I would try using the DateDiff function to convert your Start time and End
> time into a duration and then subtract that to get number minutes over or
> under.
>
> DateDiff("n",LeaveOffice,EnterOffice) - 510 will give you the number of
> minutes over or under a duration of 8 hours and 30 minutes.
>
> If StandardTime is a date field and varies you can use
> DateDiff("n",StandardTime,0) to get the minutes
>
> DateDiff("n",LeaveOffice,EnterOffice) - DateDiff("n",StandardTime,0)
>
> To Format that nicely into hours and minutes and get a negative sign,
> requires a bit of arithmetic and formatting. Replace X with the formula of
> choice above
>
> IIF(X<0,"-","") & ABS(X\60) & Format(ABS(X Mod 60),"\:00")
Thanks guys,
I was looking for a simple solution. My trainees try to solve this in
Excel but in the "1900" (as opposed to 1904) Date mode, negative times
are presented as #####
In Windows Excel enter:
A1 = 7:55
A2 = 8:00
In A3 formula = A1-A2
Result = #####
Removing formatting however shows a negative number.
If you switch to the "1904" date system, you're all set.
I know how to cope with this but I was looking for a comprehensable
solution to show my trainees.
Yes, I used Date/Time fields.
Have solved this in FileMaker but as most people who attend my courses
use Microsoft Office, I was trying to find a substitute for Excel in Access.
Thanks again - Robert
|