If/hour

[Suomi-finland]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi:
I need some helps with this function, please!!!

I have this shift: 5:00-17:00 so if I do 17:00-5:00 I have 12:00 hours shift.

Now I need to calculate the lunch brakes: if the shift is 7-10 hours you have 30 min break, and if the shift is over 10 hours, 1 hour break. So I create this function:

=IF(I2>=11;1;IF(I2>=7;0,3;0))

But i need the result expressed in hours. If the cell have the format as "number" work perfectly, but if i change the cell format in "hour" doesn't work. What I do wrong? Could you please give any help about.
Thank you very much

 

[CyberTaz]

If I understand the format you're trying to use, keep in mind that 'hours'
is a Time format which is for expressing time of day. The number of hours
returned by subtracting one time of day from another is a quantity, so it
should be formatted as Number, not as a Time.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Suomi-finland]

Hi:

I try to explain better the case.

A B C D F
05:00 17:00 12:00 0:00 1,00

Columns A,B,C,D have a Time format
Column F have a number format

In D and F I have the same IF formula =IF(C1>=11;1;IF(C1>=7;0,3;0))
in D with Time format doen't work but in F with number format works properly.

I really need Time format because then I have to subtraction C1-D1 and again I need to get the result in hours not like a number (Time format).

Thank you!
Regards from Helsinki!

 

[CyberTaz]

I understand your case, but let me make my explanation a little more clear:

10 O'clock AM
Minus
7 O'clock AM

Yields a result of 3 HOURS [3.0], not 3 O'clock [3:00].

See Bob Jordan's response for more specific info.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Suomi-finland]

Yes! now it works properly, thank you!
I Love Excel for Mac, I use this program a lot, but sometimes is hard to understand. Thank both of you. regards.

You need to note that times as you are using them are fractions of a day as

 

[stuart4487]

Do you only have Monday to Friday dates in column A? If so try this
formula in D2 copied down

=IF(B2+C2-A2>IF(WEEKDAY(A2)=6,3,1)+"14:00&quot,"Late","On Time&quot

Now you can jsut count the "Lates".....or in a single formula to count
Lates

=SUM(IF(B2:B10+C2:C10-A2:A10>IF(WEEKDAY(A2:A10)=6,3,1)+"14:00",1))

which is an array formula that needs to be confirmed with CTRL+SHIFT
+ENTER