Negative value for No. of hours possible???

[Guest]

Excess_Less_Hrs_Worked,Net_Hrs_Worked,Regular_Work _Hrs are date/time fields
.. I would like to do some thing like this -
Excess_Less_Hrs_Worked = Net_Hrs_Worked - Regular_Work_Hrs
and if Net_Hrs_Worked < Regular_Work_Hrs then I want to store a negative
value for Excess_Less_Hrs_Worked else a positve value say like -1:20 and 2:35
respectively
the above line of code is giving a positive value irrespective of whether
the Net_Hrs_Worked < Regular_Work_Hrs or not.
Should I otherwise take two fields for Excess Hours Worked and Less Hours
Worked but I wanted to do it taking a single field so that it looks decent
when generating reports- displaying negative numbers with red color without
'-' sign before the number and positive numbers with blue/black color.

 

[Guest]

There are a couple of problems here. First you should be using the DateDiff
function to add and subtract date/time values. Also, date/time values will
not return a negative number, but an earlier date/time. What you are wanting
is a quantity. All three of your fields should be Intergers and the values
should be store as minutes so you can do the math correctly and present it on
forms or reports in a time format.
To get the value of Regular_Work-Hrs or Net_Hrs_Worked you would need to use
a DateDiff function. Start and End Times should be date/time fields
Regular_Work-Hrs = DateDiff("n", StartTime, EndTime)
Now you will get negative numbers. For example if it were the -1:20 you
are trying to show, the actual value of Excess_Less_Hrs_Worked would be -80.
So if you want to display it as -1:20, here is a formula that will do that:

Format(Excess_Less_Hrs_Worked \ 60,"#0:") & _
Format(Abs(Excess_Less_Hrs_Worked Mod 60), "00")

Here is how this works. the \ operator returns the intger division of
Excess_Less_Hrs_Worked divided by the number of minutes in an hour.
Remember, the -80 is in minutes. the #0: formatting will allow for hours > 9
to show both digits, but < 10 will show only the single digis, and 0 will
display when there is less than an hour. the : is for display. The Mod
operator returns remainder of the division, which in this case is 20. two
digits will always display.