I have to dig out data for all employees first entry and last exit for two months. My raw data is in sheet 1 as under:
LAST_NAME
FIRST_NAME
EMPLOYEE
[Code]...
I am using excel 2010
Im having a hard time creating the formula to calculate the min number of employees to complete a number of jobs that come in each day and need to be finished in 18 days or less.
Data that I have:
Forecast of the number of jobs for 251 days ( jobs range from 10 -80 per day) Each job needs to be finished in 18 days or less we need to staff a fixed number of employees for the whole year each employee can complete 4 jobs per day no weekends
i have excel 2010 and crystal ball
I'm in Excel 2010, and the cell with the date I want to work from is H22.
I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,
"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
I should also probably note that the date in H22 is the result of another function.
=EDATE(G22,12)
I have a calender with days in the columns and months in the rows. However I have 3 rows for each month. I'm using text and values combinations in the cells to record data
Example First Sheet
Feb Rows, 11,12&13
Columns C,G Dates 1st to 5th
Therefore 1st of Feb has 3 cells to enter data, ie Basic Hours, Sickness, Holiday
Currently I'm defining a name range for each month, ie DFeb. Currently I'm recording each of the rows into columns in another sheet and the date in the row, by using a Hlookup on the date and a vlookup on the month
ie Second Sheet...............
I am using Excel 2010. I am digging through a workbook with 80-some worksheets. There is one worksheet with 11 data tables. When the workbook refreshes, there is a note at the bottom saying something about 21 data tables. I am not sure where the other 10 are. In an online post, someone said that the Name Manager should show them. But in Name Manager, when I filter on Tables, the list goes blank, and all the icons are the same for the other named ranges. Is there another way to find the data tables?
View 9 Replies View RelatedPlease see attached spreadsheet on what I am trying to achieve.
In the data tab I have my base data, which is dates across the top and names down the side. I have information in the middle such as 'H8' which means that the person was on holiday for 8 hours on that particular day, and 'S8' which means they were sick for 8 hours on that day.
I want a macro that will rearrange the information in the table and put it into the report format which is in the report sheet.
If you see the spreadsheet you will probably get a better idea of what I am trying to achieve.
I have a spreadsheet with 6 months of data in one row. the dates are from A3 (Jan 1) to FY3 (Jun 30). The data is in range A4:FY4. The data is a "$" (without quotes). The $'s are entered randomly during the 6 month period. I need a formula to determine the days between the last occurrence and the next to last occurrence of the $. Example spreadsheet shows the $'s on Mar 28 and May 5 as the last two. The spreadsheet is attached. I am using Microsoft Office Excel 2010.
View 6 Replies View RelatedI am using Excel 2010. The template only has 5 working days in it, but I need to set 6 working days (Saturday is also working day). Wondering, how to get this working without spoiling the functions. Also the date format is in US , need to change to UK without disrupting the Formula/Functions.
View 12 Replies View RelatedWorking on Excel 2010
Need a formula to calculate the number of leave days.
C3 - Start Date, e.g. 01/07/2011
D3 - Calculation date, e.g. 31/12/2012
E3 - Number of months - ??????? Forumal need from 01/07/2011 to 31/12/2012
F3 - Days per month, e.g. 1.25
G3 - E3*F3
Using Excel 2010.
I am trying to come up with a formula that will return a total average from two columns of dates with criteria. The range will need to cover an entire column as my data is continuously growing and the criteria would have to limit the start date to each month. I have tried
=AVERAGEIFS(DAYS360(A:A,B:B),A:A,">5/1/2012",A:A,"5/1/2012",A:A,"
I'm using Excel 2010 on Windows 7.
I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.
Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.
It would look something like this
Sheet1
Lastname
Firstname
Device
Johnson
[Code]...
So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:
1) A team member could have multiple devices
2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.
I have small bit of import code (below), that I am looking to modify. The data that I am importing is for an inventory tool that determine optimal on hand quantities and alerts to any issues. In the past there had been one inventory source (one .csv data file). So I populate that to one sheet and modify the data on that sheet. I now have multiple data sources that I need to address.
There are two things that I would like to add/be able to do.
1) I would like to add a dropdown or set of multiple buttons to the popup window that says "Select the OnHand Report". Previously I only had one report source which I posted to sheet "orow0205". I have 2 other data files now that I have to import and they need to go to sheets "orow0206" and "orow2144". I am grabbing the data the same way on each sheet so that doesnt need to change I just need the option on the pop up window to determine the sheet name I want the import to go to.
2) The files that come from the sources above have a file name like "20121213_00000_groupstatistics.csv". Where the first eight digits represent the date the file was generated. If possible, after I select the sheet via the popup solve above, I would like to have the date populated in "MM/DD/YYYY" format in the last column of the imported data (the same date for each row of data imported). The file imported has no date data on it and can represent any day (which is why I am not using some form of TODAY formula).
I am currently using Excel 2010.
Sub AddData()
' Import_New_data
'
'Open Datafile
[Code]....
I have a sheet that contains a Microsoft Query (SQL) data connection. In cell C3, I have the month name and I also have a year cell (C4), which contains just the year based on what the current month (=YEAR(TODAY())) is.
For this example let's use May (C3) and 2013 (C4)
C3 and C4 are used as parameters for the SQL query, so if I change the month to January and the Year to 2010, data is refreshed to bring in that data.
Works smashingly.
I then want to add 11 more sheets (12 total), so each sheet is a month. The value in cell C3 on each sheet will be the month name.
If I just copy this sheet 11 times, Excel adds 11 extra data connections; 1 for each sheet. As the query in each sheet needs to be exactly the same, I don't want this. I want the 12 sheets to use the same data connection, only with the specified parameters at the sheet level.
Otherwise, if I need to add or remove a column, or change a column name, rather than just doing it once, I'd have to do it 12 times.
This can't be right, surely, as that would be the most ridiculous design flaw I've ever seen in a piece of software. How can having the same query 12 times be a good thing?
I'm a bit concerned that googling 'excel data connections multiple sheets' always wants to be 'excel multiple data connections one sheet' - I want 'excel multiple sheets one data connection'
I need conditional format in column Q which highlight in red, any date over 14 days old.
This column also contains texts (non-dates) and these need to be left alone.
I've experimented with a few bits of formula I've found online, but nothing has worked . .
I'm on Excel 2010.
I got a master format in xl2010 to collect data, which is being circulated between my team. members fill in their respective data n mail back the file with their name attached to file name. i want to creat a macro which can copy the data from every members file to master file.
View 1 Replies View RelatedI have a long list of data with many columns and I'd like all the information to be in one column without manually copying and pasting each column and adding to the first column. The data has different amounts of rows and columns as well. An Example is below. I'm using Excel 2010. Is there a formula or something for this? This isn't the data I'm using but just an example since I do this frequently.
Data Looks like:
54654
31233
42343
51234
66968
43252
54657
63253
[code]....
Would like to look like:
54654
66968
79282
91596
68185
31233
[code]....
I know there are many ways to create an "All Data" worksheet. Copy & Paste is the most obvious or pasting named ranges into the new worksheet. I have a workbook with 48 tabs with up to 1000 rows of data per sheet. I need to merge each tab into one main "All Data" worksheet.
How to combine these 48 sheets in an easier way than the two options I already know (Copy/Past or Paste Named Range). Any Add-In's to Excel that can possibly do this? I am using Excel 2010.
How to make this "All Data" worksheet combining data from each sheet of the 48 tabs would be most useful.
I'm trying to create some vba code that will go into other .xlsx documents, pull all cells with a value in the first 30 columns and then return them as a table. Basically each work crew has a .xlsx spreadsheet containing their schedule and I am trying to bring them all together into one nice little package. If I go to import from external sources then click XML data, navigate to the folder they are in and them click show all files and pick my spreadsheet it imports nicely. Then I go to the next blank cell in column "A" and repeat for the 4 crews. Now I can filter by each crew but all 4 are visible on a single page as a table. I recorded a macro that shows everything I did, but it is not dynamic (the .xlsx source files name changes based on the month) I'm running XP sp3 with Excel 2010
Here is a copy of what I have
Code:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsUSERNAMEDesktopSCHEDULE STUF" _
, _
"FAcrewCurrent.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
, _
";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;J" _
, _
"et OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt " _
[code].....
Is there a better or simpler way to do this? The tables don't need to be forever linked to their previous spreadsheets so if copy/pasting is a better way than I am all for that as well.
Excel 2010 Higher than past average formula?
Im using Excel 2010 and want to make a formula that will High light the number when it exceeds the past 30 days average. My information is listed vertically in row F..
I am using Excel 2010. I am tracking client orders on a spreadsheet. I would like to offer a 60 percent rebate on revenue from orders placed between the first order date and 30 calendar days thereafter. On day 31 and continuing through day 60, I would like to offer a 40% rebate. On orders placed on day 61 through 90, I would like to offer a rebate of 20%. From all of the orders placed by clients who start ordering on different dates, I need to sum their order revenue in these three time periods. I have the individual client order data arrayed on my spreadsheet - one order per row.
I am capturing the unique client ID number (Column A), the order date (Column B), and the associated revenue (Column C). Keying off of their unique ID number, I would like to calculate the sum of how much revenue is generated in calendar days 1 through 30, days 31 through 60, and days 61 through 90 from the very first order date from any given client. The start date can be any date in the month, so I can not rely on any calculation that uses the calendar month. Orders are not necessarily placed every day. Multiple orders may be placed on any given day, and then no orders may follow for many days afterward.
I'm having trouble defining the time overlaps on continuous days for the varying results of the runs for my simulation.
For the following data set, without counting overlaps there are 18 total spare requirements, but if I take into account their overlaps in their duration there should be a total of 11 per day. For example in row 4 this event overlapped in the same day with the event of row 5 so both had only 2 overlaps as a result (So there is not possible to be 1 as an answer for overlap, there is only from 2 to "n" amount or 0).
But if an event overlaps with more than one in their duration the result would be their maximum overlap during this time frame.
So then I have for each event an Starting date, starting time, duration, ending date and ending duration.And for each day I would need the total amount of overlaps occurred for the duration of each event and from those I would take their maximum amount of overlaps occurred per day. To obtain their real requirement as shown on the file.
The manual procedure I'm using looks like this for the first day: (Graphical representation)
Event 1 occurs from 17:02 to 20:29 and only overlaps with Event 3, so it's 2
Event 2 occurs from 20:38 to 23:38, also only overlaps with Event 3, then its also a 2
For Event 3 in its whole duration only overlapped with Even 1 and Event 2, one at a time so its maximum overlaps where only 2.
And that's what I'm using so far to determine these amounts but since is a simulation, I would end up with several data from each run and this method would be too long. That's why I would need this template to paste data for each run (15 days) and obtain my results faster.
Every day I receive a csv file of NAMES, PLACES, ADDRESSES, IDS, SPECIAL ID (ETID), ITEMS, QUANTITY, AND OTHER INANE INFORMATION. which is a list of people from places that are ordering item(s) for each ETID)
I CONVERT TO EXCEL BECAUSE I HAVE TO CLEAN THE DATA FORMATTING, AND SPLIT A COLUMN INTO 2 (LAST NAME AND ETID ARE TOGETHER).
I need to put the items into a form, one order per ETID.
I tend to receive on NAME, PLACE, ADDRESS, sending orders for multiple ETIDS.
I don't know the easiest or best way to get the info into the order form. I have designed the form in word and excel.
I have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.
View 4 Replies View RelatedExcel 2010 - I need to create a graph that shows a week's use of a sportsground showing what sports were played, on which days and between which times. I thought I could show the times on the vertical axis, days on the horizontal axis and then the sports played in those times, however I'm unsure as to how to get the graph to recognise the times.
I've attached an example of some data and how I'd like to see it, I'm just not sure how I can do this using the chart set up!
Attached File : Graphs.xlsx
We have a master worksheet, and I cant let staff see it, but I want them to update 'their bits' themselves, rather than email the admin staff and let them update the master sheet..
every job is on its own row and contains a unique ID.
is there a way i can merge their data in to the correct row and column(s) on the master sheet?
i've been playing with datasources etc
Excel 2010
I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.
I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).
For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?
Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1
Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2
I wanted to determine the number of days between two dates. Specifically, if the initial date is in one month, and the second is in a different month and an output would result a number of days in each month until the final date. BUT I have a large amount of data to do this for in a list view, way to put a formula in excel and just drag down the entire list to get the required information. see below for an example.
The result I'm looking for is the separate the months and only show the relevant months between the two dates in one cell or the adjacent. Something similar to the table outlined below.
Input Data
Result
Start Date
[Code]....
I'm trying to create a database which can analyze data for my thesis.
The idea is the following:
I have all data saved on multiple work sheets in one workbook.
I would like to use the find function and when I search for specific data this is either summarized in a new sheet or else can be printed.
I am trying to use the code that can find the last used row in particular column.
Here is the code in module1
Code:
Function LastRow(rng As Range)
Dim temp, temp1
Dim col As Range
With Application.Caller.Parent
For Each col In rng.Columns
temp = Cells(Rows.Count, rng.Column).End(xlUp).Row
If temp > temp1 Then temp1 = temp
Next col
End With
LastRow = temp1
End Function
and code in the range
Code:
=LastRow(V12:V300)
My problem is, this code was working nicely but now it only shows ###.
PS I use MS office 2010
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using Excel 2010.