Formula To Count Number Of Patients Per Time Interval Over 24 Hours+
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
ADVERTISEMENT
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 2, 2013
Im trying to create a spreadshet to determin hrs per time interval
i.e
06:00 - 14:00
14:00 - 22:00
22:00 - 06:00
so a start time of 06:00 and finish time of 14:00 would show 8hrs in first interval and 0 in the other 2
and a start time of 10:00 and a finish time of 18:00 would show 4hrs in first interval and 6 in second and 0 in last
ive currently got start time in A1 , finish time in B1 and want hours for interval 1 in D1 , interval 2 in E1 and interval 3 in F1
View 2 Replies
View Related
Oct 6, 2009
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 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
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
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
Oct 10, 2009
When I am converting a time from Hours/Minutes to Hours/Tenths, Excel is not converting it consitantely. EXAMPLE: 1:15 = 1.25. When I format the cell to present only one place past the decimal point, sometimes the cell will round up to 1.3, and other times it will round down to 1.2. What am I missing?
View 3 Replies
View Related
Jan 21, 2010
I am trying to find a way to calculate the number of hours between date/times found in separate rows. The attached data set will help to envision what I am talking about.
For each couple of rows, I need to find a way to calculate the number of hours elapsed from row 1 to row 2. In the first example, to calculate the number of hours between 12/2/2009 8:56:51 and 12/4/2009 6:35:27.
View 2 Replies
View Related
Jun 30, 2014
How to get the Hours of two different time
A1
27/06/2014 11:30
B1
30/06/2014 18:14
C1
Ans: Hours of these 2 dates with time.
i just need to get their Hours.
View 3 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
Jul 20, 2006
In a single column of random numbers how do I count the interval since the last occurrence of that unique number?
View 9 Replies
View Related
Feb 1, 2010
I've got a worksheet that deals with dates and time. Similar to a checkbook, I need to take the balance and subtract what's taken from that. But the writer copied this from another software program and it's in this format. How do I convert it to a time format? Or is that what I really need? Right now the taken and balance are both in a general format.
I just need to know on any of the given days what the balance of this person's time was in hours and minutes.
View 2 Replies
View Related
Nov 4, 2012
I have devised a simple formula when a member of staff enters a date and time into a cell it starts deducting from todays date and time. ie:
Cell A1 - Date and time is entered by staff
Cell B1 - Is the above cell +24hrs
Cell C1 - NOW()
Cell D1 - B1-C1 Formatted in hours ( [h] :mm:ss (Like a count down clock)
The problem i have is that i cannot get Cell D1 to show minus ours it just goes to negative and shows continous #####. Is there any way i can get Cell D1 to show the hours it has gone minus by.
View 6 Replies
View Related
Oct 22, 2008
I need to enter a formula that calculates the time a report is received from the time it was recorded in our database. therefore, it needs to exclude non working hours. here are the fields:
A1 2008/10/10 16:30
B1 2008/10/11 09:30
C1 8:00
D1 17:00
A1 = report received
B1 = reported recorded in db
C1 = work day start time
D1 work day end time
Where the answer should = 2 hours.
I am not an experienced excel user and so far the only formula I have now is: =TEXT(B1-A1, "d:hh:mm")
And how do I account for weekends?
View 9 Replies
View Related
Sep 5, 2008
I have a list of times, and I need to work out a way to establish what time interval it applies to, using a function. In production, this will be used over hundreds of entries at a time, but for the sake of example I'll cut it down to 15 times:
17:28:35
16:11:14
17:08:20
19:21:51
15:29:01
15:31:45
14:32:24
13:39:51
15:44:41
16:52:38
20:17:37
13:26:05
15:45:01
20:12:24
12:53:26
Now, there are 27 different time intervals there times can fall into:
1: 6:00 - 6:30
2: 6:30 - 7:00
3: 7:00 - 7:30
4: 7:30 - 8:00
5: 8:00 - 8:30
6: 8:30 - 9:00
7: 9:00 - 9:30
8: 9:30 - 10:00
9: 10:00 - 10:30..............
So, what I'm looking for is a formula that will match up the time to the interval. For example, it would look at 16:52:38 and output that it falls within interval 20.
View 5 Replies
View Related
Oct 11, 2013
I have a file that sits open all the time, and performs some refresh functions every thirty minutes. I need the file to save a copy of the tab as a CSV file at a given time interval. The code below is almost there, just need to work with the time interval part. The way it should work is to open the csv, copy / paste the active sheet; then close the csv; leaving the original excel file open. I can run it, and it works, but the time interval is not triggering.
I can get the time interval to work by itself, and the save csv part to work by itself also; I need them to work together.
VB:
Sub test()
Application.OnTime Now + TimeSerial(0, 1, 0), "test"
Dim OutputFile As Workbook, InputFile As Workbook
Dim sDD As Worksheet
[Code].....
View 2 Replies
View Related
Mar 14, 2014
I need to get the total values within a criteria. Please see attached sample file.
View 5 Replies
View Related
Apr 18, 2009
I have 2 columns of data (value and time):
for example:
15 4/2/08 13:00
4 4/2/08 19:00
7 4/5/08 12:00
13 4/9/08 3:00
They are continuous data. so I want to divid the value into hourly data as follows.
15 4/2/08 13:00
? 4/2/08 14:00
? 4/2/08 15:00
. .
. .
? 4/9/08 2:00
13 4/9/09 3:00
View 9 Replies
View Related
Aug 29, 2008
I have a column that shows the date and time and it looks like this:
8/1/2008 6:36 AM
8/1/2008 11:15 PM
8/1/2008 8:01 PM
8/1/2008 3:12 AM
I want to convert it to show just the time but I want it to be in 30 minute increaments. So in the example above, I'd want to see this:
06:30
23:00
20:00
03:00
View 9 Replies
View Related
Oct 14, 2009
Im trying to figure out the formula to comput hours:minutes between time on different dates a couple of days apart.
B12 = Date
B15 = Time (on date in B12)
Previous date and time are in AA9. I have been trying to us =SUM(B12+B15)-(AA9).
View 5 Replies
View Related
Jun 23, 2006
Is it possible to 'round' a time to the next nearest 15min interval?
As an example in cell A1 I have a value that returns 2:07 PM (its formated
as h:mm AM/PM), but in B1 I wish to translate this to the nearest 15min
interval in an hour which is 2:15 PM, if the value in A1 was 5:39 PM I would
want to show 5:45 PM in B1 etc etc
View 13 Replies
View Related
Jan 29, 2010
Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....
View 9 Replies
View Related
Jun 11, 2008
A1 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.
View 9 Replies
View Related
Jul 20, 2012
I am having trouble getting a VBA code to do the following:
2012/05/01 00:00:00
2012/05/01 00:03:00
2012/05/01 00:06:00
2012/05/01 00:15:00
2012/05/01 00:18:00
From this above to this below
2012/05/01 00:00:00
2012/05/01 00:03:00
2012/05/01 00:06:00
2012/05/01 00:09:00
2012/05/01 00:12:00
2012/05/01 00:15:00
2012/05/01 00:18:00
There are data entries next to these time stamps. I am able to create just the time stamps starting at the beginning and ending at the end of the month but I need it to fill in missing entries in a data set. The code below works but only some of the time. I have tried a few different things but nothing works.
Code:
Sub Insert_missing_3min()
'Inserts a row with the date and time where the missing date and time stamp is and a zero next to the date added.
Dim min3 As Date
Dim CurTime As Date
Dim CurCell As Date
Dim NextCell As Date
min3 = 3 / 24 / 60
[Code] ........
View 1 Replies
View Related
Jan 24, 2008
I have two sets of data, one is recorded every 5 minutes and the other is every 15 minutes. I am trying to add every 3 cells in the 5 minute column so I can compare it side by side with the 15 minute column. I have tried one of the responses in this forum with placing 0s in 2 cells and then the formula in the third however this does not allow me to compare the 2 sets side by side.
View 4 Replies
View Related
Apr 4, 2013
find a formula that will calculate the hours between the two below values but only take in to consideration the business hours (from 9 to 17) and exclude any weekends?
08/03/2013 13:32:00
02/04/2013 09:32:50
View 4 Replies
View Related
Jan 13, 2009
I am making a schedule and I would like it to take out a 30 min break if the hours worked is over 6 hours.
I have so far
A B
1 11:00 7:30
=24*(B1-A1)
Gives me 8 hours, I would like it to subtract the 30 minutes only ifthe sum is over 6 and not alter the sum if it is under 6.
View 9 Replies
View Related
May 8, 2013
I have a large data set which contains four coloumns: Supplier, Supplier number, order number, and date/time of delivery. The date/time coloumn is formatted as YYYY-MM-DD HH:MM with a 24h time notation. What i want to do is to find deliveries that occurs within 1 hour and that are from the same supplier. So i basically want to group (?) the data with regards to the suppliers and then, within these subsets, check for date/time entries that occurs within 1 hour from each others by "reading" each date entry and compare it to the following one(s) (and maybe stop comparing when the 1 hour interval is passed)?
Furthermore, even if this one might be very hard, it would be good if i could make sure that the entries that are "tagged" as within a 1 hour interval, wont be used as basis for a new interval or be included in other intervals.
The result i am after would be number of 1 hour intervals for each supplier and the number of entries in each interval.
Below is an example from the date/time coloumn:
12-03-08 15:32
12-03-08 15:33 ... Interval with 2 entries
12-03-12 14:54
12-03-28 11:57
12-04-16 09:10
12-05-07 13:41
12-05-07 13:46 ... Interval with 2 entries
12-05-28 11:55
12-05-28 12:00
12-06-04 12:01 ... Interval with 2 entries
12-06-04 12:09
12-06-11 08:30
12-06-11 08:31
12-06-11 08:59 ... Interval with 3 entries
12-07-02 11:10
View 8 Replies
View Related
Sep 24, 2012
i am trying to work with a formula that will look at date today (NOW) and compare this to a due date and in return provide me with only the working hours total. Working hours are 8am to 4pm (8 HOURS).If the due date is passed this will be a negative figure.
View 9 Replies
View Related
