IF Function and Time Subtraction

Question

Coach:

I've got a simple time card spread sheet drawn up. My question is I would like to set a function that if a cell is less than or equal to my base, it subtracts from my base to give a total. And maybe indicates "ok" if equal.

Example: kevin work 6 hours in an 8 hour day = 2 hours left.

So far this is my formula:

C1 =IF(B1>=A1, "ok", A1-B1)

A1= 8 hours
B1= 6 hours worked
C1= Time left or "ok"

Next, how can I subtract time in hours, without it defaulting to real time?

Example: =A1-B2 doesn't give me the right total. And when I click on A1 to format it, reverts to 8 AM, instead of just 8 hours. Even so when I go to Custom and format it hh:mm- still reverts to 8 AM.?

Answer 1

To accomplish the specific requested task, you could use the following:
C1: =IF(B1>A1-1,"Okay",CONCATENATE(A1-B1," hours unaccounted for"))

If the baseline is always 8 hours, however, then I would use the actual values of "7" and "8" rather than filling in another A column cell every time. You'll save yourself a lot of headache if you don't have to fill in an additional cell for every single formula. I also wouldn't use "greater than" to quantify results, because you probably want to also account for any overages in hours. I would match the =IF statement with the number of hours desired.

Assuming B is still the column in which the daily hours are accounted for, I would do something like this:
C1: =IF(B1=8,"Okay",CONCATENATE(8-B1," hours unaccounted for"))

Either way, this will return a value of "Okay" if the number of hours worked was 8, otherwise, it will display number of hours that deviate from the baseline with text afterwards.