View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default Need help with my timesheet? regular, 50% and 100%

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


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(J130,3,IF(K13=7.5,K13-H13,0))

Rick