Construct staff pay timesheet

My Aunt runs a care package for her severly autistic son - this entails
having various staff looking after him at various times of the day and
night in (mostly) regular shift patterns.

I would like to help her by making her a spreadsheet that will track and
calculate the pay for each staff member and which (I hope) will be simple
enough for her and other people to use.

I hope I am not giving too much detail here and I appreciate that help is
free - for which I am very grateful.

The pool of staff is around 7 - 8 each week
Pay rates differ mostly depending on time of day - shift hours are paid at
a single rate of pay but I suppose it is possible at some time that staff
that work together get paid a slightly different rate.

Staff pay is paid monthly so I imagine a workbook with 12 (or more if
special sheets are needed) worksheets.

The shifts work roughly like this;


There are 2 staff on for 14 hours each daytime - paid 'per hour'

There are 2 staff on overnight (we call them 'sleeps' as they are asleep on
duty) - Sleeps are paid 'per sleep'

There is 1 (sometimes, rarely, 2) staff 'On Call' for a 24 hour (sometimes
less) period. OC is paid a low hourly rate

Sometimes public holidays pay at increased rates - like time and a half
etc.

I am really happy to do the actual work here, I am looking for advice - I
am a database programmer but feel that this is going to be most useful as a
spreadsheet and I don't do a lot of spreadsheet work - I am a bit versed in
the black arts of VBA if needed.

My initial idea was;
Column 1 with date
Column 2 DayStaff 1
Column 3 Daystaff 2
Column 4 Sleep 1
Column 5 Sleep 2
Column 6 OC 1
Column 7 OC 2
Column 8 (+ more if required) Extras

At the bottom of each sheet I need to add up the pay due to each staff
member and of course I would like the staff member to be entered in an easy
way - maybe a pick list which I have sort of got to grips with already, but
there may be better ways.

I have also got hours and pay rates columns at the far right of each of the
above columns so that small adjustments could be made to each shift as
required - these could mostly be hidden and exposed only when required.

The 'gross' figures at the bottom of the sheet would use the hours and pay
rates in it's calculation of each staffs total pay.

I would like to be able to print out a part of the sheet as a 'Staff Rota'
containing only 'relevant' rota info.

Being a DB programmer I am acutely aware of how much work you can cost
yourself by getting basic design wrong, and I would like to avoid that if
possible of course.

I would really welcome suggestions, advice and info regarding this task
before I launch into it.

I hope this is all not too wordy and look forward to hearing back from
anyone who has some help to offer.

Regards

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Isis" wrote in message
...
My Aunt runs a care package for her severly autistic son - this entails
having various staff looking after him at various times of the day and
night in (mostly) regular shift patterns.

I would like to help her by making her a spreadsheet that will track and
calculate the pay for each staff member and which (I hope) will be simple
enough for her and other people to use.

I hope I am not giving too much detail here and I appreciate that help is
free - for which I am very grateful.

The pool of staff is around 7 - 8 each week
Pay rates differ mostly depending on time of day - shift hours are paid at
a single rate of pay but I suppose it is possible at some time that staff
that work together get paid a slightly different rate.

Staff pay is paid monthly so I imagine a workbook with 12 (or more if
special sheets are needed) worksheets.

The shifts work roughly like this;


There are 2 staff on for 14 hours each daytime - paid 'per hour'

There are 2 staff on overnight (we call them 'sleeps' as they are asleep
on
duty) - Sleeps are paid 'per sleep'

There is 1 (sometimes, rarely, 2) staff 'On Call' for a 24 hour (sometimes
less) period. OC is paid a low hourly rate

Sometimes public holidays pay at increased rates - like time and a half
etc.

I am really happy to do the actual work here, I am looking for advice - I
am a database programmer but feel that this is going to be most useful as
a
spreadsheet and I don't do a lot of spreadsheet work - I am a bit versed
in
the black arts of VBA if needed.

My initial idea was;
Column 1 with date
Column 2 DayStaff 1
Column 3 Daystaff 2
Column 4 Sleep 1
Column 5 Sleep 2
Column 6 OC 1
Column 7 OC 2
Column 8 (+ more if required) Extras

At the bottom of each sheet I need to add up the pay due to each staff
member and of course I would like the staff member to be entered in an
easy
way - maybe a pick list which I have sort of got to grips with already,
but
there may be better ways.

I have also got hours and pay rates columns at the far right of each of
the
above columns so that small adjustments could be made to each shift as
required - these could mostly be hidden and exposed only when required.

The 'gross' figures at the bottom of the sheet would use the hours and pay
rates in it's calculation of each staffs total pay.

I would like to be able to print out a part of the sheet as a 'Staff Rota'
containing only 'relevant' rota info.

Being a DB programmer I am acutely aware of how much work you can cost
yourself by getting basic design wrong, and I would like to avoid that if
possible of course.

I would really welcome suggestions, advice and info regarding this task
before I launch into it.

I hope this is all not too wordy and look forward to hearing back from
anyone who has some help to offer.

Regards