In column A and B I have different times:
Col A Col B
09:00 18:00
What I would like to do is to is to show these times in Column E as following:
Col E
09:00 - 18:00
How do I do this without the use of Macros?
I am doing math on times and want to be able to SEE values of Negative times instead of the ########. This is happening in a time card because I am summing the weeks total and subtracting 20 from it (expected 20 hours per week) so it will ALWAYS be negative until the last day of the work week.
View 4 Replies View RelatedI've got a form with a ComboBox the RowSource of which is embeded in the column A of a certain sheet in the workbook - the values stored in that column resemble time reference, like: 00:00 (stands for 12:00 AM) or 01:45 (stands for 01:45 AM). The problem now is that when I choose something from the list the value that is displayed is different form the one present on the list, like choosing 01:45 we get the number 7,29166666666667E-02. Is there a way to cause the ComboBox to display the proper values present on the list the RowSource is linked to?
View 2 Replies View RelatedI have number of items and many items appear more than once. I need a formula so that counts the number of item appearing maximum number of times and it displays the name of the text written NOT the number of times it is written. It should also calculate number of times it appears in a particular month.
For E.g.
Table 1-5-2012
Chair 1-5-2012
Fan 3-5-2012
Table 10-5-2012
Fan 1-6-2012
Window 1-6-2012
Glass 1-7-2012
Glass 9-7-2012
The formula should work as follows
Table 2 May-12
Glass 2 July-12
I have a long column of 9-digit numbers which represent the swipe-card-ID's of patrons accessing a parking facility. Most of them occur more than once, some of them many times. There is an operational rule that bars them from using the facility more than x times. (I can't implement this limitation in the parking software/hardware, or I would happily do so.)
So my question is, is there a way to process through that column, identifying each unique ID, and reporting any that occur more than x times?
I type random numbers into column B
I want cell E10 to read column B then display (number 26 for example) how many times number 26 appears in column B
I have made a userform where I calculate how long time an operation takes. If the time fe.g. is 25 hours and 24 minutes then I get the result 1:24. I have attached my userform as it looks now. If you write 540 in the bar and 550 in volume and press "Beregn" then "Norm tid + 10%" will write 1:24 and not 25:24. Is it possible to have the Userform to write 25:24 or 1day and 1 hour and 24minutes?
View 2 Replies View RelatedExample:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s 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. I’m flexible
1:45:
If I input 1:45, then I’d 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'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 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)
I want to count every time a certain cell changes. For example, if C2 is currently set at August 5, 2007 and I change it to August 12, 2007, then add 1 to cell B2.
View 9 Replies View RelatedI have a cell which I need to copy down a column 24 times leaving 2 blank cells between each.
View 9 Replies View RelatedI want to be able to count how many instances Primary Price is >= Price 2. And, secondarily how many times Price 1 was, at a minimum, >= to Price 2 and then also >= Price Price 3.
Ultimately I want to compare Primary to 100 or so "Prices" over a years time
I figure it would be a sumproduct function, but can't get it dialed in
I have values
2
8
9
As so on in my cells a1,a2,a3 as so on
And I want value of column a repeating five times of each cell value in column b
like result
2
2
2
2
2
8
8
8
8
8
9
9
9
9
9
and so on
I want to count every time a certain cell changes. For example, if C2 is currently set at August 5, 2007 and I change it to August 12, 2007, then add 1 to cell B2.
View 9 Replies View RelatedTo put this simply, I have a spreadsheet that has 2 columns on it with multiple names and numbers on it. We'll say ABC and 123 as the example name and number. When I type in ABC and 123, I have it where it appears on the next page.
The question is that if I want to duplicate it onto the next page, how do I do it? In other words, I want to create a 3rd column with which to add a number of times I want the numbers and letters to appear. For example, I write| ABC | 123 | 5 | and ABC appears on the next page at a certain start point and is copied 5 times in certain cells. The same happens for 123 at its own start point.
I am working on an employee weekly schedule and would like to be able to calculate the amount of hours an employee is scheduled each day. For example; if you worked from 7am to 4pm, I want to have a formula that can determine that (7am to 4pm= 9 hours) then sum the total amount of hours for all employees scheduled that day.
Mon Tue Wed Thrs Fri Sat Sun Total
Employee 1 7-4 7-4 8-5 off 2-10 5-10 off 40
Employee 2 8-5 11-8 off 7-4 1-8 off 8-5 43
Hours 18 18 9 9 15 5 9 83
I've searched and found almost what i'm after but not quite i have a list of times in word in a column they are in the following textformat 12 23 00 23 14 34
i can paste them into excel but cant work out how to change them to excel time eg 12:23 00:23 14:34
I have a routine that loops through a block of records containing two time fields. One is the same for every record (let's call it the start time) the other, which varies, is the last time at which an event happened before or after the Start time (let's call it the Event time). so a pseudo block of data may look like this;
A B
15:00:00 15:01:27
15:00:00 15:00:05
15:00:00 14:59:27
15:00:00 14:59:01
15:00:00 14:48:45
I need to identify the record that has the closest time in column B to that in column A (in the example above that would be B2 (15:00:05). I then save that record off and eliminate the rest for that time block. The whole subroutine then repeats for the next block of data and so on down the list, top to bottom ( times are in descending order). Note also that times will never span the 24-hour point - they all occur betrween 12:00 and 22:00 on any given day.......
I have the price of S&P 500 in the L2 until L145 column.
And I would like to past each cell (L2, then L3, then L4....until L145) 220 times in column F.
As follow
L :
1780
1715
1680
.........(144 value)
TO column F
F:
1780
1780
1780
.........(220times)
1715
1715
1715
.........(220times)
1680
1680
1680
........(220times)
I have a workbook with a tab called "parts list" where there are thousands of parts with prices. I need to sum all of the parts based upon their location within the list. The list is divided with the following headers - Yellow (Assemblies), Gray (Sub-Assemblies) there can be many under a yellow header, and Green (Components) there can be many under a Gray header.
I have been summing the areas manually, and I am hoping for a more automated way of creating the totals by running a macro.
I need to sum (Column K) from Yellow+1 to Yellow-1 and deposit the sum in the starting Yellow row - Column L. This needs to be repeated for as many Yellow Headers as there are in the list. I then need to repeat the process for Gray Headers - Col K, Gray+1 to Gray-1, and put total in starting Gray header, Column L. Once again, there can be many Gray Headers under a Yellow header, and this needs t be repeated throughout the list. I then need to repeat this for the Green Headers - same as above Gray. I am hoping that this can be done by color, but if it's easier, I can sort the list by color and in column M, add an "A" to all Yellow Headers, "S" to all Gray Headers, "C" to all Green Headers, and "E" to represent the end of the component list -
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.
Is it possible to have the user inputing a value in cell $G$8 and have the range B11 to G11 to be copied x time depending the value of Cell $G$8 down. ie: Value is 5, it will be copied on 5 rows under B11 to G11??
View 3 Replies View RelatedI have a spreadsheet with contact information in it. I am trying to figure out a way to count how many times I click a contacts email address, then tally it in a weekly log. The end result will be a page that totals how many emails I sent each week for the year based on the number of clicks I have made to the spreadsheet.
Ideally I would also like to log the number of times the phone number is clicked too, but I am unsure how to make the phone number a live cell.
I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.
Problem 1
Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:
Tab 1 (Never changes)
AB
Bob1
Jon2
Fred3
Tab 2 (Dynamic, changes each week)
AB
Jon
Fred
Bob
So it would work as follows.
Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result.
Problem 2
Weekly league rank table that shows position movements week by week
Example.
Week1
1Jon
2Bob
3Fred
Week2
1FredUp 2
2BobNot Moved
3JonDown 2
Can Excel calculate/show the actual movements of league positions?
I want to isolate text on either side of dashes ( " - " ) that occur either 2 or 3 times in a single cell.
Example:
Apples - Pears - 99 - 100+ years
Bananas - 56 - 57+ years
In particular, I would like to capture the "99 - 100+ years" and "56 - 57+ years" text on its own.
I tried this " =SUBSTITUTE(A1, "*- ", "", 1) but it doesn't work
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 am trying to use find and replace but the text that i'm searching for appears three times in the cell. I only need to replace the first occurrence in the cell. Alternatively, if there is a way to do this, can the second and third occurrence be changed?
Example:
Cheryl called Louie to advise she would be late for the meeting. Louie responded that he would meet Cheryl at her office. Cheryl confirmed.
I need to change the first 'Cheryl' to a job title and the second and third Cheryl to her initials (CL) so would read:
Manager of Aboriginal Affairs called Louie to advise she would be late for the meeting. Louie responded that he would meet CL at her office. CL confirmed.
i have this in my 1 cell: ttgtcctacttacaacactgtgcttagtaatggttattgcgactttatccttgttctgaa i want to count how many "a" in this cell . which formula i can use to solve this problem ?
View 8 Replies View Related