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'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 Relatedproduction01.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
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.
My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.
I'd like to convert from 24 hour standard time to 12 hour time using VBA code. For example: instead of 13:00 I need 1:00.
View 9 Replies View RelatedI have a list of FLIGHT departure times that are listed in MIL TIME, however, there is no : in the format. Its just 4 or 3 digit numbers. I need to convert these to time in 12-hour clock. If I go to FORMAT/CELL/TIME and select 1:30pm it simply makes the time ZERO!
View 9 Replies View RelatedNeed 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 RelatedFormula to calculate time allotted minus time used and show the difference in hour and minute.
View 1 Replies View RelatedI have a total at the bottom of my spreadsheet and I want to distribute that total equaly to the missing columns for each month and each product highlighted in green in the spreadsheet. What formula do I need to use to be able to do that?
At the end I want the numbers in each month to total the total at the bottom.
I 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.
In A1 I have 35 min elapsed time and need to change it to time as a portion of an hour. It reads the 35 min as TIME, therefore I am currently using
=IF(A1>=0.5,A1-0.5,A1)*24. Output is .58, which is perfect.
(output column formatted as a number)
Likewise 3:28 becomes 3.47.
A given string of some letters (from three to five, six) is followed by a single number (1-4). Then it ends, or is followed by another such combination, up to a maximum of, let's say, four.
Example:
xxxx1
yyy3yyyy1
xyzxx1yxz4xzx1
xxx2xxx2xxx2xxx2
What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:
xxxx1
yyy3 and yyyy1
xyzxx1 and yxz4 and xzx1
... and so on
Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?
Does anybody know the code or the formula to divide a number generated by a formula. Id like to divide this number by 2 different set numbers. Meaning if my number comes out ot 19, id like to divide this by 5.5 and if it's not a whole number which this isn't (it's 3.45), i'd like to round this number to a whole number and add 2.5. My end answer would be 3 5.5 and 1 2.5.
Windows XP
Excel 2003
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 need a formula to calculate the time (in hours/fractions of an hour) that is "covered" for each hour of the day between a range of times. In other words, I have a "START TIME" and an "END TIME" and for each hour of the 24-hour clock, I want to know how much time this range covers.
For example, if my start time is 3:30am and my end time is 5:15am, for the 3am hour, the formula would return 0.5 hours, for the 4am hour it would return 1.0 hours, for the 5am hour it would return 0.25 hours, and for all other hours it would return 0.0 hours. The range of START TIME and END TIME can be up to 24 hours (but not more), but the tricky part is that the START TIME can be on the day BEFORE the END TIME (e.g., START TIME of 10:35pm and END TIME of 5:45pm the next day).
I found a formula for calculating time in the HH:MM AM/PM
=IF(B1<A1,B1+1,B1)-A1
This formula was to give me total hours in the cell for which it is entered, and cell format for the formula was in military format.
I cannot find this post. The formula worked in OpenOffice Calc program, but when it came to converting to excel, I came up with #value!
I want to enter the time in 12 hour format and using am/pm to designate. I am making it for someone to make work schedules with and they do not know military time.
I have a txt file imported into excel that's slightly strange.
But once into excel, I need to edit some numbers.
The values in cell B 23 is 1.
So is the value in B 28, so is the value in B 33, B38, B 43 and so forth ad infinitum... Or so it feels.
I need to edit all those values to 0.
I am using excel 2007 or 2010.
I need to divide the numbers on sheet 1 column D. This column contains numbers and blank cells so I need to divide by the actual number of cells that contain only numbers. I will be referencing the divided numbers on sheet 2 in a monthly summary format.
I have dollar amounts that I need to distribute amongst varying numbers of columns and not have have the total distributed be over or under the original amount by any number of cents. When I simply divide the dollar amount by the number of columns, the total of those columns can sometimes be more or less than the original dollar amount by a few cents.
e.g.
Dollar # of A B C All Columns
Amount Columns Total
--------------------------------------------------------------------
$25.05 2 $12.53 $12.53 $25.06
$11.47 3 $ 3.82 $ 3.82 $ 3.82 $11.46
$25.05 divided into 2 columns gives $12.53 in Column A and $12.53 in Column B. Total of Columns A and B is $25.06. Over by a penny.
$11.47 divided into 3 columns gives $3.82 in Columns A through C. Total of Columns A through C is $41.46. Under by a penny.
I know I could simply always add or take away the pennies from one column, but I would prefer the process to be random or formulated in such a way that the Column to which the extra pennies are added to or taken away from differs in order to be "fair to each column".
I have a start time in cell A1 (say 9am entered as 900), cell B1 has a time interval (25min), Cell C1 gives total (925). Cell B2 has the next time interval (56min). How do I get cell C2 to give total of 1021 rather than 981? Values in columns B and C continue on down.
View 3 Replies View RelatedI have a timesheet which for certain days of the year calculates double-time. What i would like to do is have a formula that gives double time for a 24 hour period. I have a formula setup with a start time and end time for my double time, but it wont stop at midnight.
View 11 Replies View RelatedI get data in the form of an .xls worksheet that includes a date column and an "hour" column. The hour column contains 1-2 digits representing the hour of the day (0-23). I need to be able to combine the date and time fields for graphing, searching, and sorting the data. No matter what I try, I can't get excel to recognize the hour as the time of day (e.g. 2 = 2:00am, 16 = 4:00PM, etc). The data sets tend to be large and I get new data in the form of new .xls files often, so a script to convert the data is probably not a good solution.
View 4 Replies View RelatedI have a bunch of data and I want to be able to count the number of entries that fall within each of the 24 hour increments in a 24 hour clock. (military time)
For 12:00:00 all times would be between and including 12:00:00 and 12:59:59
Column B | Count
------------------
12:00:00 344
13:00:00 44
14:00:00 5
Calculate certain time increments for various work-shifts. I have a start time,finish time and increments of time across the spectrum of 24 hours. There are also multiple start time across the 24 hour period with some start times begining on one day and ending on the next day.
Example
In B5 Startime is 22:00
In C5 Finishtime is 06:30
In I3 increment begins at 00:00
In I4 increment ends at 00:30
The employee working the shift from 22:00 - 06:30 would fall into the time increment of 00:00 - 00:30 where another employee working a different shift (08:30 - 17:00) would not. I'm looking for a formula that would return a 1 in a cell if the employee fell into the 00:00 - 00:30 time increment and a 0 in a cell in the employee did not fall into the time increment.
I am creating a Little League Schedule and want to start Saturday games and have the lower cells ad 2hr 45min to whatever time I post in the upper cell
View 4 Replies View RelatedI've calculated the time of hours worked by staff using th 24 hour clock - "hh:mm" format.
When summing up the hours, it goes a bit haywire, i.e.
Staff A = 12:30
Staff B = 14:00
Sum = 02:30
Total should equal 26:30 (26 and a half hours), however I get returned 02:30. I assume it is down to the 24 hour clock and I so I tried adding 24 to it in the formula bar to get the right answer and it didn't work.
I have a spreadsheet that users input the temperature each hour, for a 24 hour period. The time used is 24hr clock, as example, cell A1 will be 03:00, and cell B1 would be the forecasted temperature.
On another sheet, the user inputs the temperature for the specific time a airplane is going to take off, i:e: 07:32. Is there a way I can have the second sheet look back to the first sheet and grab the temperature for the correct time (using the hour block it falls into). The first sheet time blocks are every whole hour, i.e 08:00, but on the second sheet, the time could be any hour/minute, 07:32.
If I had a cell containing a time, 06:30 in A1 for example, how would I use VBA in the following argument:
If the hour in A1
I have a ton of time in the above format (hh:mm.*) that needs to be converted to a 12 hour format (2:15 PM). I'm not wonderful with formulas, but if it's explained "simplistic" enough, I can probably get it. Please help - I've googled this to death every possible way. I can go in an manually change 19:37.5 to 7:37 pm - but it will take me FOREVER!
View 9 Replies View Related