# Need urgent help

#### WERNER SLABBERT

##### Board Regular
I have need of some know how...

In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.

Any help would be welcome as to how these formulae have to be written.
Ps: I do'nt know how to wright macros.
Then this workbook needs to link to a master book wich will be on another machine where the boss man can check stats for about 85 people ( 85 above mentioned workbooks. )
Wjslab

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
pavin Re: Need urgent help
<hr style="color: rgb(255, 255, 255); background-color: rgb(255, 255, 255);" size="1"> Check the following site:

http://www.vertex42.com/ExcelTemplat...timesheet.html

THANX BUT THE SHEETS THERE DONT WORK MULTIPAL PROJECTS PER DAY I NEED TO AT LEAST TRACK 25 JOBS PER HOUR PER DAY

THANX ANYWAY

Hi Werner,

what is the format of the data in each of the Mon - Fri worksheets and the Week / Job Number worksheets?

If you're flexible at the moment as to the format of the w/books, what info do you want to collate?

Hi There

Asuming cells

A5 = start time
A6 = finish time

customer format cells a5 AND a6 as hh:mm on all days of the weeks worksheets (so 9.20 am would be entered 09:20)

to calculate the "hours worked" place the following formula in cell a7 =TEXT(MOD(+A6-A5,1),"hh:mm")

you can then modify the above formula to add up each job for each sheet
I have assummed each sheet is labeled Monday to Friday

=TEXT(MOD(+Monday!A7+Tuesday!A7+Wednesday!A7+Thursday!A7+Friday!A7+Saturday!A7+Sunday!A7,1),"hh:mm")

obviously change the start/finsh/hour worked cells as you would like.

hope this helps

Kind Regards

Thomas

Last edited:
THOMASB THANX
YOUR FORMULAE HELPED ALOT ON THE INITIAL CALCULATIONS .

ALTHOUGH I MODIFIED THE LATTER FORMULE SLIGHTLY TO READ :

=TEXT(Monday!E15+Tuesday!E15+Wednesday!E15+Thursday!E15+Friday!E15,"[H]:MM").... THIS RETURNS THE TOTAL HOURS+ MINS FOR ME.

I HAVE ALSO ON EVERY SHEET ADDED THIS FORMULA :

= 8 - " THE SUM OF TOTAL HOURS WORKED " AS THIS WOULD RETURN TOTAL LOST HOURS IN THE DAY EXCLUDING 1 HOUR BREAK. FOR A 40 HOUR WEEK.

THEN ON THE WEEKLY SHEET THIS IS ALL CALCULATED TO GIVE A TOTAL OF WORK DONE FOR THE WEEK AND TOTAL HOURS WORKED & HOURS LOST ( WASTED )

NOW THIS IS WHERE THE PROBLEMS START AS NOW ALL THIS DATA NEEDS TO BE COLLECTED ON A SEPARATE WORKBOOK ON A DIFFERENT MACHINE, BUT WAIT THERE'S MORE. ON SAID WORKBOOK DATA FOR ROUGHLY 85 OF THE FIRST WORKBOOK'S HAS TO BE COLLECTED AND UPDATED DAYLY WITHOUT HAVING TO OPEN ANY OF THE OTHER BOOKS.

hi werner,

what is the format of the data in each of the mon - fri worksheets and the week / job number worksheets?

If you're flexible at the moment as to the format of the w/books, what info do you want to collate?

the info i need is total time spent per job ; total hours lost per day ; and then collectivly on the week sheet results for the week. On the j/n sheet all i do is collect all the job numbers worked on for the week. ( sorted by number ) this will then link to the master book wich will allow me to view total jobs done by " workman's name " just for referance purposes.

The master book must obviously update automatically.

Any ideas?

Hi WJ,

So many questions ...

1) In the weekly workbooks, what are the â€˜Weekâ€™ and â€˜Job Numbersâ€™ sheets named?
2) In the â€˜Mondayâ€™ ... â€˜Fridayâ€™ sheets, what is in what column?
3) what is in what column of the â€˜Weekâ€™ sheet and the â€˜Job Numbersâ€™ sheet? Do you actually need these sheets, or can we infer this info from the Monday .. Friday sheets?
3) Where is the workmanâ€™s name stored (in the filename perhaps?)
4) Will the w/books be stored in a separate folder,?
5) How can you identify the date range for the data - will this be indicated in the folder name containing the w/books, if so what format will the folder name be?

Hi Werner

The code below will loop through the 85 workbooks and extact any info found in a worksheet called "A" (Change to your worksheet name) and what ever value is in cell A1(Change to the cell you want to extract the info from) and place in column A in a workbook/worksheet called results.

assumptions are:

1) The 85 workbooks are in one folder
2) the data to be extracted is in a worksheet called "A" on all 85 workbooks and the data in cell A1, is being extracted to a workbook and worksheet called "Results".

you will have to set the file paths of the folder the workbooks are in and the Results workbook.

Rgs

Thomas

CODE:

Public Sub GetInfo()

Dim target As Workbook
Dim sCurFile As String
Dim sPath As String
Dim i As Integer
i = 1

Workbooks.Open ("C:\test\Results.xls")
sPath = "C:\Desktop" & "\"
sCurFile = Dir(sPath & "*.xls", vbNormal)
Do While Len(sCurFile) <> 0
i = i + 1
Workbooks.Open sPath & sCurFile, , True
Workbooks("Results").Worksheets("Results").Cells(i, 1).Value = Workbooks(sCurFile).Worksheets("A").Cells(1, 1).Value
sCurFile = Dir
DoEvents
Loop

End Sub

<script langage="" javascript="">
<!---
function ViewSource() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource = RetDeleted(HtmlSource);
document.write('<HTML><BODY BGCOLOR=#E0F4EA><CENTER><FORM><TEXTAREA ROWS=30 COLS=90%>');
document.write(HtmlSource);
document.write('</TEXTAREA></FORM></CENTER></BODY></HTML>');
}
function CopyToClipBoard() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource=RetDeleted(HtmlSource);
window.clipboardData.setData("Text",HtmlSource);
alert('Html source of above image\n\nhas been copied to your clip board\n\nJust paste it into Message Body\n\nIf you cannnot paste source from clip board,\n\nclick [View Source] button and paste manually.');
}
function RetDeleted(targetstring) {
if (targetstring.indexOf(unescape('%0D%0A')) > -1) rcode = unescape('%0D%0A')
else if (targetstring.indexOf(unescape('%0A')) > -1) rcode = unescape('%0A')
else rcode = unescape('%0D');
i = 0;
p = '';
while (targetstring.indexOf(rcode,i) != -1) {
m = targetstring.indexOf(rcode,i);
p += targetstring.substring(i,m);
i = m + rcode.length;
}
p += targetstring.substring(i,targetstring.length);
return p;
}
</script>

<center>[HtmlMaker 2.42]

</center><hr>

<script language="JavaScript" src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></script><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td colspan="5" style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - JOAO.xls</td><td style="font-size: 9pt; font-family: caption; color: rgb(255, 255, 255);" align="right">___Running: 12.0 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td colspan="5" style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="middle"><form name="formCb755237"><input name="btCb873980" value="Copy Formula" onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type="button"></form></td></tr></tbody></table></td></tr><tr><td colspan="5" style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" bgcolor="WHITE"><table border="0"><tbody><tr><form name="formFb078704"></form><td style="width: 60px;" align="center" bgcolor="White"><select name="sltNb935705" onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value"><option value="=MODAY!B4">A1</option><option value="=MODAY!C7">B1</option><option value="=MODAY!G7">C1</option><option value="=MODAY!\$G\$4">D1</option><option value="=MODAY!B4">A2</option><option value="=MODAY!C8">B2</option><option value="=MODAY!G8">C2</option><option value="=MODAY!\$G\$4">D2</option><option value="=MODAY!B4">A3</option><option value="=MODAY!C9">B3</option><option value="=MODAY!G9">C3</option><option value="=MODAY!\$G\$4">D3</option><option value="=A3">A4</option><option value="=MODAY!C10">B4</option><option value="=MODAY!G10">C4</option><option value="=MODAY!\$G\$4">D4</option><option value="=A4">A5</option><option value="=MODAY!C11">B5</option><option value="=MODAY!G11">C5</option><option value="=MODAY!\$G\$4">D5</option><option value="=A5">A6</option><option value="=MODAY!C12">B6</option><option value="=MODAY!G12">C6</option><option value="=MODAY!\$G\$4">D6</option><option value="=A6">A7</option><option value="=MODAY!C13">B7</option><option value="=MODAY!G13">C7</option><option value="=MODAY!\$G\$4">D7</option><option value="=A7">A8</option><option value="=MODAY!C14">B8</option><option value="=MODAY!G14">C8</option><option value="=MODAY!\$G\$4">D8</option><option value="=A8">A9</option><option value="=MODAY!C15">B9</option><option value="=MODAY!G15">C9</option><option value="=MODAY!\$G\$4">D9</option><option value="=A9">A10</option><option value="=MODAY!C16">B10</option><option value="=MODAY!G16">C10</option><option value="=MODAY!\$G\$4">D10</option><option value="=A10">A11</option><option value="=MODAY!C17">B11</option><option value="=MODAY!G17">C11</option><option value="=MODAY!\$G\$4">D11</option><option value="=A11">A12</option><option value="=MODAY!C18">B12</option><option value="=MODAY!G18">C12</option><option value="=MODAY!\$G\$4">D12</option><option value="=A12">A13</option><option value="=MODAY!C19">B13</option><option value="=MODAY!G19">C13</option><option value="=MODAY!\$G\$4">D13</option><option value="=A13">A14</option><option value="=MODAY!C20">B14</option><option value="=MODAY!G20">C14</option><option value="=MODAY!\$G\$4">D14</option><option value="=A14">A15</option><option value="=MODAY!C21">B15</option><option value="=MODAY!G21">C15</option><option value="=MODAY!\$G\$4">D15</option><option value="=A15">A16</option><option value="=TUESDAY!C7">B16</option><option value="=TUESDAY!G7">C16</option><option value="=TUESDAY!\$G\$4">D16</option><option value="=A16">A17</option><option value="=TUESDAY!C8">B17</option><option value="=TUESDAY!G8">C17</option><option value="=TUESDAY!\$G\$4">D17</option><option value="=A17">A18</option><option value="=TUESDAY!C9">B18</option><option value="=TUESDAY!G9">C18</option><option value="=TUESDAY!\$G\$4">D18</option><option value="=A18">A19</option><option value="=TUESDAY!C10">B19</option><option value="=TUESDAY!G10">C19</option><option value="=TUESDAY!\$G\$4">D19</option><option value="=A19">A20</option><option value="=TUESDAY!C11">B20</option><option value="=TUESDAY!G11">C20</option><option value="=TUESDAY!\$G\$4">D20</option><option value="=A20">A21</option><option value="=TUESDAY!C12">B21</option><option value="=TUESDAY!G12">C21</option><option value="=TUESDAY!\$G\$4">D21</option><option value="=A21">A22</option><option value="=TUESDAY!C13">B22</option><option value="=TUESDAY!G13">C22</option><option value="=TUESDAY!\$G\$4">D22</option><option value="=A22">A23</option><option value="=TUESDAY!C14">B23</option><option value="=TUESDAY!G14">C23</option><option value="=TUESDAY!\$G\$4">D23</option><option value="=A23">A24</option><option value="=TUESDAY!C15">B24</option><option value="=TUESDAY!G15">C24</option><option value="=TUESDAY!\$G\$4">D24</option><option value="=A24">A25</option><option value="=TUESDAY!C16">B25</option><option value="=TUESDAY!G16">C25</option><option value="=TUESDAY!\$G\$4">D25</option><option value="=A25">A26</option><option value="=TUESDAY!C17">B26</option><option value="=TUESDAY!G17">C26</option><option value="=TUESDAY!\$G\$4">D26</option><option value="=A26">A27</option><option value="=TUESDAY!C18">B27</option><option value="=TUESDAY!G18">C27</option><option value="=TUESDAY!\$G\$4">D27</option><option value="=A27">A28</option><option value="=TUESDAY!C19">B28</option><option value="=TUESDAY!G19">C28</option><option value="=TUESDAY!\$G\$4">D28</option><option value="=A28">A29</option><option value="=TUESDAY!C20">B29</option><option value="=TUESDAY!G20">C29</option><option value="=TUESDAY!\$G\$4">D29</option><option value="=A29">A30</option><option value="=TUESDAY!C21">B30</option><option value="=TUESDAY!G21">C30</option><option value="=TUESDAY!\$G\$4">D30</option><option value="=A30">A31</option><option value="=WEDNESSDAY!C7">B31</option><option value="=WEDNESSDAY!G7">C31</option><option value="=WEDNESSDAY!\$G\$4">D31</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="White"><input name="txbFb426622" size="80" value="=MODAY!B4" type="text"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="center" width="2%">
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>

<center><hr>
<form name="form1"><input value="Please click this button to send the source to clipbord" onclick="CopyToClipBoard();" type="button">
<input value="View Source" onclick="ViewSource();" type="button"></form>
OK STARTING BACKWARDS THIS IS WHERE AL THE DATA FOR THE WEEKLY SHEETS IS COLLECTED

</center>

Last edited:

Replies
2
Views
167
Replies
2
Views
109
Replies
20
Views
618
Replies
13
Views
447
Replies
15
Views
1K

1,203,460
Messages
6,055,553
Members
444,796
Latest member
18ecooley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back