Book1.xls
Here is some dummy data and explanation with my problem...
I can't use usuall sumproduct because array of date doesn't match with data array... Since names are in alphabeticaly order you can see that Dave is sometime at first, second, third position.. there is more names etc...
I need to see how many times some name appear in last x time...
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
DONNAS [Single cell, 1st row] 09/08/2003 15:34 [Single cell ( date and time combined) 2nd row]. Do you know of any way that I can move the name, date and time from the above cells to three separate columns on the same row?
View 4 Replies View RelatedI'm trying to use countif or similar to show how many times a given date is between 2 dates (inclusive). Each row of the spreadsheet contains amongst other info, a Start Date and an End Date. If i've got 2 rows like this:
StartDate EndDate
01/01/2009 03/01/2009
02/01/2009 04/01/2009
I'd want to lookup 02/01/2009 and have it return 2 (02/01/09 falls between the dates on row 1 and the dates on row 2). To give some context, this is an export from a database containing staff holiday dates, and I'd like to be able to see how many people will be on holiday on any given date, given the start and end date of all holiday occurances throughout the year.
In cell A2 I have the date 28/12/11, I need that date to repeat 6 times, then in A8 it should say 01/03/11 6 times and so on , I know this can be done but my brain is on a go slow this afternoon and I can't for the life of me work it out
View 3 Replies View RelatedI have a column that can be found in the example attachment. My primary question is, how would I go about filtering out all of the Excel Dates & Times that do not have a multiple of 5 minutes in the "Timestamp". Can this be done w/ out the use of macros by use of the custom filter? My ultimate goal is averaging every hours worth of data so it would rather be hourly data rather than the 5-minute data given. More specifically I would be averaging the "Flow" data. The example given is actually a very small portion of what I am working with. I actually have a years worth of 5 minute data.
View 6 Replies View RelatedI have a list of product IDs in one col. In another 2, I have date and vendor that each items was sent. Note that each items can be sent several time to different ven for at different date. From this list I would like to build another list that shows me WHEN & WHERE each item was last sent.
I was able to find a formula to return a list of unique item for my product IDs, but get stuck on the next step.
I am recording events on a day to day basis in excel using the first column as the date, second column as start time, and third column as end time. So an entry might look like 1-Jan _ 13:00 _ 13:40, in the three columns across. There will be multiple entries for each date, but the number of entries for each date will vary. Entries may also overlap in times. For example, the next row may read 1-Jan _ 13:10 _ 13:45. Some times there will be gaps such as a third row readng 1-Jan _ 14:15 _ 15:00. What I would like to do is compute the total elapsed time spent on projects for each day, not counting any overlaps. SO even though the total time spent on those three projects for January 1st is 120 minutes, I would like to know how much time during the day is spent doing work, regardless of how many projects are being done at the same time. So I would like excel to be able to tell me that on January 1st , 90 minutes of the day was spent on work. Hopefully this makes sense. I also need it to distinguish between dates so that it can tell me how much time was spent on January 1st. then January 2nd, 3rd and so on. It would be great if this ould be computed as the data is entered. Maybe It could record it on a new sheet with a column for each date and then it lists the time spent on that day in the row below it.
View 9 Replies View RelatedI am looking to total a sheet as follows into a header row - Is it possible?
A - B - C
1)Date - Estimated Time - Actual Time
2)
3)7/27/07 - 2 - 4
4)7/27/07 - 3 - 4
5)7/27/07 - 2 - 5
6)7/28/07 - 2 - 4
7)7/28/07 - 8 - 6
8)7/28/07 - .25 - .4
9)7/28/07 - 1 - .5
I would like B2 to read "7/27/07 - 7hrs, 7/28/07 - 11.25hrs"
and B3 to read "7/27/07 - 13hrs, 7/28/07 - 10.9hrs"
I have a file that samples performance on a cpu. The output shows the sampling date and time in column A. This column changes depending on how long I run the file. No matter how long the column, I only want to pull out the sample date and time 20 times. So if I had data in column A with 300 rows, I want to divide 300 by 20 and pull those 20 numbers and put them in column B in chronological order. Same thing if I had 4000 rows in column A, I only want 20 samples.
View 5 Replies View RelatedI am trying to figure out a difference in hours between date & times.
Cell & Info:
A1 = 07/12/2008
A2 = 02:00:00
A3 = 07/12/2008
A4 = 04:00:00
So in cell A5 i want the difference which is: "02:00:00"
but i dont know how to include the data in the formula....
etc if:
A1 = 07/12/2008
A2 = 01:00:00
A3 = 08/12/2008
A4 = 01:00:00
A5 should be = "24:00:00"..
I need a formula that links to another workbook where it looks in a column for the oldest date which appears more than 10 times.
So if the oldest date was 01/01/2006 but it only appeared twice, it would ignore it, but if there was then 02/02/06 which appeared 11 times, it qould quote that date in a cell in the workbook
I am trying to import a .csv file with several fields that are timestamps. I get the file in Excel, and do Format, Cell, date as mm/dd/yyyy hh:mm.
My results come back as 1/0/1900 12:37:22 AM
My raw .csv field looks like 37:22.4
how I lost my date? I did check my system clock just in case, and it appears to be accurate.
the number of days when there were 0 cases
the number of days there was 1 case
the number of days when there were 2 cases.
As yet there are no days in which there were more than 2 cases but there might be in the future.
I have a list of dates when operations took place in that room. On some days the it was not in use, so those dates don't appear in the list. Some days there was 1 case, so that date appears once in the list. On some days there were two cases, so that date appears twice in the list.
What I've done so far is create a pivot table that contains all the dates, then grouped it by day and counted the number of times there was 1 or 2 cases in a day by hand, then subtracted the total to get the days when there weren't any cases.
I have been using this statement
if(and(c1>=a1:a144,c1<=b1:b144),"yes","no"))
And it just works for the first 2 values c1, c2 and doesn't fit for the others.
The case is i have more than one event at the same video and i need to confirm that no event was taken unless it is between start and end.
Here are some samples:
Start dtime End Dtime Event Dtime
16/09/2013 22:13:34 16/09/2013 22:14:18 16/09/2013 22:13:38
16/09/2013 22:15:57 16/09/2013 22:24:30 16/09/2013 22:16:02
16/09/2013 22:24:30 16/09/2013 22:33:49 16/09/2013 22:17:32
16/09/2013 22:33:53 16/09/2013 22:35:05 16/09/2013 22:19:02
16/09/2013 22:35:05 16/09/2013 22:39:57 16/09/2013 22:20:02
So as you can see there are more than one event between one start and end dtimes.
I'm creating a spreadsheet that has:
- budgeted dollar amount
- entry each week for that week's total expense
- total expense to date
Question: how can I hold the value for "total expense to date" so that when the new week's info is added the previous total is not lost?
I have been using this statement.
if(and(c1>=a1:a144,c1
I have call data in date/time 06/07/08 2:00 PM custom format for a 2 month period. I have my regular opening times eg Monday 9-3, Tuesday 10-4. For each call I want to know if it was made during opening times or not.
View 7 Replies View RelatedI have a list of ID Numbers and Dates. Let's call this "List 1".
I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.
I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.
What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:
List 1
List 2
ID Number
Date
Rating
111
17/04/13
5
[Code] ..........
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,
Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)
One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.
View 5 Replies View RelatedI thought I found a formula that would work, but it's not working. Each month I have to count the number of service tickets that have arrived between certain time ranges. They want to gauge during what times we seem to get the biggest batch of service requests.
6 am to 10 am
10 am to 5 pm
5 pm to 6 pm
6 pm to 6 am
The format of the cells are:
1:21:19 AM
1:28:08 AM
1:35:48 AM
1:49:19 AM
2:17:02 AM
7:14:38 AM
7:29:12 AM
8:08:28 AM
8:51:48 AM
8:54:19 AM
The formula I tried for 10 am to 5 pm: =COUNTIF(B2:B677,">="&TIME(10,0,0))-COUNTIF(B2:B677,">"&TIME(17,0,0))
It gives a result of 676, and I know from manually counting that there is only 327 cells that have a time between 10 am and 5 pm.
I included a spreadsheet that lays out what I am looking to do, basically I copied some times and speed from net. It shows up in non 24 hour format. I need to find the times and an associated speed for each row (day) directly before and after my inputted desired time. I've searched for days, tried different formulas with index,match, lookups,timevalue, time, etc and even tried using other peoples vba code without success.
View 9 Replies View Relatedi need to calculate between time. If a person is working between 8 till 12 and then 1 till 5 i need to count the instances between these times.. so if somebody is working between these hours a 1 should appear and if they have finished their shift or are on lunch then this should change to 0
e.g.
8.00 | 8.30 etc.. 12.30 | 13:00
1 | 1 | 0 | 1
First and foremost I would like to congragulate you on this wondeful piece of code in the below link...
The query was to get a future date excluding Fridays and Holidays...
http://www.excelforum.com/excel-work...rkingdays.html
I have a similar query and therefore I pasted this link...
I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..
Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..
The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies View RelatedI'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.