Find Date / Time Entries Within 1 Hour Interval And Remove All But One Of Them
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.
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.
My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.
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
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.
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
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
I have a column with dates + time under this format: 2005-01-01 00:00:00. I wanted the same thing for the whole year, so, I wrote 2005-01-01 01:00:00. in the cell below, and then did an autofill.
Unfortunately, at some point, the cell goes from 2005-01-05 03:00:00 to 2005-01-05 03:59:59 which is a problem for me, because I was using the hour as an indicator. And hour(2005-01-05 03:59:59) returns 3 and not 4!
I regularly have to add a few new lines to what is in fact a very simple data base I've had running for a long time in Excel. About 1200 lines now, one line per person. I add a dozen or so lines (i.e. people) at a time in a different colour. When I re-sort the whole thing I run my eye down the list to spot partial double (i.e. duplicate) entries (the new ones in their own colour helps). Then I delete the double entries one by one. Pretty stoooopid, in'it?
How can I do this better, faster and more accurately with Excel to find just two duplicated data ( NAME and ID NUMBER) in a person's line (there are 15 columns altogether)? Or: Where and what can I search for (I've just spent nearly an hour trying to find an answer by myself, but don't really know what to search for)?
I have a list of FLIGHT departure times that are listed in MIL TIME, however, there is no : in the format. Its just 4 or 3 digit numbers. I need to convert these to time in 12-hour clock. If I go to FORMAT/CELL/TIME and select 1:30pm it simply makes the time ZERO!
My spreadsheet contains two text columns that represent dates and times; 11/21/2007 and 0935 for example. The rows are populated with the date and time for every event during a 48 hour period. A single worksheet may contain up to 3,000 events (rows).
How do I create a subtotal for the number of events in each of the forty-eight 1-hour periods?
Basically each line signifies a date & time that a deposit was placed and a date & time that a withdrawal was actioned. My goal is to be able to determine what kind of average response time we have between the time a withdrawal was requested and the time a withdrawal was actioned.
I have data exported from a defect management tool (that reports all the defects that have been recorded in the defect management system). This data has unique columns for name of originator, defect ID, date originated, priority, etc. The date originated column has the date and time. I want to create a report that tells me how many High, Medium and Low priority defects were created on each date - and I am using a pivot table to do this.
However, because the date column is actually the date and time the defect was reported/recorded, the time element of this is making all the dates unique eg, I have three defects recorded on 29/01/2009, a High priority defect (reported at 29/01/2009 12:23) and two Medium priority defects (reported at 29/01/2009 13:02 and 29/01/2009 13:32). My pivot table is showing each date as a unique date - with a count of 1 against each, whereas I want to see 1 date entry (for 29/01/2009) with a count of 1 against High and 2 against Medium.
I have tried custom formatting the date column to dd/mm/yyyy - which appears to work in the data (but doesn't in the pivot table, because the time stamp is still there, just not visible). I have tried copy and paste special [Values] (on the custom formatted date column) -but the time stamp is still there. I have tried formatting the date column to "General", but this changes the dates to the number format - eg 29/01/2009 12:23 becomes 39842.5166203704.
I have tried inserting a new column and using the =Left function for the first 10 characters of the cell (ie the 29/01/2009), but that gives the first 10 characters of the number form of the date, ie 39842.5166. Basically, without going into every date cell (and I have thousands) and manually deleting all the times, how do I quickly change the date column into just having the date in it (ie how do I strip off the time element)?
I have a column (D) where I put percentages complete of a task. When that reaches 100% I want to put the date in column (C).
I found many samples online but nothing I can modify to do this.. This one is pretty straight forward but I'm not sure how to have it look for the value and not just empty or not. I would be greatful for any help you can offer.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 4 Then Exit Sub If Target.Row = 1 Then Exit Sub If IsEmpty(Target(1)) Then Exit Sub If IsEmpty(Target.Offset(0, -1)) Then Target.Offset(0, -1) = Date Target.offset(0, -1).numberformat = "yyyy-mm-dd" End If End Sub
How I can go about finding missing entries. I have an excel sheet that has column A as employee name and column E with a date. There are 10 employees so there should be 10 entries for each date. I need to be able to determine which employee hasnt entered information for a given date.
I'm trying to Count the number of Entries on a Sheet that match an Hour. Looking through the availiable functions i found COUNTIFS, which is exactly what I want. However, when I try to compare the Hour values within the COUNTIFS arguments, there is an error.
This is the function that I figured would work here: =COUNTIFS(HOUR(Sheet1!G:G), HOUR(E6))
which should count all entries in column G where its HOUR matches the HOUR in E6 (all are time format). I do realize that in the example above there is only one comparison made and i'm using COUNTIFS instead of COUNTIF, but i'll be adding other comparisons to it once i get this first comparison working.
I have a UserForm which writes data to rows in a master spreadsheet. I'm attempting to write some vba code for a CommandButton in the master spreadsheet which can identify and delete duplicate entries based on "user ID", "Date", and "Time". I would like the CommandButton to retain the most recent entry from a user and delete all previous entries.
My master sheet is set out as such... A, B, C, D, UserID, Date, Time, Response
The users could potentially submit multiple entries on the same day. Ideally I would like to be able to click a CommandButton and delete each user's submission but retain their most recent one (based on "UserID", then "Date", then "Time").
I've searched all day for a solution and I've come close but I can not figure out a code that accounts for my three variables ("UserID", then "Date", then "Time").
I have a list of dates with an hour inside them . I am trying to remove teh hour from the field , and leave only the date. teh format option just hide teh hour but not removing it how can it be done ?
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:
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.
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
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
I need a formula to calculate the time (in hours/fractions of an hour) that is "covered" for each hour of the day between a range of times. In other words, I have a "START TIME" and an "END TIME" and for each hour of the 24-hour clock, I want to know how much time this range covers.
For example, if my start time is 3:30am and my end time is 5:15am, for the 3am hour, the formula would return 0.5 hours, for the 4am hour it would return 1.0 hours, for the 5am hour it would return 0.25 hours, and for all other hours it would return 0.0 hours. The range of START TIME and END TIME can be up to 24 hours (but not more), but the tricky part is that the START TIME can be on the day BEFORE the END TIME (e.g., START TIME of 10:35pm and END TIME of 5:45pm the next day).
