# Distribute Project Hours Automatically Across Weeks

Mar 27, 2013

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] .....

## Evenly Distribute Hours Over Month

Dec 9, 2006

I am currently tracking our company's service tasks in Excel and would like a formula or method that will take the total hours a task has been approved for and equally distribute the hours into columns under each month.

Column A = Resource
Column E = Approved Hours
Column F = Start Date
Column G = End Date
Column H = Jan 07
Column I = Feb 07
Column J = March 07 etc. etc.

I basically want to take the approved hours (Column E) and have those hours automatically calculated and filled into the month columns (Column H forward), as per the start and end date fields (Column F & G) so the hours are evenly distributed over each month.

## Sum The Total Hours By Project Between Two Dates

May 30, 2007

I 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.

## Taking Value From Another Sheet And Multiplying (cost X Hours) And Combining Days / Weeks

Jun 18, 2014

I have tried to make a sheet which has

Resource, Task, Rate (I'd like this to be on another sheet, as there is a lot of repetition) Also, right now the columns for hours are based on weeks, but i'd like to do it by days and then collapse by weeks, and months and get totals by weeks and months too. Then, I'd like to multiply the rate by the hours to find the total number of hours, however the way it is done right now is not very intelligent and very time consuming when creating the formula, which looks something like this :

=SUM(H4*\$C\$4,H5*\$C\$5,H6*\$C\$6,H8*\$C\$8,H9*\$C\$9,H10*\$C\$10,H11*\$C\$11,H13*\$C\$13,H14*\$C\$14,H15*\$C\$15,H16*\$C\$16)

what can be done to make this a bit better?

## What To Keep Track Of Employee Project Hours To Then Do Some Calculations With Totals

May 20, 2014

I have been working on the attached spreadsheet, we have projects that our employees work on. I need to keep track of the hours spent on each. Then I need to combine this information so I can do some calculations, for example based on the total hours spent I can then work out the actual cost spend on the project versus what we have invoiced for this project, like a mini Profit and Loss on the labor only.

I created a different worksheet per employee, I would like each employee to only be able to access there own worksheet, based on some protect or share worksheet rules. I created lists of Projects and Users, so this data is the same in each worksheet. Then I would like the data from each employee (only have 3 at this stage) to collate onto another worksheet, which only I can see, so I can do my total calculations etc. Along with hours, sometimes each project might have extras, this is charged at a certain rate per employee, so on the totals page I will also use this information.

Here is what I have so far. Workings 150514 - sample for expert.xlsx

## Formula To Show Project Size Based On Number Of Hours

Jul 8, 2009

I need to show the size of a project in a cell based on it's number of hours, e.g.

Between 5-80 hours = SMALL
Between 80-200 hours = MEDIUM
Over 200 hours = LARGE

How can I do this? I can't get the IF function to work like this (unless I'm doing something wrong) and I thought about using a pivot table but think there must be an easy formula to use?

## Automatically Count Weeks

Jul 26, 2008

Here´s the deal: based on weekly reports, I need to count and sum orders created in our CRM.

I´m exporting, once a week, a full report of opportunities created in our CRM. Week after week I copy&paste the changes to my main file or dashboard. This allows me to see, manually four values my sales manager wants:

1. Opportunities created in the last week
2. Opportunities created in the last 2 weeks
3. Opportunities created in the last month
4. YTD

I don´t need help with the last one, that´s the easy part. The thing is, I have to do this manually. Every thursday I run the report, export it, find the new opportunities and add them to my main report. Then, I just modify an already defined IF formula that counts and a SUMIF one that sums the values, so it will take into account only the last week, the one before that one and the whole month.

So, my questions are...

1. I need to set different formulas for count and sum, that´s clear, but how can I make this autimatically without having to change the formula each week?

2. Do I need to consider the date my main file is modified, and count backwards?

As usual I am not sure I´m being clear, though I hope I´m getting better at this. I´m attaching an example where B2:D7 is similar to my main report, and G4:M12 is my DataTable.

The formulas in C4, C5, C6, D4, D5, D6 should be "automatic", so when I copy&paste the extra rows from the weekly reports into my datatable, those cells will count and sum without me having to change the period in the formulas.

## Distribute Hours To A New Table Hour By Hour?

Dec 2, 2013

I have a time table which some hours are 3 hours time period. I want to distribute to new table hour by hour this kind of hours, or hour to same hour period.

Attached file, defined example and detail.

## Make Selected Contents Appears Automatically According To Weeks In A Sheet?

Mar 13, 2013

I have a sheet named PE which is the main source of handling a contents in other sheet.

How to make a selected contents in PE sheet appears automatically according to weeks in sheet1?

How do i use data validation, offset or combo box to solve this problem?

## Vba Formula Automatically Inserts Into The MS Project Box

Jan 9, 2009

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.

## IF/VLOOKUP (set Up A Spreadsheet Which Automatically Calculates The Start And End Dates Of Project Tasks)

Feb 1, 2009

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.

## Generating List Of Project Parts Based On Project Duration And Time Window

Aug 4, 2014

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.

## Consolidate 4 Excel Project Lists (Workbooks) To New Master Project List Using VBA

Sep 5, 2013

My 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.

## If Statements: Option Of Between 3 Weeks And 6 Weeks

Sep 7, 2009

i currently have a what if statement

=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")

I need it to also give an option of between 3 wks and 6 weeks

Therefore all options are

3-6 WKs
< 3 WKs
> 6 WKs

I have mastered 2 but now need a 3rd.

## Get Excel To Open MS Project File And Then Run Project Macro On It

Jul 30, 2013

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].....

## Protect VBA Project Or Lock Project For Viewing By VBA

Apr 27, 2007

I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.

## Automatically Fills In The List Hours

Apr 18, 2008

I just want a simple autofill to keep from honking up my ST hours/OT hours... if you enter 6 days with a simple cell*cell formula you can get 48 hours ST and that just won't do...

How do I formulate so that when I enter in the days it automatically fills in the list hours I have for reference?

Days - ST - OT
1 8 4
2 16 8

## Run Macro From Closed Workbook Automatically For Every 24 Hours?

Nov 24, 2012

I have a file which is having name as "Team data-11/23/2012" actually i have to create a file on daily basis except Saturday and Sunday. I need a macro which automatically runs from one file and saves the excel file on daily basis. and the file name should "team data - (today's date)". this file also should be created automatically. it means that macro should run automatically for every 24 hours.

## Automatically Give Number Of Hours Worked

Feb 6, 2007

I'm working with a spreadsheet that works out how many hours someone has worked on a given shift.
In cell C4 I have the formula =E4-D4+IF(D4>E4,1)
In cells D4 & D5 I have an entered number in time format (custom hh:mm)
The problem with this is that when a time is entered in either D4 or E4 it automatically gives me a number of hours worked (upto midnight) without any input in the other.

Aim: Not to show the number of hours worked in cell C4 until hours in both cells D4 & E4 have been entered. NB. Sometimes the start times are 20:00 until 08:00 (night shift) hence answer 10:00.

## Tally Report Of X: Count On Column B (project \$) Depending On The Criteria On Column A (project Description)

Aug 28, 2007

I need to write a couple of functions. Firstly I want to do a count on column B (project \$) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].

## Separate Simple Hours / Holiday Hours And Night Hours

Jun 27, 2014

As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.

Nights, holidays and holiday night have different price/hour.

The excel is working fineâ€¦but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctorâ€™s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for meâ€¦

On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!

Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)

## Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours

Nov 4, 2008

In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time.

Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.

## Distribute Macro To Others

Feb 28, 2008

I have created a Macro and would like to make it available to other members of my team. This Macro is to be used in various different workbooks - so it cannot be tied to a single specific workbook. I tried creating an Add-In, but I've learned that, even if I instruct my colleagues how to load the Add-In, they will not be able to see the Macro that is inside it. So what methods are recommended for distributing Macro's to other users?

## How To Distribute 200 Items Into 14 Employees

May 19, 2014

I been working on excel sheet that will distribute 200 items between the employees that are available to work. I've attached a copy of the workbook. First I need to look at the quantity of employees I have working that day. Second Distribute the items to each one of the employees. Third give the first employee the first piece. Fourth give the next available piece to the last person with a zero piece in their list.

book8.zipâ€Ž

## Distribute Data Without Repetition

Feb 17, 2014

I organize monthly audits of work completed by the team. Essentially we all audit each others work; but we cannot audit our own work.

I would like a formula that will evenly distribute the jobs for audit without passing a job back to someone that owns the job originally. e.g. Gareth cannot audit a job Gareth owns, but can audit Zach or Claire's jobs. The Excel sheet attached should make it easy to understand.

So far I have tried: =INDEX(A2:A24,RANDBETWEEN(2,24)) but this duplicates data and I cannot get it to evenly spread the jobs amongst the team.

## Distribute ID Based On Criteria?

Mar 10, 2014

I have a workbook that needs to fill the column B (if the column A is populated) with Column C if the column D is "YEs" once i clicked the command button. the distribution should be even. i tried rand() but its not distributively well, so i'm thinking to work it with macro.

## SUM Spread / Distribute Formula?

Dec 29, 2009

I've enclosed a sample summary page to see how I'm going to use the formula.

Dec 15, 2009

Is there a tool that can make an Excel application behave like a “real” software? Meaning, the user will still be able to do what I planned for him to do with the file, BUT it will be very difficult for the more-than-average user to “crack” any password protections I have applied to the file? (Something like an MDE file in Access, perhaps?) The reason I am asking is because I have invested a lot of time and efforts into creating an application that I wish to sell to some of my clients, and don’t want them to easily find their way into the formulas and macros I have placed in the file.

The protection tool does not have to be an Excel tool, necessarily, but it should not require any programming skills (because I don’t have them. If there isn’t such a tool? My application contains User Forms (for input), a Database (with Auto Filters and formulas) and some Pivot Table reports. BTW, my application was created in Excel 2003 but should also be used by users with Excel 2007.

## Calculate Hours Worked With Lunch / Overtime / Holiday / Vacation And Saturday Hours?

Jan 13, 2014

Lunch is not paid. Holiday and vacation hours get calculated at the regular pay rate. Overtime is anything in excess of 8 hours per day and/or in excess of 40 hours per week and/or over 5 working days per week. Saturdays for most the employees will be overtime because it will be their 6th workday of the week; but it will be regular time for one employee as it will only be his 5th workday of the week.

For accounting and payroll purposes, we need the totals to display in both hour and decimal format.

So far, I have Lunch, Regular and Overtime hours figured out, but I still need to work with Saturday, Vacation and Holiday hours. Also, currently, the time in and out has to be typed in with the colon and AM or PM. Is there another way to input the info without having to type in those items? I'm trying to make it as user friendly as possible.

## Distribute Number Based On Score?

May 14, 2014

I am trying to distribute the total number of sessions (cell H1) across the groups in column A based on the score of each group (column D). The number of sessions a group receives cannot exceed the max (cell H2) or be less than the min (cell h3).