Problem with calculating hours in timesheet? (sysgulv)
 

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))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0)



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

Harald
Systemgulv

Problem with calculating hours in timesheet? (sysgulv)
 

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

HTH
Kostis Vezerides

On Jun 22, 6:33 pm, Systemgulv
wrote:
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))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0)

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

Harald
Systemgulv

Problem with calculating hours in timesheet? (sysgulv)
 

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)


"vezerid" wrote:

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

HTH
Kostis Vezerides

On Jun 22, 6:33 pm, Systemgulv
wrote:
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))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0)

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

Harald
Systemgulv

Problem with calculating hours in timesheet? (sysgulv)
 

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:

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)


"vezerid" wrote:

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

HTH
Kostis Vezerides

On Jun 22, 6:33 pm, Systemgulv
wrote:
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))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0)

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

Harald
Systemgulv