Please help with this formula

  • Thread starter Thread starter Joey Mattingly
  • Start date Start date
J

Joey Mattingly

Hi! I am trying to make a scheduling template for work
and am running into some troubles. I have the formula to
calculate the hours worked, but need to modify it so that
if a person is scheduled more than 6 hours, it
automatically takes out a 30 minute break. Here is the
formula that I have:=IF((OR(B12="",B11="")),0,IF
((B12<B11),((B12-B11)*24)+24,(B12-B11)*24))

If it is also possible, I would like to be able to type
in the scheduled hours as being non-military time. Can
someone please help me? Please email me back at
(e-mail address removed). Thanks in advance.
 
Joey,

How about
=IF(MAX((B12-B11),(B11-B12))>TIME(6,0,0),MAX((B12-B11),(B11-B12))-TIME(0,30,
0),MAX((B12-B11),(B11-B12)))

If you want to enter time non-military, you need to append AM or PM , like 8
AM or 7:15 AM (note the space)
 
That doesn't work either. Maybe I'm putting it in the wrong place?
Does it need to go at the end, and if so, how do I conncect it to the
other formula since its all in the same cell? Also, I don't understand
what you mean by that I need to append the AM or PM??
 
Joey,

These are the pieces:
Time = B12 - B11
If B12 < B11
Time = B12 - B11 + 12
Now
If Time > 6
Time = Time - 0.5

No AM or PM required. Time entries must be in decimal formate
15 minutes = 0.25, 30 minutes = 0.5, 45 minutes = 0.75

Examples
B11 B12 Time
9.00 12.00 3.0
9.00 1.00 4.0 (-8.0 + 12)

Now just string it all together in a big If statement. Personally, I
prefer to put it into a macro so that all the pieces are easily
laid out.

steve
 
Back
Top