Need help with my timesheet? regular, 50% and 100%
Oops! Change MAX to MAKS for the last 2 formulas
--
Regards,
Peo Sjoblom
"Peo Sjoblom" wrote in message
...
OK, I misunderstood, so what you are saying is that basically lunch time
is
F13-E13
so let's do it again
The first would be
=MIN(7,5;((G13-D13-(F13-E13))*24))
formatted as general (not time)
second would be
=MAX(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5)
and third
=MAX(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5)
to test it I did as follows
D13 E13 F13 G13
08:00 12:00 13:00 21:30
using the formulas I provide will return 7.5 regular, 3.0 50% and 2.0 100%
change the end time in G13 to 18:30 we will get 7.5 regular, 2.0 50% and 0
100%, change end time to 16:15 we will get 7.25 regular and zero 50 and
100%
It is important that you use general (or the Norwegian equivalent) or
number formatting and NOT time formatting since it will be way off if you
do. For instance after you multiply a time value with 24 you will get the
decimal equivalent and 7,5 is the same as 7,5 days which is 180 hours
Sorry about the wrong function translation, I just assumed you used
something similar to Swedish function names. Also if you get any error
message while applying the formula make sure the delimiters are correct, I
am located in the US and at least Swedish delimiters are semicolons as
opposed to commas and the decimals are written 7,5 as opposed to US 7.5
--
M.v.h.
Peo Sjoblom
"Systemgulv" wrote in message
...
Hi, Peo Sjoblom
G13-F13 is the hours after lunch.
I've tried what you suggested, but gets the error #NAVN? (#NAME?) when I
use
the command STØRST.
I,ve tried to use the command MAKS instead, but the result is wrong?
=MAKS(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5)
inn out inn out reg 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)
Harald
Systemgulv
Peo Sjoblom skrev:
If G13-F13 is lunch that should be deducted you shouldn't add it
the first 7.5 can be written
=MIN(7,5;((E13-D13-(G13-F13))*24))
between 7.5 and 10.5 can be written
=STØRST(0;MIN(10,5;((E13-D13-(G13-F13))*24))-7,5)
and all hours worked greater than 10.5
=STØRST(0;((E13-D13-(G13-F13))*24)-10,5)
--
M.v.h.
Peo Sjoblom
"Systemgulv" wrote in message
...
I,m making a timesheet for regular worktime, 50% overtime hour and
100%.
regular worktime is 7,5 hour
For this I use:
=HVIS((((E13-D13)+(G13-F13))*24)7,5;7,5;((E13-D13)+(G13-F13))*24)
NB! HVIS=IF, I think? I,m norwegian.
All hours after 7,5 is overtime, 3 hour 50% and the rest is 100%
I,ve figured out the 100% with this:
HVIS(((E13-D13)+(G13-F13))*2410,5;((E13-D13)+(G13-F13))*24-10,5;0)
All hours after 10,5 is 100%
My problem is how to get the 3 hour 50% to count.
It looks like this:
inn out inn out reg 50% 100%
Total
D13 E13 F13 G13 H13 I13 J13 K13
09:00 12:00 13:00 22:00 7,5 ??? 1,5 12
I hope someone can help!
|