Return Time Intervals Based On X Minutes
Feb 20, 2008
I have a spreadsheet set up that has a series of timestamps and values. Please find a copy attached. I need to set up a macro that will allocate the timestamp in column C to an interval of time, e.g between 12:30 - 13:00.
To achieve this I tried to set the following code up in VBA, which would input a letter in a blank cell based on the time interval that the timestampin column C falls into, for example if the timestamp is > 12:30 and < 13:00 then "A"...the plan was to extend this concept to accomodate all of possible the time intervals I have.
Sub time_intervals()
Dim x As Integer
Dim lastrow As Long
Dim timeA As Date
Dim timeB As Date
Dim timeC As Date
Dim timeD As Date
Dim timeE As Date
The idea was that the timemaster value would be the timestamp in column C of my spreadsheet and the timeA,timeB,timeC etc would reference the timeintervals. However this does not seem to work as the value returned by my if statement is always incorrect - for example timemaster in cell C2 is less than timeA but the if statement always returns FALSE as opposed to "A"
View 4 Replies
ADVERTISEMENT
Sep 26, 2008
08:00 26calls
12:00 50calls
16:30 16calls
The problem is the data is output as above just outputing intervals with data.
I need to convert this data to 15 minute intervals as below:
08:00 13calls
08:15 13calls
12:00 25calls
12:30 25calls
16:30 8calls
16:45 8calls
View 9 Replies
View Related
Jan 26, 2008
1st post. Very basic understanding of Excel / Macros / VBA but I have searched and still not quite able to get what I'm looking for.
I would like to be able to manually put in a TIME in a cell, and have a macro run at set times before that TIME e.g. something like
If TIME in cell A1 =(hh:mm minus 30mins) run macro 1,
If TIME in cell A1 =(hh:mm minus 5mins) run macro 2
It was suggested to me to use vba code that would constantly check the time against the system time and as soon as it is 30 mins before the time in cell A1 and the 30 mins flag in cell B1 was ‘N’ then it would run macro 1 code and set the 30 mins flag to ‘Y’ to show that macro 1 had been run.
and that this could also do the same for the 5 mins event
View 4 Replies
View Related
Dec 7, 2007
Below is the code i want to be run evey 2 minutes Thanks
Sub WebData()
Dim wSU As Worksheet
Dim wSR As Worksheet
Dim wSS As Worksheet
Dim iForRow As Integer
Dim iLastRow As Integer
Dim sURL As String
View 9 Replies
View Related
Oct 10, 2009
I am trying to do is calculate (sum) results data for time intervals. For example, between 0500-0559hrs then 0600-0659hrs and so on. Data is collected at 1 minute intervals and may have a result of 0 or anywhere up to 30.
Column A has all time data e.g. 0500,0501,0502 etc
Column B has sum of instances e.g. 0,1,2,3 etc
View 9 Replies
View Related
May 26, 2014
Count the number of occurences on 1 day within a 60 minute timeframe. data includes dates and times
View 5 Replies
View Related
Nov 7, 2009
I'm the Held desk manager . I'll have my folks logging in every time interval and work for 9hrs. Now I wanted to see at any given point a day the number of folks I have on my desk .
I have created table " A " and I'm looking for a formulae so that I get my data like table " B " .
To give more insight about table '" B " - During 00:00 - 00:30 , I'll have 41 people slowly goes down as people log off..
View 13 Replies
View Related
Jan 29, 2008
I need to compare how many incidents are created within a 15 min interval and populate the sum.
I currently have the create time of the incident in the format below, assume each date below is an incident. I have a column with 96 intervals (24hours) ie 8:00,8:15,8:30,8:45,9:00 etc.... Using a formula how can I say "if create time is between *:00 and *15 count 1 and put the number into the cell and do that for each interval?
A B
------|-------------------|
Int Incident
0 | 8:00 | 1/28/2008 8:32 AM
0 | 8:15 | 1/28/2008 8:35 AM
3 | 8:30 | 1/28/2008 8:41 AM
1 | 8:45 | 1/28/2008 8:46 AM
View 9 Replies
View Related
Mar 27, 2008
The code from this page works perfectly when I am running one macro at a set interval: [url]
However, when I try to run multiple macros at different set intervals the order of execution gets messed up.
For example, I need macro1 to run at 00:00:30, macro2 at 00:00:45, macro3 at 00:01:00, then macro1 at 00:01:30, macro2 at 00:01:45, macro3 at 00:02:00 and so one.
View 4 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
Apr 27, 2014
Formula to calculate time allotted minus time used and show the difference in hour and minute.
View 1 Replies
View Related
Nov 20, 2006
Office 2003 with Windows XP
This is my first post so i may not have done everything correctly. I tried doing google searches to see if anyone else has done something similar but its a tricky thing to search for. I have found pages dealing with manipulating time but none seem to be what I want
I have an Excel sheet with data
A1=date
b1=source
C1-h1 all contain data (name, location, etc, etc) but I am not interested in those.
A1 (the date cell) is formatted like: m/d/yyyy" "h:mm:ss AM/PM
[Sample]
a2 = 8/1/2006 12:20:23 AM b2 = phone
a3 = 8/1/2006 12:21:13 AM b3 = email
a4 = 8/1/2006 12:33:03 AM b4 = phone
This is what I am trying to accomplish:
I want to break each hour, whether it contains data or not and get a count of the different types of source
my desired output would be:
time Phone email
12:00AM
to 2 3
12:30AM
My problem occurs because I am unsure how to break the times down into intervals. In my head, I would create a new column and try to break the time up into an interval. So for a2 (8/1/2006 12:20:23 AM), I think I would need to break the date and time up and then determine which interval would be needed based on the time.
I was going to perform the following:
=text(a2, "h" + if("mm:ss">30, 30,00)
Basically, pull the hour from a2 and evaluate the mins/secs.
If the min/sec is bigger than 30, put a 30 in the box. Less than 30, put double zero in the box. So for 8/1/2006 12:20:23 AM, b2 would have 12:00 since it falls in the 12 to 12:30 interval.
Once I have all the intervals, I would also need to preform a count on the source cell but I think I can use a vlookup to get the count for the interval.
View 9 Replies
View Related
May 7, 2008
I am trying to continually refresh a spreadsheet (it is a countdown timer), and i am looking for a way to do this other than hold "F9". It is excel 97.
View 2 Replies
View Related
Dec 14, 2011
I have 100 Rows of Data with 2 Columns.
The left contains data input and the right Columns contains certain formula.
How do I set it up so that the worksheet can eg. Calculate 5 rows and rest 1 Second and continue this way until it reached the end of the rows? (Number of rows is not fixed).
View 5 Replies
View Related
Feb 18, 2007
I'm trying to do a line chart showing two teams and their cumulative scores over the period of a game. I've attached a sample with mock data. Is it possible to change the x-series of the charts to display custom time intervals like 0 min 1 min, 2 min, 3 min, 4 min, etc? Currently the x-series only has labels for each specific time there was a change in value. I've tried to change the scale but the options that I want aren't there.
View 2 Replies
View Related
May 19, 2008
Currently i have a spreadsheet with realtime data feeds from Bloomberg (or reuters). What i would like to do is:
a. Fill a vector/column of data values every minute until we have 30 observations i.e. from 9.00am till 9.30pm
b. This will then allow me to calculate a moving average of the last 30 (1 minute) observations.
c. At 9.31am, the 9.00am value drops out of the column and is replaced by the observation at 9.31am. This results in a constantly updating column of the last 30 minute observations and will allow me to have a realtime moving average.
View 2 Replies
View Related
Oct 2, 2007
I have a table of repair jobs done over a period of time. I just need to determine during which shift was the repair job done. The time during which the job was initiated is called Notification Time.
If the job was done at, say, 0100 hrs, then it was done during Shift 1 (0000hrs to 0800 hrs)
If done at 0830 hrs, Shift 2 (0801 to 1600hrs)
If done at 2030 hrs, Shift 3 (1601 to 2359 hrs)
I need a formula to allow me to determine the Shift no. by just checking against the Notification Time column.
View 9 Replies
View Related
Sep 19, 2013
I have an issue with identifying start-stop times for special school bell schedule. Cell B2 is contains start time (7:50 AM) and D1 is the establish variable for class length (in this case 45 minutes). Passing time is constant (5 minutes), but needs to be added to the day schedule with the start of each class. I attempted to convert these value to minutes with no luck, same goes to formatting cells.
Hour
Start
End
Class Length
Passing Time
[Code]...
View 1 Replies
View Related
Oct 21, 2008
I have a cell formatted as general that has need to be able to to take 21:56 and convert that to minutes. That is 21 hours 56 minutes to 1316 minutes. Then I need to add those minutes to a time to come up with like 19:14 + 1316 minutes = sometime the next day.
View 9 Replies
View Related
Feb 19, 2010
I have been racking my brains about this for the last hour without any joy. If I have a time value of say 01:12:00 in cell A1 (which is the difference between two other time values), but I want it displayed in minutes, so it displays 72 or 72:00 instead of 01:12:00 (which is 1 hour 12 minutes),
View 9 Replies
View Related
Jul 17, 2009
I have to work out the time difference between 2 times over a 24 hour period.
There is a fixed booked departure time and an actual time of departure, the actual time of departure can be either before or after the booked time.
I need the time difference between the times shown as either +/- minutes at present it returns the time in [HH:MM].
I have a simple IF formula to work out - time but when it calculate + time it returns the reverse difference.
A= booked time
B= actual time
View 8 Replies
View Related
Aug 11, 2007
Is there a formua that can roundup the time duration to the nearest fifteen minutes?
Eg, if the time duration is 2hr 17 min, can it be rounded to 2hr 15 min?
If is 2hr 31 min, to be rounded to 2hr 30 min.
View 9 Replies
View Related
Jan 18, 2014
I have a client where I have agreed to a variable price per hour depending on how many hours is used.
So the first 40 hours of a month costs X USD
The next 40 hours of a month costs Y USD
The next 40 hours of a month costs Z USD
The next 40 hours of a month costs Q USD
And all hours above 160 in a month costs T USD
All hours are registered as decimal with 15 minutes as a minimum, so 0.25, 0.5, 0.75, 1, 1.25 etc.
Now the challenge for me is to set up a formula calculating the total cost based on how many hours is registered each month.
I.e. if I register 46 hours in one month the first 40 hours should be multiplied by X and the last 6 hours by Y. And if I register 173 the calculation looks like this:
40 * X
40 * Y
40 * Z
40 * Q
33 * T
And I can't figure out how to make the formula calculate based on what is between the various intervals.
View 3 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 22, 2014
I have applied the excel suggestion to no success. Currently I take the hour and minutes and then manually use a time conversion chart to add. 3:00 PM to 4:10 PM will reflect 1:10 but adding time should convert to 1:17 (in 100ths)
View 1 Replies
View Related
May 11, 2009
In one of the spreadsheets there is a need to have a 'action at' field.
This is the next five minute time slot between 5 and 10 minutes in the future (eg 10.51 -> 11.00 , 1.56 ->2.05 etc)
View 14 Replies
View Related
Jul 14, 2009
I know how to do this in a worksheet: =(b1-a1)*1440. B1 being the end time and A1 being the start time. I have a userform where the start time and end time are entered in text boxes. txtstart1 and txtend1. I would like the result to show up in txtmin1. Here is my code that doesnt work. I tried to convert code from a non-time sheet of mine. Dim as Integer may be the problem, I just learn as I go, and so far have only dealt with Integers.
View 4 Replies
View Related
Oct 20, 2009
I have a formula that gives me a time value (ie 12:53). I need to perform another formula that will use this data but I need the numbers seperated (12 in one cell and 53 in another)
In essence, what would be the formula to pull off the minutes number and the seconds number into individual cells. I know anything is possible in Excel, I just can't figure this one out.
View 2 Replies
View Related
Jun 4, 2006
If i have a start time of 5:00 am and a finishing time of 4:25 pm, what function do i use to get the total time of 11 hours & 25 minutes?
View 10 Replies
View Related
Dec 10, 2008
Just basic enter a start time in column A a finish time in column B and column C gives you total in hours and minutes and then maybe a way to sum those hours and minutes. how to do "Math" on time and how it should be entered (formatted) for it to work properly.
View 4 Replies
View Related