[Wylug-help] spreadsheet help

[Stephen Garton]

Hi Scott

Scott Hodgson wrote:
> hello
> 
> I am using OOo spreadsheet to make a flexi time calculator but i am
> having trouble with the calculations
> 
> all cells are formatted in time as HH:MM
> I have IN, OUT FOR DINNER, IN AFTER DINNER, THEN HOME
> My sum for the day is
> =((C4-B4)+(E4-D4))
> C=OUT FOR DINNER B=IN E=THEN HOME D=IN AFTER DINNER
> that gives me day total, but because of law we have to put at least 30
> mins in the dinner section and oftem we have less, so I tried
> =IF(D4-C4<00:30)
> THIS SECTION WORKED SO TRIED
> =IF(D4-C4<00:30;D4=C4+00:30)
> I am trying to change the dinner to make it 30 minutes if under 30
> minutes. I stopped here as nothing happened my next bit was to try add
> onto the formula total for day.  How can i use one cell to change dinner
> to 30minutes if under and calculate total for day in one cell?

I believe the formula you want is:

=IF(D4-C4<00:30;(E4-B4)-00:30;((C4-B4)+(E4-D4)))

which OOo will change to:
=IF(D4-C4<0.0208333333333333;(E4-B4)-0.0208333333333333;((C4-B4)+(E4-D4)))

but does the same :)

> 
> the other problem I had was when totalling the week day totals even when
> I formatted cells to [HH]:MM the total was wrong the formula either
> =sum(F4:F8)
> or
> =(F4+F5+F6+F7+F8) but the day totals were correct meaning it can handle
> time.
> 

Can't help with this one, I'm afraid, seems to work fine here. I would
say it was a formatting problem rather than a formula one.

HTH

-- 
Steve Garton
www.sheepeatingtaz.co.uk