Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop
Mar 3, 2009
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.
View 9 Replies
ADVERTISEMENT
Jul 6, 2005
I was looking at a previous post to get a progress bar to work while running
a lengthy macro. Below is the post on how to incorporate the progress bar
into your code.
That works fine expect for one thing. There is a cancel button on the progress bar, and when it is pushed the rest of my code continues running. How do I end my code if I press cancel on the progress bar?
Sub Main
Dim PB as clsProgBar
Set PB = new clsProgBar
'if your subs are being called from a userform, hide the form first using
Me.Hide
With PB
.title = "some title"
.caption1 = "Executing, this may take a while"
.caption2 = "Doing task 1"
.show
doevents...................................................
View 14 Replies
View Related
May 31, 2013
I am trying to extract times from a work schedule to automatically generate a 'time in' / 'time out' chart. Been trying to use the lookup formula amongst others with little success.
See example template attached...Example Sheet.xlsx
View 9 Replies
View Related
Mar 12, 2008
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?
Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.
View 11 Replies
View Related
Jan 11, 2009
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies
View Related
Nov 2, 2008
I have one Excell Book with two work sheets. The 1st sheet (Sheet Name : Data ) contains the data... and in the 2nd sheet (Sheet Name : Rekey) i have a specific form in Rekey sheet and some data in Data sheet. Now i have to copy Rekey sheet into multiple times .... with different sheet names... and the sheet name are in Data sheet from cell A2 to end.....
For e.g. in Data Sheet cell A2 contins work1 and cell A3 contains work2 and A4 contains work3..... and so on....
Rekey sheet contains some form....
I need to paste Rekey sheet multiple times with sheet name work1, work2, work3..... and so on....
View 9 Replies
View Related
Apr 7, 2008
I am creating a 'HelpDesk Issue Logger' and I am trying to calculate the Network Days and Network Hours between two dates with times. I want to know how many business days and hours are between the two days to give me a TURE 'Response Time.'
I have been to several sites and forums looking for the answer, but I have not been able to find a solutions that works for me. Please find attached a 'stripped' down version of my project.
Variables:
- 'Date Received' (H11)
- 'Date Actioned.' (I11)
-'Response Time' (K11)
Constants:
Work Days = Monday to Friday
Work Hrs = 8:30 AM to 5:30 PM (no lunch break)
Public Holidays = (AC13:AC30)
Formats:
Date Received: dd/mm/yyyy hh:mm AM/PM
Date Actioned: dd/mm/yyyy hh:mm AM/PM
Response Time: d - hh:mm
If there is someone out there wiling to put me out of my missery with this one, you will have a friend for life.
View 9 Replies
View Related
Apr 8, 2007
formula to work out a variance between two times
Using the 24hr time format in cell a1 i have a start time of 10:43 and in cell b1
i have an estimated time i think a job should take in this case 30 minutes and in cell c1 i have the actual time that job was finished in this case 11:07 and in cell d1 i have a variance between the two times which in this case would be saving me 6 minutes
View 9 Replies
View Related
Apr 11, 2013
Just like if I put the employee's number and work time for each day,
The excel will accumulate the hours automatically somewhere in the sheet. (I don't want the total hour cover each day's work time)
Is there anyway I can do it?
View 5 Replies
View Related
Sep 14, 2006
i have a few fields with dates in my excel sheet. I also have a field, where i want to automatically insert the business day between two other date-fields! Business date means days without the weekend and public holiday! Is it possible to do that? how?
View 9 Replies
View Related
Apr 15, 2014
I have to calculate employees work hours for overtime.
in the timesheets:
A B C D E
1 ID Date Code Hours PayMethod
2 A123 4/14/14 TRN 20:00 Regular
3 A123 4/14/14 TTT 15:00 Regular
4 A123 4/14/14 TRN 13:00 <----- total for cell D2,D3, and D4 is more than 40
A B C D E
1 ID Date Code Hours PayMethod
2 A123 4/14/14 TRN 20:00 Regular
3 A123 4/14/14 TTT 15:00 Regular
4 A123 4/14/14 TRN 5:00 Regular <----- to make 40
5 A123 4/14/14 TRT 8:00 Overtime <----- 13-5=8, so i have to write down 8 here for overtime
How can I do this?
I want to make a command button for macro to perform this.
View 2 Replies
View Related
Aug 24, 2009
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
View 8 Replies
View Related
Feb 13, 2010
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies
View Related
Aug 2, 2014
I have been playing around with some data and can't seem to get it the way I want it. I have played around with Pivot tables and grouping but I can't seem to figure out how to accomplish what I need in Excel. To better explain I have attached some test data of what I am trying to accomplish.
View 14 Replies
View Related
Mar 8, 2013
I need also to calculate difference between dates(dd-mm-aaaa hh:mm) in workhours ( hh:mm):
The work period is 9-18 with lunch interval 13-14 The startdate and end date could be out of the work hours and i can't include the extra hours. I can have several days (workdays) at the difference, but i should maintain the format hh:mm.
Ex1:
Startdate 05-03-2013 18:34 ( date to calculation should be 05-03-2013 18:00)
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
will be equal to:
Startdate 06-03-2013 8:34 ( date to calculation sould be 06-03-2013 9:00 )
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
Ex2:
Startdate 06-03-2013 12:01
End date 06-03-2013 14:28
Time Difference 01:29
View 3 Replies
View Related
Dec 27, 2013
I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.
View 3 Replies
View Related
Feb 28, 2013
I can calculate total hours when a user enters a start date/time and a finish date/time. The kick is I only want to include hours from 2:00 PM to 12:00 AM (10 hour period). So assuming all the start and end times will be in this range, how can i calculate work hours over multiple days? For example: Start date/time = 2/26/13 2:30 PM and end date/time = 2/28/13 10:30 PM. I want my calculated hours to show 28 hours.
View 1 Replies
View Related
Jul 21, 2014
I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.
Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.
For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.
here is the distribution of columns per week as reference.
(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns
(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns
View 1 Replies
View Related
Aug 21, 2008
I am trying to figure out a way to count the minutes used from 8am-5pm Monday-Friday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.
View 2 Replies
View Related
Feb 10, 2009
I am trying to create a worksheet which can calculate the Outstanding Work Estimation based on available information.
The information available with me is presented in Rows 3, 4, 5, and 6. For these types of work orders, I have information regarding their Previous Outstanding Status, New Orders, Completed Orders, and at presented Unworked Orders (or Outstanding orders at the end of Jan 2008).
Second set of information is available in rows 9 to 23. This information provides for which type of orders were completed by Staff 1 and Staff 2.
Based on these two sets of information, I am trying to get to the point where I can calculate the following:
a) Applying the weight of orders (namely 1A, 1B, 1C and Blend) by using the drop down list function in column B, I should be able to get the results in Column C (C25 onwards till C40), Column D (D25 onwards till D40), and Column E (E25 onwards till E40). Simlarly for all other months...for February, March, and so on...till December 08.
View 2 Replies
View Related
Jul 16, 2009
I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
View 2 Replies
View Related
Dec 29, 2009
I have a range of cells in a work sheet "sheet 1 " my objective is to filter this range according to certain criteria (i ve succeeded to do this ) yet what i want to do now is copy this data to another existing worksheet in a certain range .
note :the existing worksheet to which i 'll copy the filtered data has some cells out of the range that i dont want to over write ..
Simply :how to copy a selected range of cells in a work sheet to already existing work sheet in a specific range aswell .
View 9 Replies
View Related
Apr 11, 2008
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
View 8 Replies
View Related
Jan 2, 2009
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
View 2 Replies
View Related
Sep 20, 2009
I have 3 spreadsheets I combined into one,
They are voters for 3 separate occasions. I would like to filter out how many people have voted 2 times or 3. I have sorted the information with 2 columns "LASTNAME" and "FIRSTNAME"
Example:
LASTNAME FIRSTNAME
ABBOTTJANET
ABBOTTSTUART
ABBOTTJANET
ABBOTTSTUART
ABBOTTJANET
ABBOTTSTUART
These first 3 show up 3 times, so I would like to take all the info from row one and two to get all the info for Janet and Stu,
View 2 Replies
View Related
May 17, 2014
I found this formula similar to other formula
NETWORKDAYS(D1,F1)-1)*"18:00"-"9:00")+IF(NETWORKDAYS(F1,F1),MEDIAN(G1,"9:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS(D1,D1)*E1,"9:00","18:00")
By the way ....D1 = start Date
F1 = End Date
E1 = start Time
G1 = End Time
It's GREAT! But now I need include time from 6:00 a. m. to 10:00 p. m. (I already modified your formula:
(NETWORKDAYS(D1,F1)-1)*("22:00"-"6:00")+IF(NETWORKDAYS(F1,F1),MEDIAN(G1,"6:00","22:00"),"22:00")-MEDIAN(NETWORKDAYS(D1,D1)*E1,"6:00","22:00")) because are regular working hours in Colombia but:
1. All the days (Monday to Saturday) are working days
2. I just want to calculate the time in shifts, I don't specify the start date and end date. Example: 8:00 p.m. to 11:00 p.m. Result: 2 hours, because 1 hour is after 10:00 p.m.
This are two different options, so I need two separate formulas.
View 10 Replies
View Related
Apr 11, 2013
I need a formula that will search between dates and time to identify if the person will be present at the conference breakfasts, lunches and dinners.
Therefore the cell needs to look if the current date falls within the arrival and departure date and also if the time within eating times falls outside the arrival and departure times.
View 1 Replies
View Related
Aug 14, 2006
In the demographics sheet, I have ages listed from row F2 to F31 with different ages. I would like to get assistance with a formula that calculates the percentage of people within these age ranges:
21-25
26-30
31-35
36-40
41-50
51-59
60+
It should be separate formulas. I'm sure if I'm given the first and last ones that I could do the others myself. Also, if I needed to know the percent of males and females, would i use the same formula?
View 9 Replies
View Related
Apr 8, 2014
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
View 7 Replies
View Related
Feb 1, 2009
Following code has 2 IF statements , the code runs the way it is and the first IF works perfectly, but the second IF does not work.
View 3 Replies
View Related
