However there is a requirement for a minimum weekly total of hours worked.
My difficulty lies in that I need to work in time units to get the hours
worked but when I deduct that total from the minimum and expect a minus
result (e.g. 18 hrs 30 min as the minimum requirement but only 10 hrs 45 min
worked should show 9 hrs 45 min owed, i.e. -9 hrs 45 min) the program will
not give me a minus answer in time units. It will if I convert the figures
to integers but I had hoped that i could avoid that step.
There are several possible solutions.
1. As Dave Peterson already mentioned, you can select to use the 1904 date
system: Tools/Options/Calculation/Workbook Options and Select 1904 date
system.
This will have the side affect of changing any dates already entered by adding
4 years (and 1 day) to them. And also, you would have to ensure that other
users opened the workbook with that option enabled.
2. Depending on how you are going to use this information, you could just
ignore the non-display (or use conditional formatting to "white it out"). The
stored value is still correct.
3. If you absolutely need to have it displayed, you could, for example, make
the result positive but use conditional formatting to make it bold/red or
something else that lets you know it is negative. You would then NOT be able
to use that cell result in dependent formulas, but would have to go back to the
source cells.
4. You could use conditional formatting to white out the negative number; then
display the result in an adjacent cell using the ABS function to make it
positive, and also using conditional formatting to make it bold/red. Then you
could use the original cell in dependent formulas.
There are also other possible solutions. Not sure what would be most
appropriate, but I would pick #1 first, if that doesn't mess up other users and
uses.
Failing that, I would use #4 and use conditional formatting to display the
negative result in an adjacent cell. The formatting for the base cell would be
to format the font as the background (e.g. white) if cell value < 0; and the
formatting for the display cell would be to set the font to bold/red if the
parent cell is <0; or to white if the target cell is >=0.
--ron