F11 = 4:00 PM
G11 = 2:59 PM
I need an if/then formula that will take F11-G11 and then if the difference is greater than 61 minutes to place a "1" in H11.
I also need a formula in V11 if H11 is blank then use formula =IF(F11>G11,1,"").
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
I have $ 3.00 in cell G9 and have a percentage of 40% in another cell B9 (I would like to be able to change the 40% in this cell if I have to). I would then like to have the answer ($3.00 - 40%) $ 1.80 posted in a third cell below G10 What would be the formula for this?
View 4 Replies View RelatedI have data over the last 170 days. Each entry has a time stamp. I would like to count how many entries there are between 7:45 am and 2:45pm. A total count across all 170 days is needed, but it would be great if I could break that down by calendar day (i do have that as a column).
Here is a sample of the data:Friday11/15/1307:08AM
Friday11/15/1307:08AM
Friday11/15/1307:16AM
Friday11/15/1307:16AM
Friday11/15/1307:46AM
Friday11/15/1307:46AM
And the sheet has dates starting in June and ending in mid December.
id like a formula that enter "1" into a field if i start after 00:00 and before 03:59 am
so in d4 is my start time and M4 is where i want the formula to go
i tried this formula but did not work
=if(d4>="00:00",<"04:00",1,0)
Is it possible to determine how many times a formula will be used? Heres my problem.
I have items that are bought, and then altered. So I need a cell to determine how any times my IF formula is used. My current formula is:
Formula entered into D3.
=IF(D2>0,D2,)
Row 2 is the amount and Row 1 are dates.
If I drag the formula to the right, E2 will be equal to 0 so will enter 0. But I need it to say what ever is in D2 3 times. (As this particular item takes 3 weeks to do) So D2 & E2 & F3 will all have whatever was entered in D2.
The attached spreadsheet is a sample of a master sheet I need to maintain. Keep in mind that the spreadsheet has hundreds of names on it and 10 sheets in the workbook. What name appears most in the "person who called" column, then I need to know out of all the people who called, what percentage of calls he made....
I have to do this on each sheet, so if it is possible to have it work for all the sheets.
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?
Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.
I have some groups of data. Each group are 5 cells: ........
What I want is to make a formula to sum the five numbers of each group, then: =sum(a1:a5). but, how could I copy the formula to make Excel understand that I want the numbers from a6 to a 10, and from a11 to a16? I try to make it with left click in the square down at right, but it just add one value to each cell:
It makes this: ..................
How to calculate overlapping times. I have found a few formulas on here I have tried to reproduce with no luck. This thread is similar to my issue.(How to calculate the amount of overlapping time) I need a non VBA format.
I need to know what times are overlapping; I need to see when two people are working active cases at the same time and how much time is overlapping. This is a huge spreadsheet with several rows of data. At this moment I am dong it all manually.
Here is a sample of what is looks like. As you can see the first line is overlapping with the second line by 8 minutes into the next case.
NAME
DOS
Start time
End Time
Person A
2/3/14
6:40
7:38
[Code] .....
I have this huge formula and I want to run it in one row, across 350 columns:
=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)))
The only thing I need to change from one column to the next is the four places where it says ROW(3:3). I need that to ratchet up as it goes across to the next. The cell to the right should say:
=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)))
Then the next cell to the right should say:
=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)))
etc., etc.
How can I fill across so those go up and I don't have to fill in those four numbers 350 times?!
Rules:
First rule is that no breaks can overlap.
1st Break is 40 Mins.
2nd Break is 20 Mins.
1st Break is at least 2 Hours After Time-In.
2nd Break is at least 2 Hours Before from Time-Out.
Each Employee might have a unavailable time for breaks.
Not sure if it is possbile to create a formula like this, or I might need an Excel Wizard to do this.
I currently have a large amount of data extracted from a performance tracking system. The columns I am concerned with are Page Title and Time (the time it takes the system to navigate to the this page).
I am trying to create a dashboard to show:
Webpage Title (each unique instance of the webpage)Aggregate Time (total time it takes for all instances)# of Hits (Each time the value appears in the data)Average Time (this formula I can handle; Aggregate Time/# of Hits)
The major issue is that the webpage is extracted as XYZ001 -- PAGE1, XYZ439 -- PAGE2, XYZ854 -- PAGE1, etc. Basically, I am only concerned with what comes after the --.
Here is an example of the two tabs.
DASHBOARD TAB:
Page Title
Aggregate Time
# of Hits
Avg. Time
PAGE1
[Code]...
Basically, I am looking for a formula that says,
For any instance in the Page Title column that contains PAGE3, add the amount in the Time column (i.e., 1.2 +.5).
I need some IF formula I believe that will yield an answer between 1 - 5. I'm not swavey enough with these things to figure this one out... trust me I tried and it keeps getting more confusing for me.
If the time worked is between certain time criteria then it would equal 1 - 5 depending on the time.
Example: If I work between the hours of 5am and 1pm then I would be in the Open/Mid range and would need to equal 2. If I only worked a few hours and my hours fell only between the Mid range then it would equal 3.
Then based on that... It would automatically fill in on the deployment charts... My name would show up on the Open and Mid Deployments under the task chosen for me to do that day.
I've attached a small sample of what I am looking for to kind of help show what I need. The highlighted areas are the areas I'm not sure how to do.
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets Id like to query data for each day and find an average value based on the time and then copy that result into another sheet. Im not sure if Ill need 2 macros for this or if one can be used.
Heres a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input 1:45 or 0:45 into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. Im flexible
1:45:
If I input 1:45, then Id like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
I need a formula that will search between dates and time to identify if the person will be present at the conference breakfasts, lunches and dinners.
Therefore the cell needs to look if the current date falls within the arrival and departure date and also if the time within eating times falls outside the arrival and departure times.
how I can have a formula repeat down a column five times before it changes to another formula? For example. Say on tab 1 I have a list of products. On tab two I have five codes that repeat down the page over and over again. On tab two next to the repeating codes I need to repeat product one 5 times and then skip to product 2 on the 6th row and repeat five times and then skip to product 3 on the 11th row and repeat 5 times and so on?
View 10 Replies View RelatedI need a formula to check two times and count the colum figures.
example only:
countif(=>4:30:00 PM <=5:30:00 PM)*(11/12/06)
So, I need to count all cells with time between 4:30 and 5:30 with the date of 11/12/06.
formula to work out a variance between two times
Using the 24hr time format in cell a1 i have a start time of 10:43 and in cell b1
i have an estimated time i think a job should take in this case 30 minutes and in cell c1 i have the actual time that job was finished in this case 11:07 and in cell d1 i have a variance between the two times which in this case would be saving me 6 minutes
Need Excel Macro or Formula for finding duplicated cells more than 5 times and copy them into new sheet.
For Example;
apple
orange
apple
banana
apricot
[Code]...
OUTPUT in new Sheet will be
apple 5
orange 5
What formula will count the number of times the value J3 appears in E2:E400. BTW...the a-g is formatted as a table. excel 07
View 3 Replies View RelatedI'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.
Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.
So I need something that can take each control number that has three cycles and add their # of 0:15 together.
The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.
Ultimately I would like the results on a separate sheet.
This is only a part of the file, there are over 1000 different control numbers.
I am attempting to create a formula that will count the number of times, lets say letter A, occurs in column E. However, the tricky part that I have been confused on is that I only want to count how many times A occurs between each hour of the day. I need to count number of times "A" occurs between each hour of the day for the entire month.
My Worksheet looks as such: Column A contains the dates for the month of November, Column B contains times that random instances occur, Column C is not important, Column E contains a Letter A-N that pertains to what occurred at a time in column B. SO, Column B and column E coordinate with each other.
Here is an example. Column A - Cell A1 - 11/01/2013 , A2 - 11/02/2013, A3 - 11/03/2013.
Column B - Cell B1 - 12:01 AM , Cell B2 - 14:03 , Cell B3 - 15:23
Column C - Cell E1 - A , Cell E2 - A, Cell E3 - B
So I would like Column F to display that between 12:00 - 12:59 Am there was X number of times the letter A occurred throughout the entire month.
I am using this formula to count the number of times “closed” appears between
particular dates:
=SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23>=$I$2)*($A$1:$A$23<=$J$2))
I have tried applying the same logic to another formula where I wanted to
Also count the number of times “Not Stated” and “In Progress” are shown.
However when I do I am receiving a ‘0’ number in return. The formula I wrote
was:
=SUMPRODUCT(--(CS_Ticket_Report_Dump!D$1:D$50000=C6)*
(CS_Ticket_Report_Dump!G$1:G$50000="Closed")*
(CS_Ticket_Report_Dump!G$1:G$50000="In Progress")*
(CS_Ticket_Report_Dump!G$1:G$50000="Not Started")*
(CS_Ticket_Report_Dump!A$1:A$50000>=AN$1)*
(CS_Ticket_Report_Dump!A$1:A$50000<=AO$1))
I need a formula to calculate hours worked between specific times. In the worksheet I have, I need to calculate the hours worked between 5am and 10pm, the start time is in C7, and the finish time in D7 the formula need to be in H7, and another formula in I7 to calculate hours worked before 5am and after 10pm .
C
D
F
H
I
Start
Finish
Total
Ordinary
20%
4:00
23:00
19:00
17
2
I'm trying to pull the earliest and lasted times out of a list of time stamps. Say I have a column full of clock in and clock out times and wanted to find the first clock in time and the last clock out time (just an example). My lookup_value would be a Store number. I can't post it here due to legality reasons, but I have a column a store numbers that will be my lookup values and I need to find the earliest and lastest time in a list of data on another tab.
View 9 Replies View RelatedI need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,
Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)
On my spreadsheet i have a bunch of variables:
A1 = Arrival time at Checkpoint 1 - 5:53:08
A2 = Time to destination from Checkpoint 1 - 1:10:18
B1 = Arrival time at Checkpoint 2 - 6:00:56
B2 = Time to destination from Checkpoint - 1:02:30
C1 = Total distance from Start - 2.83
C2 = Arrival Time at Destination - =SUM(A1,A2)
D1 = Speed determined from the other variables
D1 is SUPPOSED to equal 25.00
It also is where I am stumped!!
Not only can I not figure out the math but also how to format the function to get the right answer.
ps: Arrival Time at Destination is only included in case it would be useful to find the answer!
Looking for a formula for total cost based on a list of hours for each employee and a table of rates for each employee.
Please see attached file : Formula.xlsx
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.