I am trying to count hours where calls are received between 8am and 5pm Mon-Fri. If just one call is received between 8am and 5pm Mon-Fri then only one hour would be counted. Calls in excess of one call in the same hour do not count more than one. So if two people called from 8am - 9am then that would equal 1 and if on call came in from 9-10 then that would also be 1. Attached is a sample output.
View 11 RepliesI am looking for a way to count the number of emails I receive per day and hour, below is a chart that I use to see the data. I had accomplished this with sumproduct, however it severely slows down the workbook processing to the point that it is no longer an option to use it. I extract my data from outlook and format my date and time in the same column such as 10/01/2012 15:00, I work from 7AM until 5PM then all other email are considered after hours and go in the 5p-7a row.
Email count.xlsx
Time
Mon
Tue
[Code]....
Want to be able to count number of calls (C in the rows) of each provider by DOW (labels in the column).
View 4 Replies View RelatedCount number of occurrencesI have a large spreadsheet that list customer and call dates. Each call is displayed in a separate row with the corresponding customer next to it - so each customer may show up multiple times with different call times. I want an equation that will count if 4 calls (occurrences) have been made to each customer and if it is the answer is "1". I have been trying to make it working with COUNTIF but not getting the results.
View 4 Replies View RelatedA1 is 10 (10 hrs worked) , A2 is 10:30am (in time), A3 is 9:00pm (out time), A4 needs to be the total hours and minutes between A2 and A3 based on the hours listed in A-1. What i need is a formula that will calculate the hours and minutes between the 2 times based on hours entered in A1 but that will also compensate for a manadatory 30 minute lunch that needs to be deducted from the total hours if hrs listed in A1 are more than 6.
example: worked 10HRS, 10:30am to 9:00pm, Total hrs is 10hrs 30min, which should be just 10 since the lunch is a none work time and must be subtracted.
If a person worked more than 6hrs, they must take a lunch. if they worked less, than 6 then they don't have to. I need a calcuation to recognize the greater than, less than factor into the equasion also.
I have Problem column, with problems from A2 to A14. I have number of calls column, with number of calls from B2 to B14 and finally i have percentage column in which i want to automatically calculate problems percentage. I'm including my exel file.
View 5 Replies View RelatedHow to count the hours using Excel cells (for time table scheduling)....for example : 11:00-9:00 is 10 hours, "OFF" is 0 hour, "X" means 0 also, 10:00-7:00 is 9 hours, and so on. But when I try to put the hours, they show ###### in the cells? I know the time table is mixed with numbers and letters, that's why I'm confused to use any formula to do it, but my boss told me NOT to change the original format what so ever.
View 14 Replies View Relatedi have one Q??
i have these times
07:00
07:43
04:55
07:43
is it possible to COUNTIF the repeated hours? the result should be 3 "07:??"
I am working on a spreadsheet where I want to count the number of calls to our service desk between specific hours (ie. 6:00 a.m. - 7:00 a.m.) up through 8:00 p.m. I have looked at all the forums and tried all the formulas that seemed to be what I was looking for and it isn't working! I am adding the information to a summary spreadsheet from which I am gathering the information from other sheets in the workbook. This will be an ongoing spreadsheet done weekly for every month.
Examples: I need to know how many calls from these times on a summary sheet.
6:23
6:28
6:59
7:09
7:35
7:38
8:00
8:26
8:34
8:42
8:43
9:02
9:04
9:23
9:27
9:31
10:06
10:23
10:37
10:51
11:05
11:09
11:19
I have to count the number of hours within a cell which has also dates. Example below.
12/03/2014 04:00
12/03/2014 04:00
13/03/2014 07:00
What is the formula to count the number of hours given the date ranges need to be in the same cell and the dates and hours are likely to change ?
As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.
Nights, holidays and holiday night have different price/hour.
The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for me…
On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!
Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)
This code has worked for me about 6 months now, and I havent done ANY changes at all to it. Can any of you guys think of a reason why it wont work?
The following is just en excerpt of the entire code, but the only thing thats relevant...
I have created a model that shows me the money that I am owed each month. However, I receive the money over a 3 month period.
Using a formula I need to determine how much I actually receive in total each month, factoring in that I receive each months revenue in three equal payments over three months.
For example
Month 1 Month 2 Month 3 Month 4 Month 5 Month 6
$A $B $C $D $E $F
In month 1, I am owed $A, but this payment will be split over 3 months.
So in month 1 I collect $A/3. In month 2, I am owed $B but this payment will also be split over 3 months so in month 2 I collect ($A/3 + $B/3), etc etc. In month 3 I collect the ($C/3) + ($B/3) + (last payment of $a/3) etc etc
How do I present this in a formula so I dont have to adjust every single column manually?
I need to follow the same structre for another scenario using 18 months as well.
I have been trying to work out a formula for capturing the number of patients in the hospital at half hour time intervals. There are a lot of formulas for capturing this information within a 24 hour period however not a lot of information when the Length of Stay or episode time is +24 hours.
As you can see in my example spreadsheet below, some of the patients stay for 244 hours (row 9).
The outcome that I am looking for is that a 1 is placed in all of the time slots when the patient is there. For example if they arrive on Jan 1st at 2.15 and leave on Jan 3rd at 10.30 all of the time slots in between would have a 1 placed in them.
I have been playing around a lot and think it is probably only possible if you set it up as I have in the example i.e. by having the date running down and the time running across.
How this could work? I have tried SUMIFs and SUMPRODUCT formulas which generally work for Jan 1st but then go wrong for any date after that.
I have a spreadsheet with 4 columns named:
type
date received
time received
number received
Is it possible to use vbscript and extract the total number received for the various scenarios:
all numbers received
numbers received before specific date
numbers received on specific date before specific time
numbers received after specific date
I have a sheet in which in Coloum A I have manager names, then in Colum B I have list of Employees reporting to that manager, then in Column C, I have total login time of each employee which should be => 8 hours. In a new sheet, I want to get the count of employees next to the each manager's name who login time is less then 8 hours.
View 3 Replies View RelatedEvery week I receive a sheet of data that I need to import into my master spreadsheet. The problem is I have to adjust the data I receive to import it unless there's a better way of doing it. I'm trying to automate both the modification and the importing of this sheet. I've tried to create a macro to modify and import without any success.
To adjust the spreadsheet I receive I need to: delete or find a way to exclude the first 3 rows, the bottom row with data stating "complete" and columns C,D&E from being imported; change font, size, color, bold to match the main spreadsheet in addition to formatting; split data in column A and remove any special characters. Formatting is important because I have to be able to easily remove duplicates. Also, the number of rows in the sheet I receive changes weekly.
I've attached a workbook with sample data. The first sheet is representative of what my master looks like, the second is what I sent and the third what I've been modifying it to and then importing. As stated before, I'd like to have my master automatically update with the sheet I sent, but I'm at a loss having to reformat everything every week before importing. Having the sheet that's sent to me revised before I receive isn't an option. What's the best way to automate this?
SAMPLE1.xlsx
I'll use following as an example.
I work for a supermarket, I receive many fruits per day and I want to see what percentage of them are bananas, apples, oranges, watermelons etc.
Are there any templates of pie charts and bar graphs that I can use for this that will show the percentage of said fruits daily, weekly and monthly?
I am trying to send to multiple people so everyone sees each other received the email. How would I add other addresses?
View 2 Replies View RelatedI have been working on some ODBC queries in Excel 2010.
First, I recorded them using Excel's Record Macro function, to get an idea of what sort of source data I would need. Then, I rewrote them into something a little more intelligible.
Pulling out all the sensitive info, I want to know how to add the .ListObject.DisplayName property back onto the results of the query. I tried doing it intuitively, but it didn't work. It was in there when I recorded the macro, but I can't seem to figure out where to put it back into the re-written code. Other parts of the code depend on the results of the query being a 'named field'.
[Code] .....
Also, where would I find out what all that stuff in Cnnect means? DBA, APA, EXC, FEN, etc are all just assignments, and I might like to change some to make this run a bit quicker, if I knew what they meant.
I've sent out an excel spreadsheet to a whole load of people and some have received emails back saying that it won't open properly. It's got a form which pops up and populates two data sheets behind it.
One of the issues I've had so far is that the Microsoft Common Controls library isn't installed on some computers so the form won't work. Are there any other controls that are likely to not be installed and thus prevent the form from opening?
In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time.
Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.
I have two macros, Sub macro_Combs() calls Combin_6N()
In macro_Combs I must recover the values of the line in progress 500 step 28 to put them in vN(J) table of 10 élements.
The line in progress can go from 4 to 10 digits
if I make vN(J) = Sheets("feuil3").Cells(J).Value functions for 500 step 28 but with same the information of line 1 of feuil3
if I make vN(j)=cells(lig, col).value that does not function
Public lig As Integer
Public Col As Integer
>>>>1 ère macro
Sub macro_Combs()
For Lig = 1 To 100 Step 28
For Col = 1 To 1
Next..........
I have this code (below) that calls a number of other subroutines. It begins the process just fine, but then gets hung up upon completing the first macro called, Run_Educator_Report. What could be causing this?
Sub Run_All()
Call Run_Educator_Report
Call Run_Non_Educator_Report
Call Construct_Database_for_Graphs
ActiveWorkbook.Save
End Sub
I'm trying to use some call data for some predictive staffing. I need to see the highest number of calls happening around the same time each day. I'll have to do this for a number of different lines, so working out a formula that could apply.
I have two columns, date and time stamps of the calls. A small sample is below.
7/1/13 5:50 AM
7/1/13 9:49 AM
7/1/13 10:33 AM
7/1/13 10:53 AM
7/1/13 11:19 AM
7/1/13 11:41 AM
7/1/13 12:07 PM
7/1/13 3:58 PM
7/1/13 4:03 PM
7/1/13 5:35 PM
7/1/13 6:27 PM
7/1/13 6:39 PM
7/1/13 7:37 PM
I need to create a formula that counts the number of blank cells to the right and then actually uses the value in the non blank cell.
The result requiired is that the formula will find the difference between 1 value, the next non blank cell and then will average the cells between to get the interval figures.
eg. Value1 is 50.
Value2(the next nonblank) is 60.
There are 4 empty cells in between.
I need a formula that will find the next Nonblank (Value2), calculate the difference between Value1 and Value2, then divide that by the number of blank cells + 1 and add the value to the left.
The cell in A2, calculated manually at the moment would be:
=A1+((A6-A1)/(4+1))
This would then be altered across the page so that A3 would be:
=A2+((A6-A2)/(3+1)
etc., etc.
Therefore the result returned across the blank cells would be:
A1 A2 A3 A4 A5 A6
Value1 BLANK BLANK BLANK BLANK Value2
50 52 54 56 58 60
Is there anyway to make phone calls automaticly from a spread sheet
I'm calling old customers and it would be easy to just press a button and it will dial for me
I have created some macros that need to be used in several workbooks by more than one users, so I put them in an add-in and gave them instructions on how to get the add-in. I also created a toolbar with buttons calling those macros, attached it to a workbook the users can open, and instructed them to open that workbook once to get the toolbar.
Both the add-in and the toolbar workbook are on a network drive that my users can access, but when they get the add-in, it gets copied to their own add-in folder.
Here's my problem:
When a user tries to click on one of the buttons in the toolbar, the button tries to call the macro using the path that would be valid on my computer to get to my add-in folder. This path includes my user name, so it doesn't go to their own add-in folder: it goes to my add-in folder on that computer if I ever logged in on that particular machine, or it goes nowhere.
When I created the button, I attached the macros by referring to the add-in workbook by name only, without referring to a path, so I don't know why MY path ended up defined on that toolbar.
How can I go around this? I manually relinked all the buttons for that user, and I can do it for the other users (there aren't that many users nor that many buttons), but I'd rather avoid it: I'll have to re-do it if the toolbar is updated and it just seems like more work than should be necessary.
I'm also opened to suggestions on a better way to share those macros. They can't be attached to the workbooks because we use new workbooks every year - they're emailed to us by the people who create them and for a variety of reasons, pasting everything into a workbook of our own is not a good option.
I'm updating someone's spreadsheet and so don't won't to add new cells or anything to it if possible. The problem I have is that I need to perform hlookup's of 3 cells, average them (some responses are non numeric) and then hlookup the output to give a final value.
The problem I have is that putting the lookups in one cell and then averaging them and looking up the average in another works, but all three steps in cell won't, it keeps choking on the non numeric responses.
Are their any work arounds to this, as the sheet has quite a bit of VBA code attached and I don't really want to have to add columns and then change the VBA code too.
I have a set of call data including start time and end time, with all calls on the same date. Now, how would I show in a sepearate column the concurrent calls being made. i.e how many calls are being used at the same time. This relates to how many channels I can suggest to someone with the maximum concurrent calls being made.
I have attached someConcurrent CaConcurrent Calls sample.xlsxsample.xlsx sample data.