help with project tracking

[Guest]

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.

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

OOPS!

Didn't test past where I had data - ##VALUE! errors returned. If no one
posts a better solution I will post corrections later.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk