Count Number Of Full Time Periods Within A Time Period
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Automatically Change Cell (With Time) Color After Time Period Has Passed
I have a protected worksheet. Users wish to be able to track changes in the input cells. The suggested approach for this is to temporarily disable sheet protection and allow them to change the font color, then protect afterwards. What I would like to do is: i) check whether they are in an input cell ii) if so, then prompt the user with the 'Font Color' dialog box iii) apply the font color selected to the input cell I'm struggling to find the dialog box I need. I can launch the one to change the interior color, no problem (Application.Dialogs(xlDialogPatterns).Show). But that's no use to me, I just want a color palette that specifically relates to the Font Color
View Replies!
View Related
Allocating A Value To Different Time Periods
I got the following table. http://img163.imageshack.us/img163/2972/oldnf.jpg What formula could I use so that the amount under STP premium would display at the right cell under different time periods depending on the time of the trade date, just like the following images? http://img687.imageshack.us/img687/5720/newcg.jpg http://img168.imageshack.us/img168/413/new1zk.jpg
View Replies!
View Related
Dates Distribution Between Time Periods
What formula should I use if I have a list of maturity dates and current investments amounts as of 06/30/07: 06/30/08 10,000 08/31/14 18,000 05/31/08 11,000 07/31/09 25,000 06/30/10 14,000 12/31/25 80,000 and the amounts need to be distributed in columns titled: Within 1 Yr (From 7/1/07 to 6/30/08) After 1 to 5 Yrs (From 7/1/08 to 6/30/13) After 5 yrs (After 7/1/13)
View Replies!
View Related
Extrapolating Revenue Over Various Time Periods
I have a spreadsheet that holds a list of customers and the contracts they have with my company - this sheet includes the Total Contract Value in £s - so how much each customer will pay us for the service - the period of the service - so the start date of 01/01/08 to end date of 31/12/08 for example. What I then have is all the months across the top of my spreadsheet and I need to extrapolate the contract value, based on the period between the months. Initially I had complicated formulas that simply divide the value by the number of months of the contract - however, my company has since changed the policy and requires it to be done to the day. For example, if the value was £12,000 and the period was 12 months from 01/01/08 - to calculate January 08 it would be - 12,000 / 365 * 31, Feb would be 12,000 / 365 * 29 and so on... Does anyone know how I can do this - bearing in mind that each contract won't be as easy as 12 months - it may be start date of 15/06/08 to 08/10/10 for example... If anyone can help me do a formula or formulae to calculate the value for each month - I would be very very grateful - as always... [/img]
View Replies!
View Related
2 Series Chart For Different Time Periods
I am trying to plot two data series in one chart. The first series has monthly corn prices for last 10 years (~130 data points). The second series has bimonthly chicken prices over the last 10 years ( ~72 points). How can I plot them together? The bi-monthly periods for the second series do not exactly coincide with calender months ( e.g. Aug 19 to Oct 13, 2007). As a result, I can not adjust the points to match with calender months.
View Replies!
View Related
Match IDs Across Spreadsheets By Corresponding Time Periods
There are 2 seperate spreadsheets S1 - "End" No. | ID | Name | Login | Total | Jan-07 | Feb-07 | March-07 | => | Dec-07 S2 - "Datasource" ID | Irrelevant Name | Period | State | Irrelevant Data I need something to go through the unique IDs in the "Datasource" & find a match in the "End" spreadsheet & then insert the number "1" in the corresponding time period (matching Period with Jan-07 or which ever it's suppose to represent). The Period is written in the following format: 122007 for December 2007 12008 for January 2008 One thing, the datasource has dates for both 2007 & 2008. The end spreadsheet has 2007 in one worksheet & 2008 in another.
View Replies!
View Related
Time Trial Period In My Spreadsheet
I've searched a few of the posts here about freebies in terms of adding a trial period to your spreadsheet. I'm just starting to get into VBA programming (beginnner), but I was wondering if anyone here could suggest actual programs or add-ins that I could buy that would allow me to create a trial version of my spreadsheet? either by allowing the user to use it for a few days or to allow the user to use it for a specifed # of times. I'm willing to pay for this, but I can't afford those expensive Licensing/Copy protection programs....I need a simple solution in between $20 and $60....
View Replies!
View Related
Calculating Time Across A 24 Hour Period
Calculate certain time increments for various work-shifts. I have a start time,finish time and increments of time across the spectrum of 24 hours. There are also multiple start time across the 24 hour period with some start times begining on one day and ending on the next day. Example In B5 Startime is 22:00 In C5 Finishtime is 06:30 In I3 increment begins at 00:00 In I4 increment ends at 00:30 The employee working the shift from 22:00 - 06:30 would fall into the time increment of 00:00 - 00:30 where another employee working a different shift (08:30 - 17:00) would not. I'm looking for a formula that would return a 1 in a cell if the employee fell into the 00:00 - 00:30 time increment and a 0 in a cell in the employee did not fall into the time increment.
View Replies!
View Related
Work Out The Value Of Cars Over A Period Of Time
I have a project where I have to work out the value of cars over a period of time. The cars depreciate at 36.9% per year over a 5 year period and I can't work out for the life of me how to create a formula in excel that give me the values at the end of each year. Each car costs £10000 and I need to know what the value of each car is at the end of each year
View Replies!
View Related
Graphing - Selecting Specified Time Period
I have sheet that is populated with data daily. I wish to create graphs from information contained within a table in this sheet where i can select the start date and end date of the graph possibly from a drop down menu. I will be creating multiple graphs and wish for them all to change when i select the time period.
View Replies!
View Related
Count Number Of Time A Text String Appears
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 Replies!
View Related
Automatically Close UserForm After Time Period
I have the following code in a simple userform containing only a label: Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Private Sub UserForm_Activate() DoEvents Sleep 4000 Unload Me End Sub When I invoke this as a single macro in a blank workbook and immediately change focus by pressing ALT+TAB to enter NotePad the userform is shown and exits normally after 4 seconds. However, when I issue this code from within a larger macro, and hit ALT+TAB to Notepad, the excel application hangs on the displayed userform until I click back into Excel. If required, I can happily send you the code as an attachment.
View Replies!
View Related
Chart A Graph Of Jobs Over A Period Of Time
I have an Access database, with a table of jobs processed. One column for Job ID (Unique), Date it was processed, By Who it was processed and Comments ascoiated. I need to chart a graph of Jobs over a period of time. How many jobs done per date. In excel preferebly, I have programs like Database Plus for excel. And I am familiar with VBA. I can't figure out how to uniquely sort how many jobs for each date. As there are numerous job entries for on the same dates. I think it can be done with an SQL query or sorting it with VBA somehow. Any help will be greatly appreciated. Or can anyone suggest an application designed for charting with Access databases in Excel?
View Replies!
View Related
Formula To Be Able To Give The Full Time Rate
I have a spreadsheet, but I need the following formula to make our lives easier here. I have the following columns: Rate, I/T/P/S, FT/PT I need a formula that when I enter "I" for Infant it will give me the FT (full time) or PT (part time) rate. All the columns have drop down menus, so if I choose "I" under the ITPS column and then choose FT, I want the formula to be able to give me the full time rate which is 157.94
View Replies!
View Related
Timesheet: Calculate Time Within A Period When Crossing Midnight
I am creating a timesheet using excel 2003 users enter their shift start/finish time and a break start/finish time. Emplyee's can work night shifts (ie across midnight). There are penalty rates which apply at different times. I need to be able to work out the amount of worked time that fits into a certain time period. eg. 10pm-7.30am, 7.30am-10pm. I have a solution based on A clever formula from Daniel Maher that will calculate time within a period. But it doesn't work when the shift goes over two days. I have attached a spreadsheet to help show the problem .......
View Replies!
View Related
Pivot Tables To Find Max Value In Time Period
I have a in field column M of my table that gives the week number of various dates within the table. I would like to find the high value reached during each week based on the Highs which are located in column P, the highs consist if workdays during each week number. I would like this to appear as a new calculated field entitled "Week High", I am assuming I need to input a custom formula to do this. I also want to create calculated fields for the the high reached during each year, the year is in column L
View Replies!
View Related
Stop Macro Running After Specfied Time Period
I'm opening a workbook and then running the macro in the workbook. The problem is I would like to build a "timeout" feature if the macro is running too long (as some of the macro's this will run can take days to complete) but I don't know how to run the macro asynchronously. Also I need to know if i can get it to run asynchronous is there an event that will tell me when the process is finished. The calling application of the macro is written in VB6 and opening excel workbooks to run the macros out of. also the "timeout" feature must be done from the VB6 application it cannot be edited into the the workbooks containing the macro.
View Replies!
View Related
Add This Number To The Start Time, Factor In Break Minutes And Get To The Projected Completion Time
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 Replies!
View Related
Convert Number To Time & Decimal Time
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 Replies!
View Related
Extract Text Before/After Period (Full Stop)
I have a series of text - domains actually (i.e. goldintre.cr.usgs.gov.) - and I need to extract the text that appears before the first period in one cell, and the text that appears after the first period. Both have to be without the periods. So in the above example the result should be 'goldintre' in one cell and 'cs.usgs.gov' on the other.
View Replies!
View Related
Calculate Future Dates From Start Date With Varying Time Period/cycle
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods. For example: I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is. I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated. I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
View Replies!
View Related
Display End Time Automatically Upon Entering Start Time And Time Usage
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View Replies!
View Related
Count Periods Where Threshold Is Exceeded
I am trying to find a way to identify the nmber of periods where a particular value is exceeded. Basically I have daily data on sales for 40 years and I would like to define a level of sales i.e. 23 units per day and a period i.e. 10 days and then output the number of times where the recorded sales level is greater than 23 units per day for 10 or more days. Ideally I would like it so the sales level and period can be set in reference cells and the formula can adapt to different sales levels and periods. The data is formatted as follows: ...
View Replies!
View Related
Time (clock) Times A Number= Very High Number?
I have time start and time end and the difference between the two times. In a third column I have profit and want to calculate the profit per hour. When I divide the profit with the time spent I get a very very high number. If I for instance type in 1 hour of work and profit 100, then I get profit per hour 2400. (profit/hours spent) On a side note I am trying to get the numbers as 24 hours and not use am or pm and in the spread sheet I get these numbers but in the box at the top where you see the formulas etc. it shows as am and pm. I have uploaded an example.
View Replies!
View Related
Count Time (entries Per Hour)
I have a bunch of data and I want to be able to count the number of entries that fall within each of the 24 hour increments in a 24 hour clock. (military time) For 12:00:00 all times would be between and including 12:00:00 and 12:59:59 Column B | Count ------------------ 12:00:00 344 13:00:00 44 14:00:00 5
View Replies!
View Related
Specify Which Criteria To Count In The Scoring As They Are Not Always Relevant Every Time
I have a scoring system I monitor in excel (I have attached an example speadsheet). I score against a number of criteria e.g. criteria 1 scores "2", criteria 2 scores "4". My problem is that the criteria don't always attract a score, sometimes they blank. I also need to specify which criteria I want to count in the scoring as they are not always relevant every time. I need formulae that will take this into account and be ready to total up only when a score is there (COUNTIF?); and out of the range of scores I want to total up only the specified criteria at choice (SUMIF?). My COUNTIF and SUMIF skills are failing with this which is very complex for my level. What would the formula be in each instance? I have attached the file to reference the cells that I have questions about. Question ?A: - is this a COUNTIF? In this cell I want to calculate the number of occurrences where there is a score detailed, but only for specified criteria, for argument's sake criteria 1,2,3,4,7 & 8 The answer would be 5 in this instance, not 6, because Criteria "3" did not receive a score. I would want to have other cells which would calculate the number of occurences where there had been a score but using different combinations of criteria Question ?B: - is this a SUMIF? In this cell I want to calculate the total sum of scores for those specified criteria where there has been a score The answer would be 13 in this instance (3+2+4+1+3 from the scores that occurred for those specified criteria) I would want to have other cells which would calculate the total sum of the scores but using different combinations of criteria
View Replies!
View Related
Breaking Time Into Intervals And Performing A Count
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 Replies!
View Related
Count By Criteria Of Text & Time
I am looking to create an Excel sheet that can manage the times of access of a certain location. The information is set in a way that there are several rows, and the rows for the time I am keeping track of are in the Row "I". I need to get a count of how many people accessed the location each hour. I have managed to do this using the following formula: =COUNTIF(I:I,">=12:00:00")-COUNTIF(I:I,">13:00:00") This gives me the Count for 12:00pm to 12:59pm. However, COUNTIF can only handle one variable, and I need to put a second variable in it. In Row B, there are several different states. For the most part, they are numbers, but occasionally, there are two separate occurances that I am not supposed to count with the others. I do have to count them separately, however:............
View Replies!
View Related
Count Of Employees That Worked Within Time-frame
I am trying to do a timesheet spreadsheets that lists employees clockin and clockout times Name Start End num hours worked Cory 02:00 04:00 2 Jack 23:00 05:00 6 Fred 10:00 17:00 7 and then go through the list and and count the number of employees in a certain range. time range number of employees working 05:00-06:00 1 06:00-07:00 2 07:00-08:00 6 08:00-09:00 5 09:00-10:00 10:00-11:00 11:00-12:00 12:00-13:00..............
View Replies!
View Related
Copy Row If DATE & Time In Cell Is Between Time Span
I have a problem regarding sorting data having date and also time within a single cell. Example data (I have written it as code to preserve formatting) A B C D E F 12/5/2008 02:072/5/2008 06:0128804833363 22/5/2008 18:012/5/2008 18:0599271297 Column B is start date and time whereas Column C is end date and time. My aim is to cut and paste the whole row automatically to Sheet2 if the time is within 2AM to 8AM else leave as it is.Also I don't know anything about VBA Script.
View Replies!
View Related
Count Within Time-Frame Accounting For Sick Days
Im trying to count people working during times of the day. They work shifts from 8am to 9pm. I need to know at each 30 minute interval of the day how many are working. IE at 12:00 or say 13:30 I need to know how many are working. I also need to be able to account for people that are sick. IE If a person works during the day I wouldnt count them as sick at night. Im data is set up like Column A agent name Column B agents start time Column C agents end time Column D holds two variables either sick or support. I have found a thread that counts the people working but doesnt account for the sick or support variables. I didnt link that thread as I thought it went against the rule "STATE WHAT YOU WANT TO DO, NOT WHAT YOU THINK YOU NEED TO ACHIEVE IT "
View Replies!
View Related
Count Unique Values, And Also Average The Time Per Assignment
I am working on a Productivity Log and can't figure out a way to count unique values, and also average the time per assignment. Assignment with the action code A - should take 2 hrs to complete, all other action codes should take 20 minutes. I need to show how many of each action codes were done for the day, and what was the average time taken to complete each. Even better, how much for A action codes, and how much for all other.
View Replies!
View Related
Skip Weekends Adding Time To Date & Time
I need a formula to add just the time to ' date and time', ignoring weekends. eg: Fri 24-Aug-07 10:52 is the date and time 28:48:00 is the time If I add the time to 'date and time', result is coming as Sat 25-Aug-07 15:40 But it should come as Mon 27-Aug-07 15:40 (hence ignoring weekend)
View Replies!
View Related
Delete Date & Time Cells Based On Time
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too. I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have. Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
View Replies!
View Related
|
TRACKER
