I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.
View 3 RepliesI've just started working on an FTE calculator and wish to populate a 12 month calendar with FTE depending on the start and finshed dates.
FTE Calculator non nursing.xlsx
I've attached the file. In Column D the user would select the month the staff start and in the Column E the month the staff will finish. I would like the fte that is calculated in Column Z then to populate in the 12 columns AB:AM (Jul to Jun) with corresponding month start and finish.
how to start and name a new project in Excel VBA. Will someone explain how to?
View 3 Replies View RelatedIf you look at the file I attached you will see the tabs at the bottom are months of the year. I know how copy a sheet but every time I make a new month tab I have to retype the month in the top left corner of the table, in the tab, and other places. This gets really repetitive when I have to do this for literally around 20 other spreadsheets like this.
What I'd like is a way to create new month tabs that automatically rename the month and year in chronological order on the sheet and tab if possible. 2 months it's because I'm constantly having to tweak spreadsheets for my boss. It'd be pointless to go ahead and make a template because then I'd have to go back and change the code for every month. So I just create tabs and edit code month by month.
heres the data: [url]
Im meant to produce a simple spreadsheet that calculates the floor area of a new build city centre hotel. The developer is looking at various plots of land that allow differing sizes of floor plates and storey heights. The key variables are the number and type of bedrooms, number of floors and whether the hotel is classed as a premium or budget hotel.
I need to produce a spreadsheet that shows the key variables and the total calculated floor area at the top of the sheet.
I need a formula that calculates the total rent amount for particular dates. Example would be a formula that includes a date starting at the first of the month (October 1, 2009 to September 30, 2010) and also would include a date starting at a date later than the first (October 3, 2009 to September 30, 2010). The figures needed would be the monthly rent (ex: $1,000) and a pro rated rent amout for a lease that starts after the first of the month. The pro rated amount would also have to include the years with a leap year. I have been working on this but I know there has to be an easier way.
I am making a 'sports day' spreadsheet for a project and i have one problem, the spread sheet is meant to have the possiblitlity of mistakes reduced to a minimum.
I have 6 teams in the sports day and under each i put 1st, 2nd,3rd,4th,5th,6th.
[url]
basically i need the rows to only accept 1st 2nd 3rd..... only once. I need something like an error message to come up if a value is entered twice on a row.
i'm using a macro in excel 2007 that will open a Microsoft Project file, and go to the filtering menu. This is working fine however i have come to a deadend as i need the answer from a Data validated menu to be inserted into a box that project creates. Basically i need to copy the text from my excel (worksheet 2, cell c2), into my vba formula so that it automatically inserts into the MS project box. I hope this all makes sense and would be massively gratefull if you could point me in the right direction.
View 9 Replies View Relatedcalculating project completion % based on Project start date and end dates .
View 5 Replies View RelatedI need to sum the total hours by project between two dates. There can be multiple projects and the two dates can vary. So...
In column A are the project # (say A2 001, A3 001, A4 002, A5 003)
In column B is Yes or No for each project (contract Signed?)
In Column C through Z, row 1, are dates (shows the Monday of each week)
In Column C through Z, row 2-5 (which corresponds to the projects 001, 001, 002, and 003 above) are the number of hours worked that week.
On a separate tab (lets call it MonthTab) is the start and end date for each month (Jan through Dec)
On a separate tab I want to SUM the hours by Month for each project (so for project 001 would need to go across multiple rows), that has a Yes in column two. Therefore the hours summed For January will be taken from MonthTab and be between StartJan and EndJan.
How to achieve the same as in the linked thread below, but in weeks instead of months? I'm very insecure about how excel deals with weeks.
[URL] .....
I think this should work but it doesn't.
=AND($B4>=D$2,$C4<=D$3)
Should look like this
Excel Conditional Formatting.png
Attached I have a spreadsheet with two columns. Both are dates. One is Due Date and the other is Completed Dates. Each row represents a WO (each WO has a due date and complete date). This is what I want to extract:
- I want a count of how many WO's there are in the spreadsheet to start with
- then I want to to be able to apply this fillter = Complete Date is equal to or less than Due Date (basically, I want to see how may WO's where completed after the due date).
- then I want the spreadsheet to automatically calculate the count of WO's with the filter applied
In First Part
I Have R1 1 -17
R2 20-22
I Want When I Write R1 1-17 in cells {B1:C1}And R2 20-28 In Cells {B2:C2}
Start Automatically Write From 1-17 in Column E and 20-25 In Column F in The Same Manner You See in The Pic He Ignores F18,F19 And Start Counting From F20
And Then Color The Common Area Which {E18:F19}
And Make The Same With The Second Part The Pic Which {G1:I2}
I need to compute the no. of days between start and end dates and spread no. of days on each month. I've attached a spreadsheet with examples and further explanation.
View 3 Replies View RelatedI have some Start & End dates with some values. Based on lookup dates I like to get third column values.
Start Date
End Date
Places
Jul 1 2011
Jul 25 2011
A
Jul 5 2011
Aug 25 2011
B
Jul 7 2001
Jul 15 2011
C
Jun 29 2011
Jul 5 2012
D
A2:A5 Start Dates
B2:B5 End Dates
C2:C5 Places
E2:E3 is lookup dates, Jul 6 2011 & Jul 7 2011
So return values from column C fall under these dates.
Answer is A, B, C
I tried this formula
=IFERROR(INDEX(C:C,SMALL(IF(MMULT(ISNUMBER(MATCH(A$2:B$5,ROW(INDIRECT(E$2&":"&E$3)),0))+0,{1;1}),ROW(C$2:C$5)),ROW(A1))),"")
But this wouldn't work as expected. Only return C. I am using excel 2007.
I want, based on a given date let's say 6 months ago from today (03/01/09), a formula that tells me the name of the person that finished Between the started and the GIVEN date (03/01/09). The result should be: Name2
I can also use a formula that shows, condition results such as: True..."OK", false "out of range".
I have a list of employees and the dates on which they were absence from work for a set period (i.e. one month). Some employees have been off for a day, some for longer. This data has been pulled from a large datasheet and now needs formatting to add in the start and end dates of absence, ready to upload to a payroll system.
The Problem:
I have been able to get a macro working on a simple list of names, however the problem arrises where I have 2 employees off on the same day - I was using a loop to find the date last used, but this no longer works.
My Request:
A copy of the worksheet in question can be found here : http://www.carpe-luna.com/other/AbsenceQueryHelp.xls
But I'll try and describe the layout as much as possible
This is how my raw data is set up. (Up to row 50 but potentially more)
A.........B...........C..............D...........E.........F
EmpNo.....EmpName.....Start Date.....End Date....NoDays....NoHours
612.......Paul........26/01/07.............................12......
612.......Paul........27/01/07.............................12......
615.......Ian.........27/01/07.............................12......
758.......Peter.......16/01/07.............................12......
758.......Peter.......17/01/07.............................12......
758.......Peter.......18/01/07.............................12......
...........................................
I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedMy task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook
[code]....
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Number
Project Description
...
1111E.000000001
[code]....
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
I'm currently working on a rota for which I would like the worksheet to automatically tell me how many staff members I have beginning their shift before 9am and those finishing after 7pm. I currently have this working via a very crude set of IF statements for each staff member for each day of the week, returning 1 if true and 0 if false. Then I have a sum statement at the bottom of each day. Is there a much tidier and simpler way for me to calculate this?
I've attached the worksheet. A quick note is in Q52.
Any macro or a VB script to fill the numbers in between the "start" and "end" range . The only input that will be provided is the start and end of the range.
For example if the start = 100 000 and end = 100 010.
The output should be
100 000
100 001
100 002
100 003
100 004
100 005
100 006
100 007
100 008
100 009
100 010
Within range Sheet1!D4:D423, how can I have a macro called RestartClock start any time the Enter key is selected within a cell for that range?
View 7 Replies View Relatedi am trying to both identify the start date and the end date of a series in excel.
if you will, picture a spreadsheet with two rows with data. the top row is comprised of dates in mm/dd/yy (end of month) format. the bottom line is percentages . . . a corresponding % for each date directly above. these percentages add to 100%. in two separate formulas, I would like to identify:
1. the date in which the first %>0 occurs (start date)
2. the date in which the cumulative addition of all months %'s equate to 100% or the date in which the last %>0 occurs (end date)
keep in mind many if not most of the %'s will be 0%.
Running reports with start and end dates, example I need dates to Start 10/01/2010 and end date of 09/30/2011, retrieved date has some dates before 10/01/2010 so I need a formula to change that date to equal 10/01/2010 and the same for the end date need to have that date change to 09/30/2011.
View 3 Replies View RelatedI am trying to automate calculation of IRR for a range of scenarios.
I have a bunch of problems with this:
1. On a full cycle basis, the project start dates are not equal which means that in certain scenarios I get 0% return as the first cash flow is zero.
2. On a point forward basis, negative cash flows may start after T0 (i.e. now)
I have circumvented this on NPV basis by using a Sumproduct of individual cashflows, discount factors (full cycle cashflows are inflated to T0) and a proportional period factor (where T
I have searched for a 'simple' solution to this but I only ever seem to find complex and indepth examples.
I summarise web hit information then chart it so I can look for trendlines. I get good info from the stat company so I can see where folk are coming from, search engine, weblink etc etc.
Once I have summarized it I have date running along the column headers then it is broken down through the rows so I have
Total Hits
Total Paid Advertising
Total Search Engine
And so on for my rows
As the days go on the data gets harder and harder to read so I was hoping I could use some kind of OFFSET function to select my data. So if my dates run from 2/11/07 to today I would like to perhaps say to Excel that I might want to see 20/11/07 to 20/12/07 in my chart.
I have plenty space to put the start and end date and I have a basic block of data.
How do you subtract start/end - dates/times in vba?
I know how to do it with formulas, but can't grasp it with vba. My range will always change, so I'm trying to avoid formulas (Cutting rows etc...)
I need Col D-Col B (end date-start date) and Col E - Col C (end time - start time)
Would doing the calculation on the userform be the best way, or trying to do the calculation with the ws code?
I am trying to get excel to open a MS Project file and then run a project macro on it.
What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.
For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder
EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END
[code].....