Timesheets

[pamelas3565]

Yes I know these seem to be done to death but I think I have a new
challenge for you!!
I need to create one to ease the burden of myself and my colleague who
have the unenviable task of working on engineers timesheets every
Monday.
Background info -
Monday to Friday
6:00AM - 21:00 is normal time
21:00 - midnight is time and a half
Midnight to 6am is double time
Saturday from midnight until noon is time and a half after this is
double time
Sunday is Double time

Each job the engineer carries out is on a different contract for which
we have to allocate the appropiate hours.
I cannot for the life of me work out the formulas.

Start End lunch total hours normal time/
half double

05:30 11:00 5.50
11:00 12:50 1.83
12:50 13:15 0.42
13:15 16:00 0.50 2.25
16:00 18:15 2.25
18:15 21:00 2.75
21:45 23:00 1.25
23:00 01:30 2.50
My formula in total hours is '=((F14-E14+(F14<E14))*24-G14). The
engineers indicate the time slot in which they take there lunch this
means we accurately deduct the time from the correct contract. I need
a formula to calculate the normal hours (in the first line that would
be 5 hrs and .5 in time/half.

Any takers? Or are you all waving the white flag!!
Please if you can help or need further info mail me.

 

[JLatham]

Pamela,
This is not one for shrinking violets, I assure you. I started out on a
timesheet with someone in these groups some time back that had much the same
type of situation you do, with various times of the day requiring different
consideration and different days of the week doing other things (Sunday is
easy!) In the end we resorted to a user defined function in VB to
accomplish the task because, in his case, the situation was so complex that
it was virtually impossible to write a formula covering all of the If, Then,
Wherefore's and Therefore's and not get hopelessly confused in the process.
To complicate matters, besides stretching across midnight, that timesheet was
split into a before-break (lunch) and after-break (lunch) periods.

Anyhow, it becomes a beast probably best dealt with in VB. I see a couple
of problems with the layout you have, or at least they would be problems for
me

You don't include the date that a shift starts/ends on . So what happens
when someone gets stuck on a tough job and ends up working from 05:00 on
Monday on through until 01:00 on Tuesday (or even worse: 05:00 on Monday
until 05:30 on Tuesday - without a date, it looks like they worked 30
minutes!).

You also don't mention whether or not there's extra pay involved if they
work over a specified number of hours in a shift. For example, you say that
6:00 a.m. until 9:00 p.m. (21:00) is all normal time - but is it all normal
time if they work that entire period of 15 hours?

 

[JLatham]

Found the discussion and here's link to where I generally described that
setup. You'll notice that in that discussion it looks like we got it all
solved with worksheet formulas - that was not the case. It went on for at
least a month after that discussion ended in eMail exchanges, the conversion
to a UDF and several tweaks to that before it was declared DONE! And then it
may have been decided by those at management level that the whole thing was
too complex for them to maintain should it ever break or they ever change
their rules and so I don't even know if it ever actually got put into use.

http://www.microsoft.com/office/com...misc&mid=a32b9a87-6ee6-4971-b33e-079025833f47

 

[Sunday88310]

Just a passing thought; Steven Bullen's "Professional Excel Development" uses
this time clock model approach.