Need help with my timesheet? regular, 50% and 100%

S

Systemgulv

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))*24>10,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!
 
R

Rick Rothstein \(MVP - VB\)

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))*24>10,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
Click to expand...

Not sure what HVIS is (probably language specific IF statement). Anyway,
assuming column H is calculated (in case a person works less than 7 and a
half hours), and assuming column J values calculate correctly, can't you set
column I to 3 if column J is greater than 0 and to the difference between
column K and column H otherwise? I am thinking something like this (using IF
statements where the decimal separator is the dot and argument separators
are the comma)...

I13: =IF(J13>0,3,IF(K13>=7.5,K13-H13,0))

Rick
 
P

Peo Sjoblom

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)
 
S

Systemgulv

Hi, Rick

I see you use the difference between K13 and H13, the problem is that K13 is
the
SUM of H13, I13 and J13

=SUMMER(H13:J13)

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)

I need:
H13 to be between 0,0 to 7,5 (this one works!)
I13 to be between 0,0 to 3,0 (this is the hours after H13 (7,5), not
working?)
J13 to be between 0,0 to 13,5 (this is the hours after H13 and I13
(10,5), works!)

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

But the value only shows negative numbers and 3???

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

Harald
Systemgulv


Rick Rothstein (MVP - VB) skrev:
 
S

Systemgulv

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)
Click to expand...

Harald
Systemgulv




Peo Sjoblom skrev:
 
P

Peo Sjoblom

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
 
S

Systemgulv

YES, you solved my problem Peo Sjoblom!

I wil use your formula for I13, with some smal changes as you suggested.

Instead of MAX I used MAKS.

=MAKS(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5)

Thanks
Harald
Systemgulv

NB! Your second and third formula is the same... but it was just what I
needed:)


Peo Sjoblom skrev:
 
P

Peo Sjoblom

Systemgulv said:
YES, you solved my problem Peo Sjoblom!

I wil use your formula for I13, with some smal changes as you suggested.

Instead of MAX I used MAKS.

=MAKS(0;MIN(10,5;((G13-D13-(F13-E13))*24))-7,5)

Thanks
Harald
Systemgulv

NB! Your second and third formula is the same... but it was just what I
needed:)
Click to expand...

Oops! Not my day today


Peo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with calculating hours in timesheet? (sysgulv) 3
Working night and day..... Timesheet 5
Return Single Row of Numeric Data to Single Column 4
Why Am I getting a blank in the result cell? 4
Excel Timesheet Error 2
Need help with a timesheet formula 1
Create new workbook when macro is run 2

Top