Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

View Answers     

Similar Excel Tutorials

Change Minutes from a Decimal to a Percentage of an Hour
Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and ...
Open Excel Workbook Using VBA Macros
Simple way to open an Excel workbook using VBA and macros. Syntax Workbooks.Open ("File Path") Replace file path w ...
Calculate the Total Time Worked Minus Lunch Breaks in Excel
Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks. This is ...
Countdown Timer in Excel
How to create a countdown timer in Excel. This allows you to display a timer in whatever cell you want and it will ...

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

Similar Topics







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.


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


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


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




Hi Folks

In the office we currently make the provision that two of our officers must be on phones each day, this would be simple if it weren't for the three offices they are based in and the fact that we have some fulltime and part time staff.

I have seen a number of rota's on here but nothing seems to be applicable that I have found.

So we have

HEAD OFFICE
5 full time staff mon-fri 37 hour week
1 Part Time Staff mon-wed 22 hour week
1 Part Time Staff thurs-fri 15 hour week

Regional Office 1
1 full time staff mon-fri 37 hour week

Regional Office 2
1 full time staff mon-fri 37 hour week

Regional Office 3
1 Part Time Staff thurs-fri 15 hour week

What I need to ensure is that each day there is someone from head office on duty, and that the other regional offices also assist.

I know this seems like a logistical administration nightmare but if there are any ideas or if anyone has come across a similar issue I would really appreciate any pointers.

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.



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 need help creating a vacation accrual sheet for my job.
Here are the details:
I am tracking our employees' hours weekly. If they work 40 each week they get 10 hours of vacaction per month. If they work 30-39 hours per week they get 7.5 hours per month. If they work 20-29 hours per week they get 5 hours per month.
Since I am tracking the hours for our employees each week I will have 4 cells (possibly with different values). Now, the employee only gets 10 hours of vacation time if he/she works 40 each week of the month. If he/she goes below 40 but over 30, he/she gets 7.5 hours, if he/she works between 20-29 hours he/she gets 5 hours and everything under 20 hours is 0.
Example 1:
Week 1: 40
Week 2: 40
Week 3: 40
Week 4: 40
Vacation: 10 hours

Example 2:
Week 1: 40
Week 2: 38
Week 3: 40
Week 4: 40
Vacation: 7.5 hours

Example 3:
Week 1: 40
Week 2: 40
Week 3: 25
Week 4: 40
Vacation: 5 hours

Example 4:
Week 1: 40
Week 2: 40
Week 3: 40
Week 4: 17
Vacation: 0 hours

I hope this makes sense. Please let me know if you can help me and/or if you have any question!

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.


All,
I have weekly data on one tab, and daily on the other. On my weekly tab, I have a basic sum formula setup to grab the corresponding seven days. My challenge is how to create a sum formula on my weekly tab that I can drag down the other 51 rows.

Week 1 =sum(e3:39)
Day 1 Week 2 =sum(e10:e16)
Day 2 Week 3

Day 3 Week 4

Day 4 Week 5

Day 5 Week 6

Day 6 Week 7

Day 7 Week 8

Day 8 Week 9

Day 9 Week 10

Day 10 Week 11

Day 11 Week 12

Day 12 Week 13

Day 13 Week 14

Day 14 Week 15

Day 15 Week 16

Day 16

Thanks,
Chad


Hi guys and gils,

I found a thread telling me how to extract the weeknumber from a certain date. It lead me to this link http://www.cpearson.com/excel/weeknum.htm

It gave me the following formula

=INT((A1-SUM(MOD(DATE(YEAR(A1-MOD(A1-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)

When I use it, Excel brings back a date instead of a week number. I have no idea what this formula does, couldn't figure it out, but it looked promising...

Some thought on when a week starts for me:

Monday is the first day of the week.
Week #1 is the week that contains the first Thursday of the year
ISO standards.
Starting from 2007, week 1 starts on Monday, Jan 1st. This leads to a week number 53 in 2009.

Can anyone help me with getting the right week number from a date?

Thanx!

Elmacay

'People of genius do not Excel in any profession because they work in it, they work in it because they Excel'


OK we use a 52 sheet excel work book to track our hours against project numbers 08XX for 2008, 09XX for 2009, 10XX for 2010 etc. Each sheet is named by the week for example May-15.

The challenge is that everyone works on different projects and doesn't use a static list of project numbers (since the list grows everytime a client walks in the door) to put their time against but rather inputs the project number in one cell, then the name of the project in the next cell, then the time for each day which is then totaled for the row (week) in another cell at the end all on the same row.

I'm wondering how we can setup a script to automatically see all the 0828 (for example) project lines in every sheet and grab the total time for that week on that project then give me one big master list of time vs every project that person has worked on in one "total's" sheet at the end of the work book. As it stands it takes about half an hour to go through everyone's excel time sheet work books to prepare an invoice.

Idealy this Total's sheet would list any used project numbers in one column, then have a column for each weekly sheet into which it would place the total time against that project for that sheet so we could still track when the hours were being put against the project but it would take a fraction of the time it does now.

I can post an example of our time sheet book if that helps.


Hi,
I've a xls table to check when my gay work.
http://www.fcarboni.com/varie/tab.jpg

all people can work max 5 time in a week from Mon to Sat, if they want work on Sat they do to reserve a free day in a same week (for example first Jonatan week work).
The Jonatan, second week, is not possible!!!

All cell are validate with select (S1-S2-R1-R2-Free), I want to disable the Sat cell when in the same week gay has worked (R1-S1....) 5 day, in a case when he work 4 day and had take 1 free day, the cell will be avaible from the same select list.

Some ideas??
Tank in advance
Fabrizio

ps: next time a course of english


Hi,

I can't seem to come up with a solution to this and I've been working on it for a while.

I work for a waste removal company and we are looking at changing out some of our old equipment and I am trying to design a spreadsheet that will allow me to enter the following variables:

-size of current container
-# of current containers
-new container size
-current pickups/week
-new pickups/week
-safety

So what I am trying to accomplish here is to have something where I can enter in the current container sizes/numbers/pickup frequency, then enter in various different new container sizes that we are looking at as well as different pickup rates a week, then determine how many containers will be needed of the new size (ex. currently we have 3 8 cubic yard containers at a site that we empty twice a week, so if we switch those out to 6 cubic yard containers how many will we need to empty them only once a week?)

I also seem to have problems getting the rounding correct, because if 20 cubic yards are generated in a week and i have a 25 cubic yard container obviously the container will only be 80% full at pickup yet I still need one container, not 0.8 of one. On the other side of that if I have 26 cubic yards of waste generated/week (26/25=1.04) I will need that number bumped up to 2, not rounded back down to 1.

In terms of the safety, it would also be a nice feature to be able to add say for summer events where I know there will be a 25% increase in traffic in the area (therefore more waste) I will be able to tell right away how many more containers to add.



I realize this is far more than just a simple question but any help would be very much appreciated. I am struggling for the most part in trying to come up with an equation that will take the garbage generated/week and tell me how many containers I will need (and round the answer correctly).


Thank you very much.

Dan


Problem #1.
I am trying to use a date as a reference in a vlookup formula to determine the name of the week in which the date is present. My formula in cell D1 is vlookup(c1,a1:B12,1,false) and I am expecting to received a result of "Week 4" however I am getting #N/A instead.

Week 1 2/14/09 3/7/09 #N/A
Week 2 2/21/09
Week 3 2/28/09
Week 4 3/7/09
Week 5 3/14/09
Week 6 3/21/09
Week 7 3/28/09
Week 8 4/4/09
Week 9 4/11/09
Week 10 4/18/09
Week 11 4/25/09
Week 12 5/2/09
Week 13 5/9/09

Problem #2
Assume an invoice date of 12/31/08. I expect to receive payment in 45 days (when added to 12/31/08 falls on 2/14/09, a Saturday). Is there a way to make the default the next business day - so my date should be 2/16/09?

Thanks in advance.


Hi all,

I have set up a calendar (away from my main data in a named range 'dates') covering the time period a project is running. I have the week's numbers running down a column (Weeks 1 - 10) and the days (Mon - Sun) running across in rows.

In column A I have listed when a certain part of the project is taking place ('Week 1', 'Week 2', etc). In column S I have which day of this week a certain part is taking place (Mon, Tues, etc).

What I'm trying to achieve is for Excel to determine from the week number and day what the specific date will be. The days (but not the weeks) are constantly changing, so I need for the user simply to be able to type in, for instance, 'Fri' and Excel will, from my calendar, work out what the specific date is.

I vaguely think some kind of VLOOKUP or INDEX/MATCH formula might work, but can't work out the exact phrasing. Any ideas?

TIA,

SamuelT


hi, I'm doing my thesis, and its all about encryption. i used some methods like mirror method etc, for example if the binary is 10110001, the output should be 10001101 (or the mirror image of the original binary). i need to come up with a mathematical equation with this, can u help me math genius? i would really appreciate your help. thanks in advance.

i already know how to do it in excel. my problem is i need to come up with a mathematical equation with this method. I need to put it in my paperwork. please help me.