timesheet help

  • Thread starter Thread starter stevebicks
  • Start date Start date
S

stevebicks

Hi all!

Don't know if this can be done but i'm pretty sure one of you guys will
soon help me out.

i'm trying to setup a timesheet whereby entering the time from and to
will insert the total hours worked in another selected cell.
when i do this by simply using the formula say =sum (A4-A3) i get the
result i require but if i then want to have a total for the week it
goes haywire if it goes over 24 hrs ,i hope someone knows what i mean
lol, this is with cell formatted to hh,mm.

second hopeful requirement, if the hours TO goes over say 18:00 then
any hours worked past that time will go in another cell representing
time + 1/4 and if after 20:00, in a column representing time + 1/2

forever grateful for any help

Steve
 
Hi Steve!

For first problem of hours >24 in total use format [hh]:mm

For second problem use the formula:

=B1-A1+(B1<A1)

The logic is that times are recorded as a decimal part of one day (eg) 0.25
for 6:00AM. Where the time is after Midnight we should really input 1.25 for
6:00AM the following day. Subject to the total time not being greater than
24 hours, the above formula does that.

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks for that norman, however i'm still stuck on the second problem.

Would it be possible to send you an example timesheet as i'm not sur
of the best way to explain what i'm trying to do.

thans

Stev
 
Hi Steve!

No problem. I've got pretty good anti-virus pro#$&#!@ #^@@ $$@^^ that
hasn't let me dow^@ ^@^@

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Steve!

Received OK and returned. Difficult to explain the problem but no
really new principles are involved.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
stevebicks > said:
Cheers Norman, i sent it early this am so let me know if you
Click to expand...
received it
 
Hi Steve!

ROTFLMAO!

Since we do try to share what we have resolved:

Clock on and off times were in B2 and C2.
Steve wanted in separate columns DEF
Hours up to 18:00
Hours between 18:00 and 20:00
Hours after 20:00
Possibility of spanning Midnight was also to be accounted for.

Formula for D2:
=MIN(C2-B2+(C2<B2),(TIME(18,0,0)-B2)*(C2>B2))
Formula for E2:
=(((C2-TIME(18,0,0))*(C2>B2)*(C2>TIME(18,0,0))))-IF(C2>TIME(20,0,0),C2
-TIME(20,0,0),0)
Formula for F2:
=C2-B2+(C2<B2)-(D2+E2)

The entries for seven days then needed summing and multiply by the
hourly rates in D10:
=(SUM(D2:D8)*24)*D10

I'll not pretend that the formulas are the most efficient but they
seemed to work OK for the selected data given.

My suspicions are that information provided does not cover someone who
clocks on at 2:00 AM but I wasn't given an earlies "penalty rate"
(which is the term used in Australia for overtime / unsocial hours
payments)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top