formatting negative numbers

Maybe try this?

=IF(C5="Y",G5,MAX(0,G5-D5))

Attach the workbook if you want any further help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks for the rep comment.

Again, without the workbook, not easy to say. Is that 9 hours or 9 minutes?

Try this:

=IF(C5="Y",text(G5,"h"),MAX(0,text(G5-D5),"h"))

or:

=IF(C5="Y",text(G5,"m"),MAX(0,text(G5-D5),"m"))

Obviously untested ...

Follow the instructions in the yellow banner at the top of the page.

OK - where am I looking? Where (which cell) will I find the formula?

I changed the formatting of K5, K8, etc. to simply h.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Please provide a sample workbook that SHOWS the PROBLEM and tell us anything else you have already tried to save time all around.

hi i tried that previously however when performing its next function it is incorrect. for example if it returns 9 as apposed to 09:00 because its formatted as h. (if i then did k5 x 3 it wouldnt return 27 it would return 3

I think you are falling prey to one of the challenges of working in a spreadsheet -- understanding the difference between "what you see in the cell (the display)" and "the actual cell's value (what Excel sees in the cell)". Number formatting is a powerful and useful thing in spreadsheets, but it leads to cases (and date/time values are probably the most common expression of this) where what you see in the cell is very different from what Excel sees in the cell.

The key thing as a programmer that you need to become comfortable with when working date/time serial numbers is that Excel stores time as a fraction of a day. The value "6 hours" (however you choose to format that value) is stored in Excel as 0.25 (6/24 or 1/4 of a day). This means that, whenever you need to use the time value (remember that the value Excel sees is in units of "days") with a rate (something/unit time), the key to getting the calculation to work is a simple unit conversion -- either make sure that your rate is in units of "something/day" [=rate/CONVERT(1,"unit time","days")] or convert the time value that Excel sees from "days" to whatever time unit your rate uses (if its and hourly rate, then convert Excel's time value from days to hours) [=CONVERT(time value,"days","hrs")].

There are, of course, lots of different ways to perform the unit conversion. I like to use the CONVERT() function mostly because it helps me remember the purpose of the calculation -- namely that I am performing a unit conversion. In the end, you as the programmer can decide exactly how you like to perform the unit conversion.

In your sheet, for example, I might replace the "pounds per hour" rates in column T (at least, I'm assuming they are "pounds per hour") with "pounds per day" rates of 24 (in place of the 1 pound/hr values) and 120 (in place of the 5 pounds/hr). Then I can use the times (in days) as Excel sees them. Again, you as the programmer can decide exactly how you want to do this, but the key step is how do you want to reconcile values that involve times in hours and values that involve times in days so that those values are using consistent time units.