Automatic Billable effort hours calculation

[MaheshK5277]

Hi,

I need help to construct a excel spreadsheet for my project to track billable effort hours which would auto-update based on daily entries.

Brief:
i have 6 members in a team including me. We have a Project(say named FLEX) & we certain effort hours budgeted for this project (say for e.g 800). These 800 hours are allotted under various WBS (work breakdown structure)codes like D01, D02 & so on until D15 which totals to 800. Now everyday one or all persons in my team will work on this project & book their hours under respective WBS codes. (the hours booked by persons may be haphazard, like someone books 2 hours under WBS code D15, other person on the same day books 4 hours under the same code, some other person may book 4 hours under D10 & so on..No restriction to limit the hour bookings on a particular day)

i have to maintain a sheet which captures the entire project hours, date-wise, day by day- person-wise & mapped with respective WBS codes.
Also, it should show me how many hours under a particular WBS code are remaining "as on date", i.e.=TODAY() to enable me to track the hours. Also, if possible the sheet should flag an alarm (in terms of colored cell or otherwise) when any particular WBS code hours are about to exhaust ,say 10 hours before.

Pl help & provide me with a excel sheet or guide on formulaes.

Thanks,
Mahesh

[MaheshK5277]

file attached for ready reference

[shukla.ankur281190]

Hi Mukesh ,

I think you need a pivot table which can deal your requirement.

Here is attached an workbook with containing two sheets naming data and pivot. Data sheet need to update manually and pivot will auto update information as you desired.

Note :- I have converted data sheet into table so when you need to update data you need to go on last filled cell and click tab it will insert a row. We are doing this job just because we will no need further update ranges into pivot.

Hope It will help you

[MaheshK5277]

Hi Ankur,

Thanks for the solution...i am however facing a problem of the Pivot not getting auto-updated when i enter some information in the worksheet named-"working". You have a worksheet on 2nd tab named as "Pivot"; is that really a pivot? Can you help me on how to auto-update the pivot?

Thanks again...your solution is amazing.

Thanks,
Mahesh

[shukla.ankur281190]

Hppy to hear it that you liked it .. Yes you can refresh pivot by right click of mouse on pivot any cell and refresh the pivot. BTW whenever you update working sheet and close the workbook and re-again open it.... Pivot will auto update.

Please speak if you will have further query.

If you liked my solution please add rep by clicking * star icon on you thread screen on that post which helps you

[MaheshK5277]

i have added a * star icon & changed the post prefix as 'SOLVED". Many Thanks....!

[shukla.ankur281190]

Glad to hear it and thanks for feedback


Stay tune with Excel forum for learning new things about excel