Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with project tracking
Monique,
The easiest way would be to have one sheet per project. If you have the following headings in the row 3: A3: Date B3: Name C3: Start D3: End E3: Breaks F3: Total Time G3: Rate H3: Cost I3: Week From J3: To K3: Weekly Cost Then in cells F3 & H3:K3 the following formulas: F4 =IF(AND(C4<"",D4<""),D4-C4-E4,"") H4 =IF(F4="","",F4*24*G4) I4 =IF(A4,A4-WEEKDAY(A4)+2,"") J4 =IF(I4<"",I4+7,"") K4 =IF(J4<"",SUMPRODUCT((A4:A1000=I4)*(A4:A1000<=J4 )*H4:H1000),"") The in cell I5 enter the formulas: I5 =IF($I$4="","",IF(MAX(A:A)=I4+8,I4+8,"")) and copy all the foumulas down as far as you require The times in Columns C:E should be entered as XL time ie 10:15 or 10:15 AM, (but note that there is a space between the 15 and the AM), and any times in column E as 00:15 Post back if you have any further questions of or e-mail me direct if you want a sample sheet. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Monique" wrote in message ... Hi, I work for an Architect and we have several draftsmen and draftswomen who do the drawings for us. Every client that comes in and wants us to do some work for them we assign it a project number. We dont always assign a certain draftsmen or woman to work on any particular project from begining to end we have probably everyone of the draftspeople working on one project at some time or another. I am trying to find the easiest and fastest way to keep track of each draftsmens hours per project per week per our payperiod and then calculate the total to charge the client at the clients rate per hour. i basically need to be shown or told how to set up my worksheets in a workbook to tally all of this on one sheet per either client project number or by draftsperson. I hope I am making some sense about my objective. Please can someone understand what I am trying to ask? Thank you I look forward to someone's response. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with project tracking
Now that I have returned, starting over again:
Monique, The easiest way would be to have one sheet per project. If you have the following headings in the row 3: A3: Date B3: Name C3: Start D3: End E3: Breaks F3: Total Time G3: Rate H3: Cost I3: Week From J3: To K3: Weekly Cost Then in cells F3 & H3:K3 the following formulas: F4 =IF(AND(C4<"",D4<""),D4-C4-E4,"") H4 =IF(F4="","",F4*24*G4) I4 =IF(A4,A4-WEEKDAY(A4)+2,"") J4 =IF(I4<"",I4+7,"") K4 =IF(I4="","",SUMIF($A$4:$A$1000,"="&I4,$H$4:$H$26 )-SUMIF($A$4:$A$1000,""&J4,$H$4:$H$26)) The in cell I5 enter the formulas: I5 =IF(I4="","",IF(MAX(A:A)=I4+8,I4+8,"")) and copy all the foumulas down as far as you require The times in Columns C:E should be entered as XL time ie 10:15 or 10:15 AM, (but note that there is a space between the 15 and the AM), and any times in column E as 00:15 Post back if you have any further questions of or e-mail me direct if you want a sample sheet. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with project tracking
No even then I've got it wrong! try:
J4: =IF(I4<"",I4+6,"") I4: =IF(I4="","",IF(MAX(A:A)=I4+8,I4+7,"")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Sandy Mann" wrote in message ... Now that I have returned, starting over again: Monique, The easiest way would be to have one sheet per project. If you have the following headings in the row 3: A3: Date B3: Name C3: Start D3: End E3: Breaks F3: Total Time G3: Rate H3: Cost I3: Week From J3: To K3: Weekly Cost Then in cells F3 & H3:K3 the following formulas: F4 =IF(AND(C4<"",D4<""),D4-C4-E4,"") H4 =IF(F4="","",F4*24*G4) I4 =IF(A4,A4-WEEKDAY(A4)+2,"") J4 =IF(I4<"",I4+7,"") K4 =IF(I4="","",SUMIF($A$4:$A$1000,"="&I4,$H$4:$H$26 )-SUMIF($A$4:$A$1000,""&J4,$H$4:$H$26)) The in cell I5 enter the formulas: I5 =IF(I4="","",IF(MAX(A:A)=I4+8,I4+8,"")) and copy all the foumulas down as far as you require The times in Columns C:E should be entered as XL time ie 10:15 or 10:15 AM, (but note that there is a space between the 15 and the AM), and any times in column E as 00:15 Post back if you have any further questions of or e-mail me direct if you want a sample sheet. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Are there project tracking templates in Excel? | Excel Discussion (Misc queries) | |||
Need help with reporting | Excel Worksheet Functions | |||
How to convert MS Project to MS Excel. I don't have MS Project. | Excel Discussion (Misc queries) | |||
Cannot save Project file as MS Access file | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |