Break Down Time Frames
May 8, 2008
I have a spreadsheet with agents schedules. What I would like to do is to break down the schedules to determine how many hours are worked between different times. This is what I have: C7:C11 is Sunday Start Times D7:D11 is Sunday's End times. The sheet does Sun-Sat. so Saturday ends at Q11. I want to find out how many agents work from 6am-10am, 10am-12pm, 12pm-2pm, 2pm-4pm, 4pm-6pm, 6pm-8pm, etc. I was originally trying to use a sumproduct but had some problems getting it to work. This is what I was trying: sumproduct(--(C33:C40>=(0,0,0)),--(C33:C40
View 9 Replies
ADVERTISEMENT
Jun 17, 2014
I 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
View 1 Replies
View Related
Mar 10, 2005
I'm trying to designate if a worker has worked over night and specifically between the hours 22:00 and 06:00.
In Greece these hours are been managed as extra benefit, so when I enter the worker's In (C5) and Out (D5) hours I wan't it to bring me in a cell the total hours he has worked in that specific time zone.
View 9 Replies
View Related
Oct 12, 2009
I have a column that has time ranges in it. Example: Column N Row 6 thru
253 has the following time ranges: 11am-1pm, 1pm-3pm, 12pm-5pm, 8am-12pm
I need cells with 11am-1pm to read 13:00
I need cells with 1pm-3pm to read 15:00
I need cells with 12pm-5pm to read 17:00
Is there a formula that I can use the look up the entire column and make all the changes at once, or do I have to use the find a replace. The correct format can appear in a different column.
** I found a reply for someone one a different website with the similar problem. Below is the response but it did not work for me.
Try this formula =--SUBSTITUTE(SUBSTITUTE(MID(A6,FIND("-",A6)+1,11),"pm"," pm"),"am"," am")
Also change format to show 13:30 timeframe
View 14 Replies
View Related
May 19, 2007
I need to calcualte averages for time-frames, of 7 days 30 days and 90 days. Attached is a spreadsheet and the data I am looking to average. I need to lookup todays date minus 1 and then average from there.
View 6 Replies
View Related
Aug 1, 2013
I'm having trouble defining the time overlaps on continuous days for the varying results of the runs for my simulation.
For the following data set, without counting overlaps there are 18 total spare requirements, but if I take into account their overlaps in their duration there should be a total of 11 per day. For example in row 4 this event overlapped in the same day with the event of row 5 so both had only 2 overlaps as a result (So there is not possible to be 1 as an answer for overlap, there is only from 2 to "n" amount or 0).
But if an event overlaps with more than one in their duration the result would be their maximum overlap during this time frame.
So then I have for each event an Starting date, starting time, duration, ending date and ending duration.And for each day I would need the total amount of overlaps occurred for the duration of each event and from those I would take their maximum amount of overlaps occurred per day. To obtain their real requirement as shown on the file.
The manual procedure I'm using looks like this for the first day: (Graphical representation)
Event 1 occurs from 17:02 to 20:29 and only overlaps with Event 3, so it's 2
Event 2 occurs from 20:38 to 23:38, also only overlaps with Event 3, then its also a 2
For Event 3 in its whole duration only overlapped with Even 1 and Event 2, one at a time so its maximum overlaps where only 2.
And that's what I'm using so far to determine these amounts but since is a simulation, I would end up with several data from each run and this method would be too long. That's why I would need this template to paste data for each run (15 days) and obtain my results faster.
View 1 Replies
View Related
Mar 3, 2009
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 Related
Jul 26, 2008
Is there a way to insert a line of code that, once all workbook open code has completed, will count for 5 seconds and then perform another piece of code?
example,
Once my workbook opens - i would like a message box to appear 5 seconds after.
View 9 Replies
View Related
Oct 8, 2008
I am creating a worksheet where employees schedule work throughout the day
eg task 1 will be start time 9am, finish time 11am. The result is 2 hours to do the task.
The problem i'm having is that I need to do a calculation that if a task includes lunch then the result takes an hour away from the task time.
eg task 2 will be start time 12pm, finish time 3pm, excel displays 3 hours, i need it to say 2 hours because 1-2pm is contractual lunch.
I can't just do a day formula that says minus 1 from total as this is task orientated and will vary depending on start and finish times of each task.
View 8 Replies
View Related
Jun 15, 2014
basically I am trying to make a time calculator for work, I want it to automatically add the hours up but then also minus break times and then give me a whole paid total hours worked that week.
CALCULATOR.xlsx
View 1 Replies
View Related
Sep 14, 2007
if someone called me at 4:55pm and ended the call at 5:10pm, the whole call lasted 15 minutes. So, I want to show on excel that the call lasted 5 minutes in the 16th hour of the day (4:55pm) and 10 minutes in the 17th hour of the day (5:10pm), for a total of 15 minutes.
View 3 Replies
View Related
Feb 13, 2010
I would like a solution for the automatic calculation of the end date and end time for project tasks. I have already spent hours on the issue, thanks for any help on this.
The parameters are:
A1 = Start time 08:00
B1 = End Time 17:00
A2 = break lunch 12:00
B2 = back from lunch 13:00
Task parameters
A5 = start date 01/03/10 (entered manually)
B5 = start time 10:00 (entered manually)
C5 = duration 02:00 (hrs entered manually)
D5 = "end date" >>> (to be calculated exluding breaks and holidays)
E5 = "end time" >>> (to be calculated exluding breaks and holidays)
the next line should be filled in automaitically according to the hours needed and the previous end date & time
A6 = "start date" >>> (after line 5: to be calculated exluding breaks and holidays)
B6 = "start time" (after line 5: to be calculated exluding breaks and holidays)
C6 = duration 14:00 (entered manually)
D6 = "end date" >>> (to be calculated exluding breaks and holidays)
E6 = "end time" >>> (to be calculated exluding breaks and holidays)
and so on for every new line down.
View 9 Replies
View Related
Oct 15, 2007
I have a timesheet where user updates start and end time for various tasks.
I have placed a time capture button in the excel sheet (which is simply a macro saying =now() function)
The user clicks it before starting and after finishing the task. The start and end times are captured in adjacent cells.
If the user starts the work, and goes on a lunch break say for 20 min, comes back finishes the task and captures end time, the time difference will not consider break time which is non productive.
How can I incorporate something like 'pause' option so that before he goes for lunch he can temporarily pause the time.
View 9 Replies
View Related
Apr 8, 2009
why I get a "Can't enter break mode at this time" message when stepping through the following code (using the F8 key) at the 'With NewButton' line?
(This is code from a Walkenbach book, and it runs fine if I don't step through it. I'm using Excel 2007 btw)
View 5 Replies
View Related
Jan 13, 2009
I am making a schedule and I would like it to take out a 30 min break if the hours worked is over 6 hours.
I have so far
A B
1 11:00 7:30
=24*(B1-A1)
Gives me 8 hours, I would like it to subtract the 30 minutes only ifthe sum is over 6 and not alter the sum if it is under 6.
View 9 Replies
View Related
Jan 27, 2010
I'm wanting to loop through the first 13 Frames of a Userform and think the below code will do what I want, but I have been unable to figure out how to set cFrame.
View 5 Replies
View Related
Jan 25, 2010
I have a UserForm with lots of controls divided by multiple frames. (163 controls over 19 frames). The same UserForm will be collecting multiple sets of data but the first set is a "master" set. Anything entered in the master set will auto fill in subsequent sets.
What I need is a way of preventing the user from changing any options that were input in the master set in subsequent sets. ie, If I entered info into Frame1 of the master set it would show up the next time I opened the UserForm (I have this part), but I would not be able to interact with it (this is what I need).
Since I'm already pre-populating subsequent sets with the master info, my first thought is to loop through each frame testing for input and if so setting Enabled = False, but I'm not sure how to do the loop/test. Is there a way to set up a "For Each Frame in UserForm" statement? Also, the controls in each Frame could be TextBox, OptionButton, and/or CheckBox. (sometimes more than one type in the same Frame)
View 2 Replies
View Related
Jun 17, 2008
I think this is going to be the right method for me. I need to simulate a click on a link (because the link calls a javascript), though the link is buried behind frames.
Here's the code I am sure works for a normal 1 frame page.
appIE.Document.all(0).contentWindow.Document.all("Some Link").Click
Here is the frame setup I have to work with. Mind you the frames I need to target are not labeled so I'd have to use the numbers.
HTML <FRAMESET>
<FRAME>
<FRAMESET>
<FRAME>
<FRAMESET>
<FRAME> This is where my link is hiding
</FRAMESET>
</FRAMESET>
<NOFRAMES>
</.NOFRAMES>
</FRAMESET>
View 9 Replies
View Related
Oct 6, 2008
I have been looking on here for a solution, but did not find the right answer, so I hope someone can help me with this.
What I like to do is on a userform set up a "frameoptions" with two option buttons and depanding on what option I click either frame A shows or frame B shows.
When I click on "option" one it shows "frame 1" and if I click on "option two" it should show "frame two". both frames I would like to have on the exact same location of the userform. So the look of the userform stays the same.
Is there a solution to this?
View 9 Replies
View Related
Mar 25, 2007
How is the data organised in frames in a web page accessed?
View 2 Replies
View Related
Oct 21, 2009
I have a question regarding frames and textboxes on a user form.
I have a user form that has a number of textboxes populated from the results two Comboboxes.
I decided to group some of the textboxes in frames for more logical viewing.
I grouped the textboxes, moved them, placed two frame controls on the form then, finished by replacing the grouped textboxes. No code was altered or added.
When I ran the form, only the Comboboxes were functional and the text boxes were blank.
There were no runtime errors or compile problems.
View 5 Replies
View Related
Oct 22, 2011
I'm having some trouble getting control ENTER & EXIT events to fire properly when having controls embedded on frames within a userform. I'm using Excel 2003, 2007, & 2010. Here's the userforms I'm working with:
With FRAME:
Without FRAME:
In both cases, the DESCRIPTION field is disabled. The selectable controls on both are a combo-box, textbox, listbox, & 2 buttons. On the FRAMED version, the combo-box & textbox are contained on a FRAME.
Here's the code, same on both userforms:
Code:
Option Explicit
Private Sub cmbRecipes_Enter()
ListBox1.AddItem "ENTER - " & cmbRecipes.Value
End Sub
Private Sub cmbRecipes_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ListBox1.AddItem "EXIT - " & cmbRecipes.Value
End Sub
All this is doing is posting a message to the listbox when the combo-box ENTER & EXIT events fire. This works as expected without the FRAME, ENTER is shown when the combo-box is entered and EXIT is shown as focus is moved to another control. But when running it on the FRAMED version all I get is a single ENTER event recorded regardless of how I move the focus through the control set.
Another oddity is that if I have more than 1 control that can receive focus on the FRAMED version, it appears to work correctly.
View 6 Replies
View Related
Dec 9, 2008
The code which you provided works fine no problem for a page break. I need to run the macro for the page break by asking the input file for page break to be done.
For Example, If excel filename "A" contain the code which you have given need to ask to input the filename "B" and process need to be done in file "B".
I have added some code to your code which you provided but it gives error message "1004" "Method 'Range' of object '_Application' failed" at following line :
Set rng = oExcel.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
View 12 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
Dec 7, 2009
how to break the string into 3 variables.
Example: Given string is : -4.98e-005x^2+0.368x+0.0588
All the string there will be two + symbols.
I have to break the above string into 3 parts and store it into variables like
a=-4.98e-005x^2
b=0.368x
c=0.0588
View 3 Replies
View Related
Jan 2, 2013
I need a macro that can take a sheet containing million rows and break it down into multiple sheets within the same workbork beginning on sheet 2 (source data on sheet 1). I would like the macro to include a input box that appears when the macro is run that asks "how many rows per sheet?"
View 2 Replies
View Related
Mar 15, 2009
I am herewith enclosing the sample data, which is one row, containing the branch name, branch code, itemcode, rate, qty, amount . Each branch has several items with different quantity of various rates, which has come in the horizontal line. Now I want that in one by one, for converting that data into oracle.
BR.NAME BR.CODEITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTxx1104.5100450153250750263.25158513.5394.5180810506.55003250result should be like thisxx1104.5100450xx2153250750xx3263.25158513.5xx4394.5180810xx5506.55003250
View 9 Replies
View Related
Jun 26, 2009
Is there a simple code I can add to the end of my macro that will break all existing links in the active workbook?
View 9 Replies
View Related
Nov 15, 2009
breaking apart a string in a cell.
in cells A6:A112 my trial balance export from QB Online is account # [SPACE] Account name:Sub Account:Sub Account
eg. 70160 Administrative Expense:Travel & Entertainment:Travel & Ent.-Travel & Lodging
I would like to break the string apart so I have only account # in column "a" and the right most sub account name in column "b". So if I use my example from above, I would have 70160 in "a" & Travel & Ent.-Travel & Lodging in "b". Note all the account name & sub accounts are separated by ":"
View 9 Replies
View Related
Dec 1, 2006
I have managed to capture a table from a website and currently have it going to range"a1"
Set mytable = IeApp.document.getElementById("maindetail")
Sheets("sheet1").Activate
Sheets("sheet1").Range("b1").Value = IeApp.document.getElementById("maindetail").innerHTML
Application. ScreenUpdating = True
What Id like to do is breakapart this data, remove the html tags, and place each element of the table in its own cell (a1, a2,a3 etc.). Anybody have any ideas, I'm just not familiar enough with the IE DOM.
View 9 Replies
View Related
