I'm busy working on a schedule for calculating irregular hours.
A thing I need to do is the following:
name: Ed
From: 6:30
Till: 15:00
+15% :
Ed is one of the employees working on a busline. If he works between 6:00 and 7:30 he gets +15% on his salary for his worked hours. So what I need to know is how many hours(or minutes) he's worked in that 15% box. (In this example 1 hour)
Need to divide a cell containing a time value (hrs:mins:secs). My timesheets calculates the number of hours on a job and I want to divide this total by the number of components made.
View 7 Replies View RelatedI've found how to present time exceeding a minute in seconds ([m]:ss.00), but I need to formulate items per second. Doing simply CellItem / CellTime won't work. I've tried Minute(CellTime)*60+Second(CellTime), but this leaves out the hundreds of a second.
View 3 Replies View RelatedI am attempting to calculate % utilization. I have number of minutes used formatted in [mm]:ss and number of minutes available formatted as a number. When I divide available by minutes used I get a percentage that is off.
So I have Available 33600 minutes available 4901:20 minutes used.
I'm trying to work out the productivity of something per hour but I'm having a bit of trouble with dividing time in Excel.
Basically I've got
A1 = # of Sales (e.g. 5)
A2 = Total time worked (e.g 07:36:00)
I want C1 to show me how many Sales are made per hour.
I have Excel 97. I'm working on a spreadsheet and I just can't figure out how to create a conditional formula that will look through Column A & B, then add up the related time value (hh:mm) in Column C.
A B C D
Gender Age Start time End Time
Male Adult 23:10 01:45
Female Adult 21:30 03:30
Male Adult 00:15 01:05
Female Juvenile 23:50 00:10
I tried the SUM(IF((A:A="Male")*(B:B="Adult),C:C)) or something like that.
The times seem to have a problem adding up or averaging if the time value exceeds 24:00 hrs. Or if it's computing numbers that some are before and after 24:00 hrs.
Is there some way to create a formula that will result in something like: Of 5 Adult males, the average start time was 23:50 hrs and the average end time was 01:30?
I am developing a report that tracks the efficiency of industrial sorters on
an automated assembly line. The line runs around the clock in three shifts
(1: 7:30am to 3:29pm, 2: 3:30pm to 11:29pm, and 3: 11:30pm to 7:29am), and
collects certain performance indicators, such as units processed. I am
trying to calculate the units processed during each shift on each day. For
example, if a run ran from 2:30 pm to 4:30 pm, and processed 100 units, I
would want 50 units credited to shift 2 and 50 units to shift 3 (we are
assuming a steady rate of processing).
My data arrives from the machine with the following columns (each run is a
separate row):
B|D|E|G|H
Run #| Start Date| Start Time| Units Processed | End date | End Time
I have successfully used the start time to determine the starting shift with
IF statements:
=IF(--E3<0.3125,3,(IF(--E3<0.6458,1,(IF(--E3<0.9792,2,3))))) and a similar
formula to calculate the end shift. This works fine if the run only spans
two shifts within the same day.
My problem is that some of the runs, run across more than one shift and even
across days. For example, a run that ran from 10:30 pm on one day to 4:30 pm
on the next, would overlap 5 shifts and two days. I need to be able to
figure out the proportion of time spent on each shift, and use that to get
the proportion of units processed during each shift on each day.
I am currently thinking of a convoluted series of nested IF statements (and
generating lots of columns to the right of my data for each potential shift
within a run), but this seems inelegant and cumbersome. Is there a more
logical way to approach this? Even more specifically, is there a function
that can pair my start/end dates and times with a set of shift start/end
times and calculate the elapsed time for each shift within a run? Something
with MATCH or VLOOKUP maybe?
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View 2 Replies View Relatedthe vendor has a 21 hr working window; start from 7am and goes until 4am; Mon to Fri.
Here is a scenario:
- i request for a product information from a vendor on 3-Feb-14 8:00am (Monday)
- he replies with all of the product info on 6-Feb-14 12:00pm (Thursday)
can you find the time in above scenario consideration the working window?
Here is another scenario:
- i request for a product information from a vendor on 6-Feb-14 8:00am (Thursday)
- he replies with all of the product info on 11-Feb-14 12:00pm (Tuesday)
- Sat & Sun are days off but keep in mind that my Friday shift ends on sat at 4am so the networdays formula wont work.
I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!
View 4 Replies View RelatedI am looking for a function to convert time given by my computer (Local time) in EST (Eastern Standard Time). We are several users of a same file (with timestamp macros) and all time need to be aligned to one time zone (EST), even if all users are working in different time zone (EST, CST and IST).
View 1 Replies View RelatedConsider this code:
'light eligibility
Dim facb As String
Dim sunset As Variant
[color=green]' check if facility has lights[color]
facb = WorksheetFunction.VLookup(RID, ds, 10, False) 'find facility code
If WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Then 'facility has lights
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False) 'lookup the sunset time based on the record's date
[Code] ......
This code checks the need for lights at a facility.
It first checks to see if the facility even has lights by cross-referencing a value in the record with a facilities database.
If it has lights, it then checks to see if they are needed. If the rental goes past the sunset time, then it needs lights. Sunset is determined by cross-referencing the date value in sheet1! A9, with the sunset database.
If it needs lights, variable lghtson is calculated equal to "sunset"-30 minutes.
As I step through this code:
WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Facility has lights.
Check to see if lights are needed.
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False)
sunset=0.879166666666667 which is 9:06PM. This is a proper value from the lookup.
If rental_end.value > sunset Then
rental_end (value from textbox) = "9:30 pm" , sunset=0.879166666666667. This is true, and Excel accepts it as true ...
lghtson = sunset - 0.5
0.379166666666667 = 0.879166666666667 - 0.5 (9:06 AM)
This is not the value I was looking for. I was looking for 8:34PM (0.856944444444444)
I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.
Note: If the column I already have the date and time inserted before then it should give message record already have date and time.
I am using office 2010.
i am looking to do a table which shows time started, time finished and then a total for hours that day, then that week.
Start 08:00
End 16:00
Total 8 hrs.
How can I get the total to display as 8 hrs? not 08:00? When I change the format to "number" it shows 0.33?
I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!
For example
Start time: 21:00
Finish time: 06:30
Hours worked: 9.50
Start time: 12:30
Finish time: 23:00
Hours worked: 10.50
production01.png
I am trying to figure a way to search for a cell that has a specific date and time range. There are several cell titles pending on the activity. I want to find a cell that has a time ** 7:30-15:30 , 15:31-17:30, 17:31-20:30. The end result is to calculate the activity between those time periods based on the data cells.
Example
If the date searched time field ** the activity ranges is 1635 I need to split the time and credit the activity time in the 730-1530 time and the rest on the 15:31-17:30 time
I have been able to do it on a single labor group based on time alone, but when I try to add the DATE to it my numbers go null. Eventually i will need to add 11 labor groups daily for weeks at a time .
Excel 2007
A client buys 500 minutes of my time. In one week I spend 340 minutes on the account. I'd like a column to show Time purchased (say 500 Minutes) Time spent and a final section showing time left (but showing negative values in red)
I hope I've explained this ok but here is an illustration of what I'm trying to achieve in Excel 2011.
[URL] ...........
I am not sure that I can do this, but here is what I would like to do. I have a worksheet that I initial when I start a job in on cell and then when I finish in another cell. What I's like is to have a macro running in the back ground that will tell me the total elapsed time from when I started to when I finished.
View 5 Replies View RelatedI have a workbook with macro button to run some vba, if i want the button to disable when the user make copies of the workbook (date created is later than original wb saved time) what codes to add in the VBA?
what i assume is to add the time to a cell when the wb is saved, when the wb opens, it check for that cell if the same with the date created, if different, then disable the macro button.
I have a protected worksheet. Users wish to be able to track changes in the input cells. The suggested approach for this is to temporarily disable sheet protection and allow them to change the font color, then protect afterwards. What I would like to do is:
i) check whether they are in an input cell
ii) if so, then prompt the user with the 'Font Color' dialog box
iii) apply the font color selected to the input cell
I'm struggling to find the dialog box I need. I can launch the one to change the interior color, no problem (Application.Dialogs(xlDialogPatterns).Show). But that's no use to me, I just want a color palette that specifically relates to the Font Color
Formula to calculate time allotted minus time used and show the difference in hour and minute.
View 1 Replies View RelatedI have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
I'm trying to look up information in "pi" by entering a time that you want to look up say 1800 or 935 and have a cell that would enter it as todays date with that time so I can use it as a timestamp in "pi"
View 1 Replies View RelatedRefer to the attachment. I am trying to average the data in the Y column, if the times fall into the range between column R and S. I am having trouble with the averageif function. Is there a better way to parse through column W, check if the values fall between the ranges of S and R, and if they do, average the associated values in column Y?
Attached image: Capture.JPG
So I have two dates:
Opening Date: 29/07/2014 13:27
Closing Date: 29/07/2014 14:42
These are formatted in DD/MM/YYYY and HH/MM
I need to subtract the dates and find the time taken from the opening and closing time.
I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.
Any idea about a dynamic sum function that will do this, in combination with VBA?
I am using the count function for attendance tracking of Vacation, Personal Time, & Sick Time. (Example: = COUNTIF($F6:$CQ6, "V")
I need to be able to do half days. I have tried many different formulas/ways to incorporate the half day scenario even without using the count function with no success.
I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.
Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?
I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.
What I need is a formular/vba code that can divide hours into different time ranges. The picture below shows an explanation.
In example 3 there time range analyzed goes from saturday till monday. Here Sunday should get 24 hours and the time range 23-08 for weekdays is at this point 00-08.
I've tried everything I could imagine to get this one working,
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
