Man Hours - Theoretical, Actual and Deficit

[Phill.Gould]

Hi everyone!

I'm new to this forum but have had many successes with Excel thanks to the posts here.

I have recently started a new role Volunteering to help get HR cleaned up for a Community organisation. I have recently redone their Rostering and Main Database systems but the next project is proving a little tricky.

I've been tasked with finding a way to calculate the 'Theoretical or Required' man hours, the Man Hours actually worked, the deficit between the two, and then breaking the Deficit down to types of absenteeism, such as Leave, no Show, Illness etc.

Any advice would be greatly appreciated!


Phil

[kersplash]

Attach a workbook showing some sample data and what you expect as your outcome.

Go Advanced -> Manage Attachments -> Upload

[Phill.Gould]

Here is a sample-of-sorts of what I'm looking for - I hope it is clear enough.

[Toonies]

Are the Rostered hours the same each week?

[Phill.Gould]

No, as it's a Volunteer centre the hours are chosen by the volunteers themselves, so they often differ from week to week

[FDibbins]

Could you show some sample answers, so we know what you are looking for?

[Phill.Gould]

The answers I'd like would be simply a breakdown of hours such as:

Man Hours Required
Man Hours Worked
Deficit between Req/Worked

And then a break down of the Deficit into Leave/Sick or No Show... I'm not really sure how to show that in a sample piece

[Toonies]

Hi You could try this

it records and breakdowns Hours by cell colour,


I've used the following Theme Colours (on my excel the are the right top 3 of the Theme colour Table)

Illness = Gold, Accent 4 (Colour Code 44)
No Show =Blue, Accent 5 (Colour Code 42)
Leave = Green Accent 6 (Colour code 50)

Normal rostered hours are No Fill (Colour Code 0)


In H4 create a Defined Named Range Called Color

In The Refers to: put the following Formula

Please Login or Register  to view this content.

in H4 put

Please Login or Register  to view this content.

then Copy

Please Login or Register  to view this content.

Now when you change the colour of the rostered cells the corresponding cell in the named range displays the cell colour code.

Now in M4 put the following formula

Please Login or Register  to view this content.

and copy M4 to Q18

Now in S4 put the following formula

Please Login or Register  to view this content.

and copy S4 to W18



Now in Y4 put the following formula

Please Login or Register  to view this content.

and copy Y4 to AC18


Now in AE4 put the following formula

Please Login or Register  to view this content.

and copy AE4 to AI18

When you change the Cell colour for any rostered hours then you must re-enter the text value again and press enter as Excel does not pick up cell changes by colour alone.

I'm sure that the Excel Gurus (who leave me way behind them) could come up with a much easier way of doing what you need.

hide any columns that you don't want to be shown

this is my best shot at it.

[Phill.Gould]

Toonies,

That is pretty damn close to perfectly what I am looking for. Thanks so much for your effort!

[Toonies]

No problem, I would still ask the Guru experts in the forum as I'm sure the could enhance it.