Count Of Number Of Beds In Use Based On Admit And Discharge Time
Jul 9, 2014
ER manager is looking for a count of how many beds are in use on a given day at any given time using the admit time and discharge time. I used the floor/ceiling formulas to get the times to being more user friendly.
If A column is the dates, B column is the admit time and C column is the discharge. I have tried the formula:
=COUNTIFS(A:A,"date",B:B,"admitTime=admitTime")
Plus various other combinations of moving the admitTime/dischargeTime around in this setup. But mostly I end up adding the beds used for the entire day beyond that given admitTime.
Data set for example:
date
admit
discharge
5/1/2014
8:00
9:00
[Code] ..........
So basically, based on this data, at 18:00 4 beds were in use.
View 9 Replies
ADVERTISEMENT
Oct 23, 2008
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).
=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)
Would a Index/Match/MAX function be more efficient?
View 4 Replies
View Related
Nov 18, 2008
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
View 9 Replies
View Related
May 25, 2007
I have a very large spreadsheet and want to count the number of times a particular text string shows up in a column. I can't use autofilter due to the 1000 limit.
Here's an example, Column C contains:
Dan Parker
John Doe
Dan Smith
Jill Smith
So if I search on *Dan*, the function should return a count of 2.
I've used COUNTIF before to return values when the whole cell = a certain value but in my case the cell may have 200 characters and I want to count based on a fuzzy search. I would like to do this in a function and not a macro.
View 14 Replies
View Related
Jul 11, 2012
which formulas suitable as i just wending to count a number which is time format as "HH:MM" on at column A to down without a duplicated,
I have suck on it .. What I wanted as exampled :
12:00
12:00
12:01
12:01
12:02
range B1 = 3
View 3 Replies
View Related
Jun 19, 2014
I'm doing a study of when people are most productful in their day. I just need to count how many units a person made within say 4 hours of starting work.
My data is similar to the excel I've attached
Count completes.xlsx
I'm using Excel 2010 too.
View 4 Replies
View Related
Jan 23, 2014
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.
View 4 Replies
View Related
Nov 30, 2013
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.
View 4 Replies
View Related
May 5, 2014
I have a data to filter and count it based on the time.
As you can see on the attached file, there is a table of time range wherein I will input the total counts of the partners.
For example:
I will filter Column B with "One" & "M One".
After filtering, on the table, i will count the data that is fit on the time range.
Let say on 1:00am time range, i just want to count the data that is less than 2:00am on the filter data, and put the result on the corresponding partners. Put "0" if there is no data on that time range.
I have attached my file..
View 1 Replies
View Related
Jan 30, 2014
I am working on a spreadsheet that will provide count of types of complaints for particular areas over a running time span. I have tried a multitude of formulas but not sure how to write any of them correctly. What I am trying to do is generate a count of area type by whether it is formal or informal. (i.e. I want to know if there are x formal finish issues vs. y informal finish issues and so on.) This information will get charted and be kept "real-time" user input.
Type
Description
Concern
Formal
Informal
[Code] ....
View 6 Replies
View Related
Feb 5, 2013
I get this work a lot and am looking for a much more effective way of working the dataset,
I have numbers like 1.1, 1.2, 1.3 and so on.......
These come under criteria like (1) = 1.1, 1.2, 1.3 and so on.....
I am look for an automated way of doing a count of any number that falls under this criteria, so I want to count based on criteria (1) it would count all 1.1,1.2,1.3 and so on as one count.
I am attaching a sample document to see how it is laid out. [URL] .....
View 6 Replies
View Related
Jul 27, 2009
I need to count the unique values in a list based on another value. i.e.
AB ___ Jones
AB ___ Smith
BC ___ Henry
AB ___ Jones
BC ___ Brown
BC ___ Henry
AB ___ Tomkins
For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting:
=IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100)))
but haven't been able to get it to work.
View 9 Replies
View Related
Feb 22, 2011
I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.
In excel is best illustrated what I mean!
In total, 5267 lines and need to do this with all. You can do this in excel?
View 4 Replies
View Related
Jun 17, 2014
I have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
View 10 Replies
View Related
Jun 17, 2014
I have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 16:01:00
Days Late = 2
View 3 Replies
View Related
Apr 28, 2014
I have a column of "timestamp" data (in mins) which i want to filter by a given time interval, say 10 mins. Then i want to count the number of records for each time interval and output the data to a sheet. how can i achieve this? through vba?
I attached a pic illustrating what i want to accomplish.
QQ截图20140429104406.png
View 1 Replies
View Related
Nov 17, 2005
I am using the count function for attendance tracking of Vacation, Personal Time, & Sick Time. (Example: = COUNTIF($F6:$CQ6, "V")
I need to be able to do half days. I have tried many different formulas/ways to incorporate the half day scenario even without using the count function with no success.
View 13 Replies
View Related
Aug 14, 2014
I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.
I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.
I've attached an example work book and I would like the result in H2.
Example Workbook.xlsx
View 5 Replies
View Related
Aug 24, 2009
I am struggling to find a formula to calculate hour:minutes between the following:
Cell
A1= 04:00
B2= 17:00
I am wanting to calculate the hours between 06:00 and 16:00 and then the hours between 16:00 and 06:00 the next day
ie: 06:00-16:00 = 12;
16:00 - 0600 = 3
View 14 Replies
View Related
Aug 7, 2013
I need to count the number of equal cells in col D beginning at the top of the column. The counted cells must begin with a text prefix of "Category:" without the quotes.
Some but not all of the cells in col D begin with a prefix of "Category:" without the quotes, followed by a word or words following the word "Category:" See examples below. All of the terms prefixed with "Category:" in col D are in alphabetical order. I need to count the number of identical cells in col D with the "Category:" prefix.
Examples of the contents of cells in col D with the "Category:" prefix are as follows:
Category: Adversity
Category: Answers
Category: Assurance
Category: Blessings
Category: Build
Category: Change
Category: Children
Category: Choices
Cells above and below cells with a prefix of "Category:" in col D are not adjacent.Cells above and below cells with a prefix of "Category:" in col D are separated by 3 to an undermined number of rows.
I need to count the number of equal cells in col D and insert the count in col A at the last equal term. For example, col A above would have 93, 1, 1, 5, 10, 8, 3, and 12 inserted into col A.
View 9 Replies
View Related
Aug 21, 2006
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....
View 6 Replies
View Related
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!
View 4 Replies
View Related
May 5, 2006
I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!
For example
Start time: 21:00
Finish time: 06:30
Hours worked: 9.50
Start time: 12:30
Finish time: 23:00
Hours worked: 10.50
View 9 Replies
View Related
Sep 8, 2009
I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.
Any idea about a dynamic sum function that will do this, in combination with VBA?
View 2 Replies
View Related
Dec 24, 2008
I have a userform that has a time field on it and then the userform displays the time as a number.
Ex. I entered 11:00 AM in the Time of Contact field and the form displays it as 0.458333333333333.
View 10 Replies
View Related
Jan 16, 2006
in writing a formula that will count the number of times
the store is listed (Column B) when it matches with closed (Column C).
On the table listed below I will return the data using a match.
From this table
A B C
1/8/2006 9:45Store 1Closed
1/8/2006 9:57Store 2Closed
1/8/2006 10:05Store 3Closed
1/8/2006 10:09Store 4Closed
1/8/2006 10:15Store 5Closed
1/8/2006 10:24Store 1Closed
1/8/2006 10:36Store 2In Progress
1/8/2006 10:41Store 3In Progress
1/8/2006 10:50Store 4Closed
1/8/2006 10:58Store 5Closed
1/8/2006 10:59Store 1Closed
1/8/2006 11:15Store 2Closed
1/8/2006 11:22Store 3In Progress
1/8/2006 11:24Store 4In Progress
1/8/2006 11:33Store 5Closed
1/8/2006 11:51Store 1Closed
1/8/2006 11:56Store 2Closed
1/8/2006 11:57Store 3Closed
1/8/2006 12:03Store 4Closed
1/8/2006 12:16Store 5Not Started
1/8/2006 12:23Store 1Closed
1/8/2006 12:28Store 2Closed
1/8/2006 12:57Store 3Closed
To this table
A B C
1/8/2006 9:45Store 15
1/8/2006 9:57Store 24
1/8/2006 10:05Store 33
1/8/2006 10:09Store 43
View 11 Replies
View Related
Oct 8, 2007
I need to separate a list of times from a telecommunications bill. I have 170,000 line of data.
The Times are represented as below.
eg
46 46 Sec
59 59 Sec
100 1 Min
159 1 min 59 sec
200 2 mins
502 5 mins 2 sec
1256 12 mins 56 sec
3456 34 min 56 sec.
How do I separate them to a decimalized per min value.
View 5 Replies
View Related
Mar 2, 2012
I have data in a column (G) consisting of zero and ones. I would like to count the number of clusters of the number 1 in the data. For example in the data below there are 8 ones. But instead, I need to calculate how many groups of 1s occur. So in the case below the group of 1s = 3.
In terms of what defines a group. Whenever there is a zero either before or after the occurance of a 1 constitues a group, i.e the groups are broken out by zeros.
0
0
0
0
1
1
1
0
0
0
0
0
0
0
0
0
0
0
0
0
1
1
1
1
0
0
0
0
0
0
1
0
0
View 3 Replies
View Related
Mar 22, 2012
Consider this code:
'light eligibility
Dim facb As String
Dim sunset As Variant
[color=green]' check if facility has lights[color]
facb = WorksheetFunction.VLookup(RID, ds, 10, False) 'find facility code
If WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Then 'facility has lights
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False) 'lookup the sunset time based on the record's date
[Code] ......
This code checks the need for lights at a facility.
It first checks to see if the facility even has lights by cross-referencing a value in the record with a facilities database.
If it has lights, it then checks to see if they are needed. If the rental goes past the sunset time, then it needs lights. Sunset is determined by cross-referencing the date value in sheet1! A9, with the sunset database.
If it needs lights, variable lghtson is calculated equal to "sunset"-30 minutes.
As I step through this code:
WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Facility has lights.
Check to see if lights are needed.
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False)
sunset=0.879166666666667 which is 9:06PM. This is a proper value from the lookup.
If rental_end.value > sunset Then
rental_end (value from textbox) = "9:30 pm" , sunset=0.879166666666667. This is true, and Excel accepts it as true ...
lghtson = sunset - 0.5
0.379166666666667 = 0.879166666666667 - 0.5 (9:06 AM)
This is not the value I was looking for. I was looking for 8:34PM (0.856944444444444)
View 2 Replies
View Related
Jul 30, 2012
I want to count the number of times any given number appears either as a consecutive group or singularly.
To give you a context I monitor windturbines and for any given fault code I wish to count the number of events it occurs in a month. Now it could be for 1 hour then clear the next then back for 17 then claer again. That would be 2 events!
NB the data is in seperate coulumns per turbine.
View 7 Replies
View Related
