Establishing A Time Interval
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
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
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
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
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
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
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
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
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
Jun 26, 2014
I have to run quite a few excel VB macro every morning one after the other.
I have to manually go thru each of them.
I need to create 1 VB macro which will run all macro after 1 min of interval in between.
Example:
Macro to run Sub Store1
Wait 1 min and then run Macro for Store2.
Macro to run Sub Store2
Wait 1 min and then run Macro for Store3.
Macro to run Sub Store3 and so on.
Below is the code I started with but not successful.
[Code] .....
View 2 Replies
View Related
Mar 2, 2013
I'm working on a plan with the objective to reach a 20:80 ratio from Team 1 to Team 2
The intent of the is get 80% of the work to be supported by Team 2, which currently has no team members.
Team 1 currently has 32 team members.
I am trying to to determine if I need to hire or re-deploy employees to from one team to another or to another program to achieve the objective.
The objective is to take Team 1, which is currently doing 100% of the work & reduce that work load to approximately 20%, which then Team 2 will be supporting approximately 80%.
I started a generic calculation but I believe it's wrong.
View 3 Replies
View Related
Apr 17, 2014
I have a spreadsheet with thousands rows of items on. I am trying to identify duplicate entries based on two or three cells within each row. If those two or three cells are duplicated for that particular ID then the duplicate entry (row) should be highlighted for deletion.
Better still it would be great if there was a macro or formula which would delete that duplicate row.
So for example on the spreadsheet I have attached, I would like rows 6 and 9 deleted or highlighted as the criteria for deletion is a duplication of the entry in the 'YEAR' column AND the 'VALUE' column for the REFERENCEs AB-1234 and AB-9876 which are the unique identifiers for each individual case.
This would leave the end result as per the table below on the attached spreadsheet.
View 4 Replies
View Related
May 2, 2008
Is there a way of establishing what the color of a shaded cell?
View 9 Replies
View Related
Nov 23, 2009
See attached worksheet for reference. Is it possible (while utilizing the same spreadsheet on a weekly basis) to zero a spreadsheet subsequent to its use. Importantly however, all relevant formulas must remain perfectly intact and will re-establish themselves once relevant data is placed inside an individual cell? In this case, as soon as a “Name” (or even a letter) is referenced inside the “Name” column: H10:H19?
In other words, the entire sheet is blank bar the top date and respective headings. Once any text is placed inside cells H10:H19, the formulas from the associated Row re-applies itself to the “Week-Start” dates, “Week-End” dates and references a default “Phone” amount for ‘$10’? The Data Validation formulas I’m sure would remain undamaged? This would prevent ‘text clutter’ (such as dates extending to the bottom with no apparent referencing or connecting information?
View 2 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 7, 2014
I have geological readings "W,X,Y & Z". For every drilled hole I have these readings recorded for individual intervals (i.e. 1-2m, 2-3m, etc.)
However, the intervals have now been grouped together based on other (unlisted) specifications.
Now I am tasked with averaging these readings "W,X,Y & Z" for the decided groupings. For example.... if intervals 1-4m have been grouped for the first hole, what is the average reading of "W,X,Y & Z" over this new interval?
I have attached a file which displays the layout of the spread. To the left is the original data, and in columns I:K are the new 'groupings' of intervals. Ideally, I would like to generate the average table to the right of the original data (highlighted in green).
So basically, I need some vba code to average the readings in the 1st table conditionally to correspond with the grouped intervals in the 2nd table.
View 1 Replies
View Related
Jan 13, 2014
I want to make a formula that returns a value of a interval, this value is estimated from a serial of 10 intervals with up and down value.
View 1 Replies
View Related
Nov 18, 2009
I want to write a macro to copy A2:A25 and paste the same range in the next blank cell upto Row no 21384. I have to do it 891 times.
View 5 Replies
View Related
Apr 30, 2014
I am making a spreadsheet to control my house expenses, and for that matter I would like to have the total sum of expenses for each month, but I am not quite sure how to do it.
I attached an image to better illustrate my question.
I have two persons in my household, and I use this spreadsheet to calculate how much we owe to each other.
But I would like also to know how much we spent in each month (because we both have a top budget we need to control).
I tried to use IF functions relating E8, E9, E... cells to the column A, but I had no success.
View 2 Replies
View Related
Oct 16, 2008
I have a template whereby it show agent hourly performance. I unable to use vlookup formula because duplicate id with different interval. If I select id 1977 it will auto update agent performance it the table according to the interval.
View 9 Replies
View Related
May 13, 2007
Is it possible to have Excel calculate using a fill down formula the number of days between a reoccuring value in a column? For example, I have column A formated as a DATE column containing every Wednesday and Saturday of the week for all of 2006. Column B has the value 21 in day intervals of 3, 5, 7, and 6 days. Currently, I'm counting the days (or ROWS) in between each time 21 appears manually. It would be cool if Excel can count the days in between each occurance of 21 using the DATE Data column on Column A.
View 5 Replies
View Related
Dec 16, 2013
I want to create a room reservation sheet, based on the following:
December
Room 1
Room 2
08:00 am
09:00 am
10:00 am
Where the blank cells should be filled with a color (red, blue, anything), based on another sheet that tells me i.e that the room 1 is occupied from 09:00 to 10:00 and room 2 from 08:00 to 09:00 am.
View 7 Replies
View Related
Dec 22, 2013
I am currently trying to sum up values every day of the week. (salaries )
That means I have a sheet called Salary where I have the entire year mapped out pr. day. Like you see underneath:
Date
30/12/13 0
31/12/13 0
01/01/130
02/01/130
03/01/130
04/01/130
05/01/130
etc.
In another sheet called Overview, I have another column divided into weeks, like this:
Date
30/12/12 0
06/01/13 0
13/01/13 0
20/01/13 0
27/01/13 0
Now what I need to do is, take the 7 days in a week in the first sheet, and summarize it in the first week in the overview sheet. Now that is simple the first time, =SUM(Salary!C1:C7)
But how do I drag down and automize it so that the next row becomes =SUM(Salary!C8:C14) instead of =SUM(Salary!C2:C8)?
So basically I need to autofill with intervals of 7 (the 7 days in the week) on every row in my Overview sheet.
View 1 Replies
View Related
Jan 9, 2014
How to write a formula to calculate how many minutes an agent have been in Open Time by interval ...
Example if I have open time from 9:00-10:00 I need to calculate how many minutes were used from 9:00-9:30, from 9:30-10:00 and from 10:00-10:30
What formula can I use?
View 2 Replies
View Related
Mar 31, 2014
I have a column with intervals(every 30 min) and with some values. My problem is that if there is that if the value is 0, the interval is deleted. Is there a way(maybe macro? ) for the excel to see that an interval is missing and add it with value 0?
Attached File : intervals.xls
View 9 Replies
View Related
Jan 27, 2010
I got 2 fields A2 = Date1 and C2 = Date2
The Date1 field comes from a dropdown list using Data Validation
I want Date2 to also have a dropdown list but to be greater or equal to Date1, is this possible?
Check the following example
View 8 Replies
View Related
Feb 21, 2010
I'm trying to sum each months visa purchases. The data is in three columns: date,value and payment type.
I can't get this formula to work:-
{=SUM(IF(AND(Purchases!C6:C999>DATE(2009,12,28),Purchases!C6:C999<DATE(2010,1,29)),IF(Purchases!K6:K 999="Visa",Purchases!H3:H999)))}
It's returning a value of 0 (and if that were true I'd be delighted!)
View 12 Replies
View Related
