Prev: how can i freeze first coloumn & first row of exel sheet
Next: V Look up
From: Isis on 23 Apr 2010 06:38
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


From: Don Guillett on 23 Apr 2010 08:42

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
dguillett(a)gmail.com
"Isis" <isissoft(a)NOSPAMbtinternet.com> wrote in message
news:Xns9D637673D7C10isissoft(a)193.202.122.161...
> 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
>
>

 | 
Pages: 1
Prev: how can i freeze first coloumn & first row of exel sheet
Next: V Look up