Time converstion

  • Thread starter Thread starter Becky
  • Start date Start date
B

Becky

I need to be able to show if the differential is negative
or positive. In other words I need to show if we used all
the allocated hours, were under or were over. I
calculated the difference this way:

Difference: Format([hours]-([TimeOut]-[TimeIn]),"Short
Time")

That gives me 0:30, 0:45, etc. But if Hours is less than
[TimeOut]-[TimeIn] then I need to show that we were over
0:30.

Thanks,
Becky
 
Becky-

I can't think of any simple way to solve your problem short of using IIF.
If TimeOut and TimeIn and Hours contain time values only, you're really
working with time of day on December 30, 1899. If TimeOut minus TimeIn is
greater than Hours, then you'll get a negative value, which will translate
to some time just before midnight on December 29, 1899. (If the time is 30
minutes over, you should be seeing "11:30 PM" or similar with your current
expression.) So, you'll have to test and use the appropriate subtraction to
get what you want:

Difference: IIF([Hours] > ([TimeOut] - [TimeIn]),
Format([hours]-([TimeOut]-[TimeIn]),"Short Time"),
"-" & Format(([TimeOut]-[TimeIn])-[Hours], "Short Time"))

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top