Calculating Break Between Duty

Mar 20, 2007

i have a formula on my worksheet that tells me how many hours break i have between my finish time on say monday and my start time on tuesday, now..ive added another column but in this one i need it show either a 9 or 11. so if i have lets say 10.75 hours break or less between duty then i need the cell to show 9 however if i have 11 or more than 11 i need it to show 11. here is the code that i have at the moment

=IF(AND(ISBLANK(D10),ISBLANK(E10)),"",IF(24*(IF(E10>D11,D11+1-E10,D11-E10))=0,24,24*(IF(E10>D11,D11+1-E10,D11-E10))))

View 9 Replies


ADVERTISEMENT

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.

View 8 Replies View Related

Formula To Calculate Pilot Duty Time In 24hrs

Jun 8, 2014

I am a flight dispatcher who is having difficulty with a Flight Duty Period tracking sheet. I need a formula which gives me the total duty time of a pilot in one single day, which is calculated by using the DUTY START time of first flight and DUTY END time of last flight in the same day.

I have attached the excel file. Please download file and open the FDP tab from the file and goto cell AI7.

Basically I need a formula in cell AI7, which checks up the date on column D and selects a date range of the same date in column D (in this case D7:D8) and then give the difference between the Duty Time START of FIRST FLIGHT (column AF) and Duty Time END of the LAST FLIGHT (column AG).

Ex: 12:05-9:20 =2:45

Please note that I want the formula to automatically check the cells in column D for the dates, not manually select the dates, because pilots do more than one flight per day sometimes and it is never same. Formula than should be able to calculate the difference between the largest number in column AG and lowest number in column AF in the same date range mentioned before.

Uneven number of flights on different days is making it complicated for me.

View 2 Replies View Related

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

View 12 Replies View Related

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

View 3 Replies View Related

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

View 2 Replies View Related

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

View 9 Replies View Related

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?

View 9 Replies View Related

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

View 9 Replies View Related

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.

View 9 Replies View Related

Break The Link Between Tables?

Apr 28, 2014

I copied a worksheet with a pivot table as a new worksheet. I wanted to group each pivot table differently on each worksheet, but the changes I make on one worksheet is reflected on the other. Is there a way to break the "link" between these tables?

View 2 Replies View Related

How To Break Text In Two Cell

May 3, 2014

in COLUMN A i have text in one cell i need break this text in two cell COLUMN B and COLUMN C

[Code] ......

View 2 Replies View Related

Changing The Page Break ?

Jan 15, 2009

I have a workbook that is not showing everything that I would like it to on one page (but I know that all the information would fit onto one page (width) but I cannot seem to be able to change the page break. I tried following the directions in the "excel help" but just can't seem to get it to work. I have attached a sample workbook so that you can see what I mean.

View 2 Replies View Related

Border In Each Page Break

Jun 9, 2009

Bottom border each page,

View 9 Replies View Related

Automatic Page Break

Feb 3, 2010

I have a worksheet where the print area is 16 columns and ~90 rows. have it set to print on one page in page set-up, which generally scales to about 80%, and this has worked fine for years. A couple of months ago I was traveling for work, but got a call that the worksheet was printing each cell as a separate page. My co-worker messed around with it and it seemed to fix when she cleared and reset the print area, and has been working fine ever since.

Today it popped up again. It is setting automatic page breaks every few cells so page 1 is A1:A7, page 2 is A8:A11, etc...Both Print Preview and actually printing a page show me my few cells in the lower right hand corner of the page. Viewing the page break preview shows that they are all automatic page breaks, and if I try to move them I get the error that Microsoft Office Excel cannot move the page break because doing so would reduce the scale below the minimum 10%.

As I mentioned I have it set to "fit to" 1 page high and 1 page wide, which it right now is saying sets the scale to 10%. If I instead choose the scaling to be "adjust to" and set to 100% it only wants to take up 4 pages, but if I try to manually drag the break over afew columns it reverts back to wanting to print every few cells.

View 5 Replies View Related

Multiple Page Break

Dec 8, 2008

to have a page break by grouping id together, Heading need to be printed on each page. I have attached the sample file.

eg. the ID.

AAAAW0250H
AAAAW0250H
AAAAW0250H
AAAAW0250H
AAAAW0250H
AAAAW0250H
---- need page break ---
AAACA0314L
AAACA0314L
AAACA0314L
AAACA0314L
................
................
................

View 13 Replies View Related

Line Break Not Working?

Jan 16, 2009

I have this code that is to long for the vba window so I read around and found I had to add a space and _ to extend the line.For some reason I get an error in the code when I do that?

However I copied the formula right from the working cell?

View 5 Replies View Related

Break Points Not Working

Oct 6, 2009

I have a spreadsheet that I have been using for some time.
Suddenly, in just the Worksheet_change event procedure, break points will not work.
In the same sheet break points do work for other events.
I've tried everything.

View 12 Replies View Related

Break Down How Many Days In Each Month

Mar 3, 2009

I have is a start rent date and an end rent date. They want to break down how many days in each month the item was on rent so they have a column for each month. For example say an item was rented on 12/14/08 and returned on 1/12/09. It was rented for a total of 30days 18 days in December and 12 days in January. So I would need a function in each column that would return a 14 in the December Column and a 12 in the January Column. there are over 350 rows and they are doing the process manually right now and taking several days.

Also I just noticed that the date range is currently entered as a text in the format 2008-12-14 so YYYY-MM-DD. Don't know if the text will be a problem.

Just attached an expample. In it I am trying to find a function to fill in the green cells.

View 5 Replies View Related

How To Set Page Break In File

Feb 16, 2012

I need to set the page break in my excel file. Basically what I need is for vba to check if the page breaks are a certain value (in this case Range("A150"). If they are not, then the code will set them to what I need.

The problem is the code is not working.

Code:

If ActiveSheet.HPageBreaks(3).Location Range("A150") Then
Set ActiveSheet.HPageBreaks(3).Location = Range("A150")
End If

View 4 Replies View Related

Break Out Information From Cell?

Jun 19, 2012

I have copied from an email a very long list of emails but they all end up in one single cell

sample: eyee@gruposhahani.com; laroyeimport@gmail.com; sjimenez@vsjimenez.com; yhwh_shekhinah@hotmail.com; sparcells@formatec.com.pa

and would like to know if there is a way I can quickly create a list separating each email into a different cell. I was thinking of using a MID formula or maybe an INDEX or MATCH, but cannot figure out how to make it work.

View 3 Replies View Related

VBA Break All Links Syntax?

Jun 24, 2014

I'm looking for syntax to break all links in a workbook using VBA. I've found the below through independent research but I'm receiving a type mismatch error.

Code:
Sub BreakLinks()
'Macro Purpose: Break all links in the active workbook
Dim vLinks As Variant

[Code].....

View 1 Replies View Related

Break An Infinte Loop

Dec 30, 2006

Taking the following as an example, where you find yourself in an infinite loop. How can you interrupt the sub and keep the code. All because you where to stupid to save it before running it.

Sub TEST()

LabelA:

****MsgBox "YOU IDIOT"

****GoTo LabelA

End Sub

View 9 Replies View Related

Page Break @ Every Change In Value

Mar 4, 2008

I would like to put a page break everytime a column value changes. Example:

A
Atlanta
Atlanta
Atlanta
Page Break
Boston
Boston
Page Break
Chicago
Chicago

View 9 Replies View Related

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 View Related

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.

View 9 Replies View Related

Page Break Every 32 Rows

Feb 20, 2009

I have macros that then run through and hide all unnecessary questions and paste the selection to another work sheet.
what i need next is the issue.. Because the questions vary in size ( row height) and include blank cells for comments, i can not come up with a macro to print with any conformity

What i have come up with is that after all this is said and done, i would like a macro to print only 32 rows per sheet ( portrait) , then have a page break.

View 9 Replies View Related

Multiple Links - To Break Just One

Jun 3, 2009

I've got a file that is currently linked to two other files. The links are summed in the cell as below:

='[Book1.xls]Funding and Interest'!D$17+'[Book2.xls]Funding and Interest'!D$17...and so on.

I want to get rid of the link to Book1, so I'd zeroed the values in Book1 and my plan was then to Break the links to this, leaving zeros and the links that I want, but because both files are linked in the same cell (the same nested SUM), when I go to Edit, Break Links, it breaks the links to both files, even though I only select one form the list.

View 9 Replies View Related

Force A Page Break In VBA

Dec 9, 2009

I have a report which is produced by a macro, it works well. The report grows over the year to maybe 20 printable pages, landscape, and nicely readable.

Summary information is produced alongside the report over more columns than can be diplayed, so I have copied the summary data as a picture and pasted it below the main printout and adjusted the size of the "photo" to fit the page width.

I would like this photo to always appear on a new page of its own - not just tagged onto the pages before it.

View 9 Replies View Related

Scoring To Break Tie (i.e. Golf)

Oct 7, 2006

I couldn't find a solution to where I'm at now, but Derks formula...
= SUMIF($J$5:$J$44,J5,$K$5:$K$44)/COUNTIF($J$5:$J$44,J5)

from...
excel formula

...Almost gets what I need, but my data will not always be in a high/low descending order. I'm braindead; I can't think of how to make this formula work.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved