Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Full Time Equivalent Formula

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello All,

I have a project a need a very simple mathematical equation:

i just need the least possible number of FTE needed to work in 17hrs of operation 7days a week but those FTEs will only work for 5days a week.

Thanks in advance...


Similar Excel Video Tutorials

Helpful Excel Macros

Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

Similar Topics







Hello All,

I have a project a need a very simple mathematical equation:

i just need the least possible number of FTE needed to work in 17hrs of operation 7days a week but those FTEs will only work for 5days a week.

Thanks in advance...


Ok, I have read and read and can't seem to come up with a work around for my problem. Here is the situation:

I have a workbook that contains 52 worksheets (one for each week of the year). Each of these worksheets have the exact same format (day of the week with data based on daily intervals).

What I am attempting to do is circumvent the 30 limit in a formula. I need to take the value in say cell B4 from each worksheet so as to build and everage for the year (this is a call center environment that I am trying to determine the average number of calls per dat per interval).

What I thought it could look like is this:

=AVERAGE('Week 1'!B4,'Week 2'!B4,'Week 3'!B4,'Week 4'!B4,'Week 5'!B4,'Week 6'!B4,'Week 7'!B4,'Week 8'!B4,'Week 9'!B4,'Week 10'!B4,'Week 11'!B4,'Week 12'!B4,'Week 13'!B4,'Week 14'!B4,'Week 15'!B4,'Week 16'!B4,'Week 17'!B4,'Week 18'!B4,'Week 19'!B4,'Week 20'!B4,'Week 21'!B4,'Week 22'!B4,'Week 23'!B4,'Week 24'!B4,'Week 25'!B4,'Week 26'!B4,'Week 27'!B4,'Week 28'!B4,'Week 29'!B4, 'Week 30'!B4,'Week 31'!B4,'Week 32'!B4,'Week 33'!B4,'Week 34'!B4,'Week 35'!B4,'Week 36'!B4,'Week 37'!B4,'Week 38'!B4,'Week 39'!B4,'Week 40'!B4,'Week 41'!B4,'Week 42'!B4,'Week 43'!B4,'Week 44'!B4,'Week 45'!B4,'Week 46'!B4,'Week 47'!B4,'Week 48'!B4,'Week 49'!B4,'Week 50'!B4,'Week 51'!B4,'Week 52'!B4)

But there are more then 30. Any ideas how I can get around this????????? Or if there is a better option available to me (I do however have to keep the data in weekly worksheets)

Thank you in advance.


Hello again.

I am this time trying to get a week number and have it display "Week 0", "Week 1" etc up to "Week 12". I have tried ="Week "&weeknum(a1,2)/4 but I am not sure how to get the "Week 12" to reset back to "Week 0". A1 is =today() so the formula above displays "Week 8" when I really want it to equal "Week 6" but subtracting two will not work as when I start a new year the formula will not work.

So, January 1, April 1, July 1, October 1 all equal "Week 1", with the week previous to this being "Week 0".

I am putting the formula into D1 of the attached sheet.

I know it is long and difficult so thanks for whoever takes the time to look at this.


http://www.mediafire.com/?sharekey=obfy2g3xef8d4


Hi guys

this could be very simple or impossible basically i wish to fill this series for 104 weeks so you can see it is taking much time to just copy and paste anyone know a quicker way?? Thanks


To clarify the Week 11 is in Column A and T1 is in column B


Week 11 T1 Week 11 T2 Week 11 T3 Week 11 T4 Week 11 T5 Week 11 T6 Week 11 T7 Week 11 T8 Week 11 T9 Week 12 T1 Week 12 T2 Week 12 T3 Week 12 T4 Week 12 T5 Week 12 T6 Week 12 T7 Week 12 T8 Week 12 T9 Week 13 T1 Week 13 T2 Week 13 T3 Week 13 T4 Week 13 T5 Week 13 T6 Week 13 T7 Week 13 T8 Week 13 T9


Hi~ Can someone assist me in writing a formula that will give the following results:

Condisdering the work week is Monday - Friday, return:

0 = the first partial week (for example the 1st of the month starts on a Tuesday)
1 = the 1st full week
2 = 2nd full week
3 = 3rd full week
4 = 4th full week
5 = if the month ends on a partial week (for example the 31st lands on a Thursday)

Thanks!!


Hi,
I get a set of data from our corporate planning which shows the start date of the projects, project name and configuration code for the product. The product code provides a fixed sequence of number in the form of hrs per week for that project to be managed by a person. So for product code 1a we have 10 hrs first week 10 hrs 2nd week through to perhaps 54 weeks with zero work on some weeks. For 2a it may be 10, 15, 20 hrs per week sequence. I need a formula, not VBA, to be able to start the sequence from the project start date so that if the project started on week 20 then 10 hrs in week 20 another 10 hrs in week 21. I have a date to week number table consisting two columns vertically as it cover a number of years. For the formula to work it needs provide a start point followed by next set of hrs in next week and so on until the end of the project. I can then use a pivot table to obtain the hrs per week or month. Can you please help me this one has got me stumped.


I have a cell which has a duration in hours. I am trying to sort this column in another cell by 3-5days (= 72-120hrs) , 5-7days (=121-168hrs) and >7days (=>169hrs) returning the Text "3-5days" for the corresponding hours etc..

I hope this is enough info


hi there i am trying to build a calculator for the UK working time directive it requires workers to work no more than 48 hrs a week averaged over a 17 week period as drivers hours vary from day-day week - week i have made a weekly time sheet style spreadsheet which is fine until i get to

if one week they work 56 hrs = 8 hrs above 48 hrs
next week 48 hrs so that is equal to the weekly
3rd week 42 hrs so they have worked less than 48 hrs
etc etc

what kind of formula would be best to work out if above below or equal to the 48 hrs

i'm ok with basic formulas but this has me stumped
thaks in advance if anyone can help


Alright,
I am trying to build a spread sheet that shows some simple averages of numbers over the course of a month. Its broken down into Week 1, Week, 2 and so on. Then there is a Month to Date column. Problem is that when you are in week 1 and there are no values in the other fields it displays #DIV/0! Second part to this is how to tell it do different equations for the month to date number as you go from week to week. For example at the end of week 1 it would simply display the week 1 number, at the end of week 2 it would then to display week 1 plus week 2 divided by 2. Have to get this baked ASAP, greatly appreciate any help.


Hi everyone... again. Third post in as many days. As you can tell Im not the brightest spark when it comes to Excel.

I am trying to calculate the number of forecasted hours worked per week for each contractor based on their allocation to the project and also based on a 37.5 hour standard week.

I am using a formula which was previously provided to me for another problem but theoretically should share the same logic. The formula works only when a contractor is forecasted to work for a full week. If there is a contract which finishes mid week the formula returns a weird value. Also another problem is that if a contract is starting on the 12th of January, it still forecasts a full week for the week commencing the 7th of January.

I have attached a spreadsheet for everyone's reference.

I would really appreciate any help again.

Thanks.


hi there i am trying to build a calculator for the UK working time directive it requires workers to work no more than 48 hrs a week averaged over a 17 week period as drivers hours vary from day-day week - week i have made a weekly time sheet style spreadsheet which is fine until i get to

if one week they work 56 hrs = 8 hrs above 48 hrs
next week 48 hrs so that is equal to the weekly
3rd week 42 hrs so they have worked less than 48 hrs
etc etc
the week starts sun finishes sat if a day is not used for any reason hol sick etc the value is counted as 00:00 as i have done the hrs as a time formula

eg wk 1 total hrs worked 56 = 8 hrs above 48 so 48 -56 = 8
but what if hrs are less 42 hrs worked so 48-42 = +6
i can do the formula one way or the other but not both

i'm ok with basic formulas but this has me stumped
thaks in advance if anyone can help


Hi All,

Hope someone can help.
I have a date related problem and it goes like this:-
Within any working week a TRD (target ready date) is set by customer services.
Depending on the product selected (21;22;23;24) each has a lead time respectively of (2days;5days;7days and 9days)
Initially I thought it would be simple enough to take the TRD and subtract 2 days from it which would then give me a start date.
This works quite well until you realise that the new start date could fall on a Saturday or Sunday which I can't allow to happen.

What I require is a formula that looks at the product code and then subtracts the equivalent amount of days from the TRD and also takes into account weekends. If a weekend falls within thes period, then I would like the start date to be on the Friday.

Regards

Bodz


EXCEL 2007

I need the avg of b5-b19 in b20, and
the avg 0f c5-c19 in c20

need a formula to do it, use the avg function it doesn"t work.
any help?

Thanks


A B C D 4 BOS COLL AOPR MRGN PR 5 WEEK 1 64 18 82 6 WEEK 2 57 13 70 7 WEEK 3 #N/A #N/A #N/A 8 WEEK 4 #N/A #N/A #N/A 9 WEEK 5 #N/A #N/A #N/A 10 WEEK 6 #N/A #N/A #N/A 11 WEEK 7 #N/A #N/A #N/A 12 WEEK 8 #N/A #N/A #N/A 13 WEEK 9 #N/A #N/A #N/A 14 WEEK 10 #N/A #N/A #N/A 15 WEEK 11 #N/A #N/A #N/A 16 WEEK 12 #N/A #N/A #N/A 17 WEEK 13 #N/A #N/A #N/A 18 WEEK 14 #N/A #N/A #N/A 19 WEEK 15 #N/A #N/A #N/A


Hello kind folks in Excel Programming Land,
I'm trying to use a "WildCard" in an Array Formula to both simplify and handle variations in several formulas.

My work sheet is used for Time Cards. I have a tab for each of the 52 weeks in the year and each tab is labeled "W1", "W2" for Week 1, Week 2, etc.

On my SUMMARY tab, I'm using an array formula that looks at cell "'SUMMARY'!D6" for a project number, and than searches through all of the Week Tabs for anyone that has charged time against that project.

The employee would enter the project number in D12:D35 of a given weeks tab with each row representing a different task for that week. In some cases they may have several rows for the same project number.

On the SUMMARY tab, the formula for Week 1 is in cell E6. For Week 2 it's in F6, etc.

I was thinking I could simply place the sheet tab name in the row above (i.e. D5, E5, F5, etc., and then write the formula for 1 cell and copy it to all of the other cells on that row.

Currently my formula looks like:
{=IF($D$6="","",SUM(IF(' W1 '!$D$12:$D35=$D6,( 'W1'! $N$12:$N$35))))}

I was thinking the formula might look like:
{=IF($D$6="","",SUM(IF( '&E5&'! $D$12:$D35=$D6,( '&E5&'! $N$12:$N$35))))}

But that isn't working.

Additionally I was hoping to do something similar on the SUMMARY tab by referencing a different spreadsheet with something like:
='[ &A1& ]Sheet1'!$B$51
Where cell A1 has the value " Time Sheets - 01-17-09.xls " Currently the formula looks like:
='[ Time Sheets - 01-17-09.xls ]Sheet1'!$B$51

But this isn't working either.

Thanks in advance for the help.
Regards
Dan


Dear All,

Newbie - Please help!!

I have a list of week no's and a volume intake for each week. (Please note not every week has a volume intake number)

I am trying to create a couple formula that will summarise the actual week No the intake started, then the second intake hit etc, and secondly what the volume was for these weeks?

Simple Example to get idea:

Week No: Intake:
Week 1 0
Week 2 100
Week 3 0
Week 4 250

I would then require formulas that result in first intake was "Week 2" the intake was "100", the second intake was "week 4" at 250 units.

I have tried lookup's which seems to work ok but on more than one occasion the intake is the same resulting in the wrong intake week being reported plus the processing time is incredibly slow.


I hope this makes sense, and if any guidance can be given or ideas I would be very grateful,

Kind regards

The MG


I've got a project, that is scheduled out as Project Day 1, 2, 3, 4, 5, 6
etc. I want to write a formula that will add a column showing the calender
date that corresponds to each of these, once I key in a start date. They'll
all reference that start date. That, I can do. What is going to give me
fits, is that we are on a 4 day work week. SO, Project Day 5, is calendar
day 8, 6=9, 7=10, 8=11. Then, project Day 9=15, 10=16 and so forth.

Now, I used to use a formula calculated a time difference, based on an 8
hour day and work week. I could calculate the business hours from 3:00 on
a Friday to noon on a Monday. I'm thinking I'll need the same type of
formula here, somehow working in a 4 day workweek.

Does this make any sense? Do you have any idea how I can do this?

I only have 500 lines to apply this to, so I could go calculate it all and
manually re-type them, but I expect the Project Days to change over time, and
I want the calendar dates to change with them.

Thanks.



At my work we need to calculate when a patient is to come back to the office for a visit. There is also a window included in this. For instance:

Patient is seen on day one. I would like to enter that date in excel and get the following:

Week 4 with a window of + or - 3days
Week 16 with a window of + or - 7days
Week 24 with a window of + or - 7days

These values vary from time to time and would have to be changed. For instance the next time I may need to enter day one and get 5 more visits: day 3 (no window), day 8 (+3day window), day 15 (+ or -5 day window), day 28 and day 57 (+ or - 7day window)

The outcome fields would show the exact date that the person is to return with the next column showing the window. See example below:

I would enter: Friday Sept. 11, 2009

I would get:

Day 3 _________9/13 ___________ no window
Day 8 _________9/18 ___________ 9/18 - 9/21
Day 15 ________9/25 ___________ 9/20 - 9/30
Day 29 ________10/9 ___________ 10/2 - 10/16
Day 57 ________11/6 ___________ 10/30 - 11/13


Hi,

I'm looking for a formula to work out the week number from a list of dates based on the working week starting on a saturday and ending on a friday. Week 1 would need to start 1st Jan regardless of the day of the week. I can't work out how to get weeknum to do the job... any ideas?

thanks
Charlie


I am very new to Excell and looking to set up a spreadsheet that keeps track of an office football pool and measures win percentage. This is easy if everyone plays every week. But this is not the case.

The formula I would need to use would have to ignore weeks where a particular person didn't participate. There are 17 weeks in the season and a different number of games each week ranging from 13-16.


WEEK 1 WEEK 2 WEEK 3 WEEK 4 WEEK 5 WEEK 6 WEEK 7 WEEK 8 WEEK 9 WEEK 10 WEEK 11 WEEK 12 WEEK 13 WEEK 14 WEEK 15 WEEK 16 WEEK 17 TOTAL games 16 16 16 13 14 14 14 13 14 14 16 16 16 16 16 16 16 256 0 WIN PERCENTAGE correct 11 10 9 9 10 8 0
So in the example above through week 8 this participant missed two weeks. He got 38 out of a total of 89 games for a win percentage of 42.69.

Could anyone help me with a proper formula that could calculate this throughout the 17 weeks?
Thank you very much in advance.


I am working on a workbook where I have a pivot table with a list of projects that we are currently working on, then on my worksheet I have it set up so the crew can open the pull down menu and select the project that they did work on, and then enter their time spent on that project in the columns to the right of that for each day. It then sums up the hours for that week and puts the total in the column on the far right.

What I am trying to figure out is if there is a way I can have a formula that will identify what each crew member worked on, then take that time for the week and sum it all crew members times for a total time on that project for the week without me having to go through each crew members time and add them up manually. The complication I am running into is we may only work on a few projects per week, or we may work on a lot so there is no easy way for them just to use a set list of projects so the pull down menu works best there.

Any ideas or thoughts would be helpful, thanks.


I can't seem to work out how to work out the proportion of 1/2 hour for any given "x" hrs/week?

Full time colleagues works at 36.5 hours/week, but paid for 37 hours/week.

Question:
If a part-time wishes to work at 33 hours/week (as an example), how do I work out the proportion of 1/2 hour (pro-rata) to ensure it is treated fairly with full time counterparts?

I've come across a table something like this from somewhe
A = Wkly Hours Worked
B = Proportion of Weekly 1/2 Hr (Hrs-per-Week)
C = Proportion of Weekly 1/2 Hr (Hrs-per-Year)
D = Annual Allowance in hours (calculated to the nearest 15 minutes)

A-----------B---------C---------D
7.0000----0.0959----5.0005----5.00
7.2500----0.0993----5.1778----5.25
7.5000----0.1027----5.3551----5.25
7.7500----0.1062----5.5376----5.50
8.0000----0.1096----5.7149----5.75
8.2500----0.1130----5.8921----6.00
8.5000----0.1164----6.0694----6.00
8.7500----0.1199----6.2519----6.25

And yet, I can't see how it is being formulated? Any ideas?

Ta!


Hello:

I'll try to explain this the best way I can.

I've been asked to do a resource matrix based on complexity of work and duration of a project.

Complexity would be values of 0-5. Duration would be based on a start date and an end date. I think the WEEKNUM function may work out here [e.g. week 9 (start) week 45 (end); the difference would be 36 (weeks)].

I would need to take the start week and populate the count from week 9 all the way through to week 45..run a loop through the data and continue to populate each cell for each week that there is activity (or duration/difference between the two dates).

So if there is a project that starts week 1 and ends week 5 and another project that starts week 2 and ends week 4...the table should look like this...

For complexity 0 and PM Joe; week1=1, week2=2 week3=2, week4=2, week5=1.

Any help would be greatly appreciated, I'm going bald


We have a four week work rota cycle. So week 1, people do x, week 2 the same do y, week 3, z, 4 something else, then it all goes back to 1 and starts again.

I can get excel to work out the week number in the calendar year (ie week 1-52) with Quote:

TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7)

but don't know how to convert that into week 1,2,3 or 4 of our cycle.

By the way, Week 1 of the year, starting 03 Jan was week 3 of our cycle.

Any ideas?


Hello,

I've tried reading older post on this topic as I know it has been covered. Thus, I apologize for having yet another repeat post, but I can't quite figure this out. I'll start with what I believe to be the easier question, then later post the second.

I have a 6 sheet workbook, weeks 1-5 and then mtd (month to date). I simply want the average of the same cell over weeks 1-5, disregarding zero.

Currently I'm using a funky IF formula that works, but I'd like to clean it up:

=IF('Week 2'!B12=0,('Week 1'!B12),IF('Week 3'!B12=0,('Week 2'!B12+'Week 1'!B12)/2,IF('Week 4'!B12=0,('Week 3'!B12+'Week 2'!B12+'Week 1'!B12)/3,IF('Week 5'!B12=0,('Week 4'!B12+'Week 3'!B12+'Week 2'!B12+'Week 1'!B12)/4,('Week 1'!B12+'Week 2'!B12+'Week 3'!B12+'Week 4'!B12+'Week 5'!B12)/5))))


As you can tell, I'm a novice in need of some guidance. Without being too much bother I'd like to understand why another one would work, opposed to simply copying and pasting it. So if there is a resource I have over looked, please point me in that direction.


Hello. I have data like the following (... added just for spacing). This is basically a team roster and project assignment schedule.

Worker...........Week 1..........Week 2..............Week 3
Bob................Project A ...... Project B
Jim .....................................Project B.............Project B
Pete ................................... Project A ............Project B
Mary................................................................Project A

I am looking for an easy (non-VB) way to filter/present the data to see just the "Project A" data for instance. So I would end up with (for instance):

Worker...........Week 1..........Week 2..............Week 3
Bob................Project A
Pete ................................... Project A
Mary................................................................Project A


Note that the row with "Jim" is gone because he is not working on Project A. And the Project B data is not showing; just the project A data.

Thank you in advance for any help you can provide.

DM