# 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

## Excel 2013 :: Separate Time From Date To Sort Time Frames Over Multiple Days?

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

## Trying To Designate Time Frames

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.

## How Do I Change Time Frames In Cells

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

## Calcualte Averages For Time-frames

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.

## Excel 2010 :: Count Max Time Frames Overlapping On Continuous Days

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.

## Add This Number To The Start Time, Factor In Break Minutes And Get To The Projected Completion Time

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!

## Insert Time Break

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.

## Calculating Time, Potential Lunch Break

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.

## Minus Break Time Minutes Off Working Hours?

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

## Break Time Difference Into Minutes & Military Hours

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.

## Automatic Finish Date & Time With Lunch Break And Bank Holidays

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

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.

## Start/Stop Timer For Hours Worked To Include Break Time

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.

## "Can't Enter Break Mode At This Time"

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)

## Time Scheduling: Take Out A 30 Min Break If The Hours Worked Is Over 6 Hours

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.

## Looping Through Frames

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.

## Disabling Frames By Content

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)

## Simulate Click Through Frames

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.

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>

## Option Buttons To Show Different Frames

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?

## Accessing Web Pages Organised With Frames

Mar 25, 2007

How is the data organised in frames in a web page accessed?

## User Form Frames Hindering Textbox Text

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.

## Excel 2010 :: Control ENTER And EXIT Event Firing With Frames Active?

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.

## Page Break By Asking The Input File For Page Break To Be Done

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))

## Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames

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.

## Break The String

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

## Break Down 1 Sheet Into Several

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?"

## Break Up One Row Into Many Columns

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

## Break All Links

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?

## Break Apart A String

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 ":"

## Break Apart Web Query

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.