I 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 two problems with my PivotTable: 1. My PivotTable consists of a constant number columns and varying number rows, eg.......
The dates (with data) should continue to update as I add new date records to the source datasheet, but they don't. I try the refresh option for the table, but it only updates for the dates that it was designed for. I also have automatic updates set, but so far nothing is working. I have to rebuild the PivotTable each day after I add new data...
2. Second problem are the date labels on the rows. I need to have them include the literal day of the week such as
Thursday 01/01/09
Friday 01/02/09
Another less urgent problem that I would like to solve is that I have included additional percent calculations for the total column that I am having to rebuild each time. Can that be included in the PivotTable as well?
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.....
I'm using excel 2008 for mac. I've got a spreadsheet with around 4000 rows. The data is organized like this:
|___________URL_________|______EMAIL______|
|Example Domain
|Example Domain
|Example Domain
|Example Domain
|Example Domain
What I'm trying to do is concatenate all of the URLs into one cell so that each email address has only 1 single row with many URLs associated with it. I tried using VLOOKUP(B1, $A$1:$B$4000, 1, FALSE) but this didn't work. I thought it was a long shot anyway. I also tried writing a bash script do parse a csv with this data and create a csv file with the URLs concatenated with no luck. I'm trying to get my data to look like this:
|___________URL_________|______EMAIL______|
|Example Domain
|Example Domain
|Example Domain
|Example Domain
|Example Domain
Where these are 2 adjacent cells.
EDIT: The format didn't turn out like I'd hoped. In the first example, each URL has an associated email address, each in its own row. In the second, it is just one row, with multiple urls in the first cell and an email address in the second cell.
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.
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 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 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 RelatedIf you look at the attached file you will understand what I am trying to get at more. I have a download which comes from a time management system. What I want to do is have a macro which runs everytime the name dropdown box on the summary page is changed. The macro needs to lookup the correct times for that person and the day and place them in the correct order (i.e 1st clock in, 1st clock out, 2nd clock in, 2nd clock out, etc.) I am trying to find the best way to do this.
View 3 Replies View Relatedi've been viewing different formuals, and have to admit there is alot more to excel than I originally thought, and totally stumped as to what and how to go about my problem.
I have a problem, and it mainly stems from my employer not paying me correctly (and pretty much the other 1600 odd employees encounter the same issue too on a semi-regular basis).
To give you a run down, this is our complexed pay system
Mon-Fri, if you work up to 7h36m you get 100%, then 150% until 10h36 and then 200% after 10h36m working time
Sat, if you work, its 150% upto 7h36m and then 200% there after
Sun, double time all shift
But to throw in some complicated things, here are some more items that need to be considered.
If you work from one day into the next (say fri into sat, and sat into sun and sun into mon, then after midnight, it then goes to that days rates)
If you work your day off, say a weekday, your then paid the eqivelant of saturdays rates unless you were called in straight away which then is sundays rates
If you work your day off, say its a saturday or sunday, then its 200% for the whole shift.
And then public holidays, its 250% upto 7h36 and 300% thereafter
So as you can see, its pretty complicated, and my employer (a government employer) can't seem to get the right software to pay us correctly, nor can the poor employee sometimes work out if they've been paid correctly or not.
So, this is what I have so far:
columns: Day, Type (normal day or work day off or work day off immediate), On1, Sign Off1, Sign On2, Sign Off2, Sign On3, Sign Off3, Working Time, Paid hours
(for instance its my normal rostered monday, I start at 12pm, lunch at 5, return at 545 then finish at 915pm, which equates to 8h30m working time and 8h57m paid time) - the 3rd sign on and sign off is if we do extra work beyond our shift.
There are other things aswell like allowances, but thats for another day, and not as urgent as what i'm really looking for.
Now if anyone can understand what i've just written above, your doing pretty good (even some of the most experience employees still don't understan our pay system), and anyone able to produce a formula for what i'm looking for, i'd be exceptionally grateful, and probably the 1600 odd other employees too who seem to get shafted occassionally.
In a single employee Timesheet spreadsheet:
....................Mon.....Tue.....etc.
In:................6:65.....6:71
Out:............12:07....12:05
In:..............12:62....12:41
Out:............15:66....15:73
Hrs Worked:.....8.46......
How to format the cells in the daily columns and what formula to calculate 'Hrs. Worked'?
I have a workbook with a tab called "parts list" where there are thousands of parts with prices. I need to sum all of the parts based upon their location within the list. The list is divided with the following headers - Yellow (Assemblies), Gray (Sub-Assemblies) there can be many under a yellow header, and Green (Components) there can be many under a Gray header.
I have been summing the areas manually, and I am hoping for a more automated way of creating the totals by running a macro.
I need to sum (Column K) from Yellow+1 to Yellow-1 and deposit the sum in the starting Yellow row - Column L. This needs to be repeated for as many Yellow Headers as there are in the list. I then need to repeat the process for Gray Headers - Col K, Gray+1 to Gray-1, and put total in starting Gray header, Column L. Once again, there can be many Gray Headers under a Yellow header, and this needs t be repeated throughout the list. I then need to repeat this for the Green Headers - same as above Gray. I am hoping that this can be done by color, but if it's easier, I can sort the list by color and in column M, add an "A" to all Yellow Headers, "S" to all Gray Headers, "C" to all Green Headers, and "E" to represent the end of the component list -
Is it possible to have the user inputing a value in cell $G$8 and have the range B11 to G11 to be copied x time depending the value of Cell $G$8 down. ie: Value is 5, it will be copied on 5 rows under B11 to G11??
View 3 Replies View RelatedI have a spreadsheet with 4 columns - Start Date, Start Time, End Date and End Time. In the 5th column, I need to fill in the "Duration" which is calculated as follows :- Duration = (End Date,End Time) - (Start Date,Start Time)
View 2 Replies View RelatedIn a single employee Timesheet spreadsheet:
....................Mon.....Tue.....etc.
In:................8:00.....8:30
Out:............12:00....12:00
In:..............13:15....13:00
Out:............17:45....17:00
In:................0:00....19:00
Out:..............0:00....20:15
Hrs Worked:.....8.5......8.75. How to format the cells in the daily columns and what formula to calculate 'Hrs. Worked'?
With reference to attached file. I want to copy column D&E for 'n' number of times of value based on B1. If value in cell B2 is 0 then hide cell D&E and if there is any other value, excel to copy E&F to next column (leaving one column blank after each paste).
A
B
C
D
E
2
Product A
Price
VTA-2001
999
VTAA-2001
1299
XMV-1001
1000
Attached is a sample schedule form. I recived the data on the "Working" sheet in a Word file that is emailed monthly. THis shows a full month of arrival/Departure and passenger counts by Time of Day. I need to develop
1. a driver schedule that would indicate the time the driver needed to be at the station and how many passengers to collect. There could be both and arrival and a departure trip at the same time so this would also need to be considered. The sample data shows when passengers arrive and when they are expected to leave. Some will leave the day they arrive some the next day. Ideally I would like to be able to plan the number of trips to the station per day and at what times, whether it was an Arrival, Departure or a combo trip delivering departing passengers and collecting Arriving passengers on the same trip.
2. A housekeeping schedule also needs to be created showing how many rooms need to be available for passengers and at what times. It would also need to indicate if the passenger arrives and departs on the same day or if they stay over.
I enter a part number in "D1". The number of components required for that part number is returned in "G1" based on a VLookup. I would like a macro that will copy and paste the part number I enter in "D1" in the first empty cell in column "D" and will paste it once for each component (G1) required. For example, if the number of components required is 4, and the first empty cell in column "D" is "D10", I would like to paste the part number in cells "D10", "D11", "D12", and "D13".
View 2 Replies View Related