Problem with calculating hours in timesheet? (sysgulv)

[Systemgulv]

wMy timesheet looks like this:

inn out inn out reg,h 50% 100% Total
D13 E13 F13 G13 H13 I13 J13 K13

06:00 12:00 13:00 16:00 7,5 ??? 0,0 ???
(1,5) (9,0)

I need help with I13: The hours between 7,5h and 10,5h.

H13 to be between 0,0 to 7,5 (this one works!)

=HVIS((((E13-D13)+(G13-F13))*24)>7,5;7,5;((E13-D13)+(G13-F13))*24)

I13 to be between 0,0 to 3,0 (this is the hours after H13 (7,5), not
working?)

=HVIS????

J13 to be between 0,0 to 13,5 (this is the hours after H13 and I13
(10,5), this is working!)

=HVIS(((E13-D13)+(G13-F13))*24>10,5;((E13-D13)+(G13-F13))*24-10,5;0)



NB! I think the HVIS comand is the same as IF, logic test.

Harald
Systemgulv

 

[vezerid]

=MAX(0;7.5-24*(E13-D13+G13-F13))

HTH
Kostis Vezerides

 

[Toppers]

try:

H13:
=MIN(7.5;($E$13-$D$13+$G$13-$F$13)*24)
I13:
=MIN(3.0;($E$13-$D$13+$G$13-$F$13)*24-7.5)
J13:
=MAX(0;($E$13-$D$13+$G$13-$F$13)*24-10.5)

 

[Systemgulv]

Hi, Toppers

Your formula for H13 and J13 works great, but not for I13...
It shows a negative number if the hours are less then 7,5.

So I will use this instead:
=MAX(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5)

(I got the formula from Peo Sjoblom in my other tread her)


but thanks for the effort.

Harald
Systemgulv





Toppers skrev: