Full Time Equivalent Formula 


Full Time Equivalent Formula  Excel 
View Answers 
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...
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 Tutorials
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 ...
Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks. This is ...
How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
Stop Formula Calculation in Excel  Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
Reference Other Excel Files with Formulas and Functions
In Excel you can use formulas and functions to reference data that is stored in another Excel workbook. This creat ...
In Excel you can use formulas and functions to reference data that is stored in another Excel workbook. This creat ...
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
 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
 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
 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
 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
 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.
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
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
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!!
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 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 35days (= 72120hrs) , 57days (=121168hrs) and >7days (=>169hrs) returning the Text "35days" for the corresponding hours etc..
I hope this is enough info
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 dayday 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
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.
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 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 dayday 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 4842 = +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
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 4842 = +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  011709.xls " Currently the formula looks like:
='[ Time Sheets  011709.xls ]Sheet1'!$B$51
But this isn't working either.
Thanks in advance for the help.
Regards
Dan
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  011709.xls " Currently the formula looks like:
='[ Time Sheets  011709.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 monfri 37 hour week
1 Part Time Staff monwed 22 hour week
1 Part Time Staff thursfri 15 hour week
Regional Office 1
1 full time staff monfri 37 hour week
Regional Office 2
1 full time staff monfri 37 hour week
Regional Office 3
1 Part Time Staff thursfri 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
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 retype them, but I expect the Project Days to change over time, and
I want the calendar dates to change with them.
Thanks.
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 retype 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 1316.
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.
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 1316.
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.
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 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 3039 hours per week they get 7.5 hours per month. If they work 2029 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 2029 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 parttime wishes to work at 33 hours/week (as an example), how do I work out the proportion of 1/2 hour (prorata) 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 (HrsperWeek)
C = Proportion of Weekly 1/2 Hr (HrsperYear)
D = Annual Allowance in hours (calculated to the nearest 15 minutes)
ABCD
7.00000.09595.00055.00
7.25000.09935.17785.25
7.50000.10275.35515.25
7.75000.10625.53765.50
8.00000.10965.71495.75
8.25000.11305.89216.00
8.50000.11646.06946.00
8.75000.11996.25196.25
And yet, I can't see how it is being formulated? Any ideas?
Ta!
Full time colleagues works at 36.5 hours/week, but paid for 37 hours/week.
Question:
If a parttime wishes to work at 33 hours/week (as an example), how do I work out the proportion of 1/2 hour (prorata) 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 (HrsperWeek)
C = Proportion of Weekly 1/2 Hr (HrsperYear)
D = Annual Allowance in hours (calculated to the nearest 15 minutes)
ABCD
7.00000.09595.00055.00
7.25000.09935.17785.25
7.50000.10275.35515.25
7.75000.10625.53765.50
8.00000.10965.71495.75
8.25000.11305.89216.00
8.50000.11646.06946.00
8.75000.11996.25196.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 05. 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
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 05. 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 152) with Quote:
TRUNC(((StartDateDATE(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?
I can get excel to work out the week number in the calendar year (ie week 152) with Quote:
TRUNC(((StartDateDATE(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 15 and then mtd (month to date). I simply want the average of the same cell over weeks 15, 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.
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 15 and then mtd (month to date). I simply want the average of the same cell over weeks 15, 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.
Hi, this is not just simply inserting defined names into formulas.
This is mainly for use between different worksheets.
So for example: if I have 52 sheets named: Week 1, Week 2, ..., Week 52.
And on a separate sheet I have the following:
Column A Column B
Week 1 ='Week 1'!SUM($B$3:$B$8)
Week 2 ='Week 2'!SUM($B$3:$B$8)
... and so on.
Is there a way so that in column B, I don't have to manually change the week number, but it will take the value from column A. Kind of like:
='A1'!SUM($B$3:$B$8)
I know this doesn't work, but I need something like it to work. Any ideas?
I am no good at all on Visual Basic
This is mainly for use between different worksheets.
So for example: if I have 52 sheets named: Week 1, Week 2, ..., Week 52.
And on a separate sheet I have the following:
Column A Column B
Week 1 ='Week 1'!SUM($B$3:$B$8)
Week 2 ='Week 2'!SUM($B$3:$B$8)
... and so on.
Is there a way so that in column B, I don't have to manually change the week number, but it will take the value from column A. Kind of like:
='A1'!SUM($B$3:$B$8)
I know this doesn't work, but I need something like it to work. Any ideas?
I am no good at all on Visual Basic
Hello All,
My data set is as follows:
Sheet1 A B C D E F G H I J K L 1 Time ID Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 2 1 Value A 70 88 22 4 33 29 2 75 66 73 3 1 Value B 83 77 94 66 34 47 64 56 88 69 4 1 Value C 8 29 74 67 41 43 67 67 84 25 5 2 Value A 68 23 16 80 37 17 6 2 Value B 23 13 28 89 85 35 7 2 Value C 28 25 27 70 13 58 8 3 Value A 22 87 9 3 Value B 98 73 10 3 Value C 34 83 Excel 2003
And I'm looking to return the latest "set" of values in the table, so the results would look as follows:
Sheet2 A B C D E F G 1 ID Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 2 Value A 68 23 16 80 22 87 3 Value B 23 13 28 89 98 73 4 Value C 28 25 27 70 34 83 Excel 2003
I've been playing around with INDEX, MATCH and LOOKUP(9.99999999999999E+307), but I can't quite get the syntax right to retrieve the right values. It's like I need LOOKUP(9.99999999999999E+307) but with an IF criteria (relating to the ID element).
Can anyone help?
Thanks in advance.
Matty
My data set is as follows:
Sheet1 A B C D E F G H I J K L 1 Time ID Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 2 1 Value A 70 88 22 4 33 29 2 75 66 73 3 1 Value B 83 77 94 66 34 47 64 56 88 69 4 1 Value C 8 29 74 67 41 43 67 67 84 25 5 2 Value A 68 23 16 80 37 17 6 2 Value B 23 13 28 89 85 35 7 2 Value C 28 25 27 70 13 58 8 3 Value A 22 87 9 3 Value B 98 73 10 3 Value C 34 83 Excel 2003
And I'm looking to return the latest "set" of values in the table, so the results would look as follows:
Sheet2 A B C D E F G 1 ID Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 2 Value A 68 23 16 80 22 87 3 Value B 23 13 28 89 98 73 4 Value C 28 25 27 70 34 83 Excel 2003
I've been playing around with INDEX, MATCH and LOOKUP(9.99999999999999E+307), but I can't quite get the syntax right to retrieve the right values. It's like I need LOOKUP(9.99999999999999E+307) but with an IF criteria (relating to the ID element).
Can anyone help?
Thanks in advance.
Matty
Hey everyone, another question. I'm trying to use an IF statement for this, so I'm not sure if that's my problem or not. Are there any restrictions to the # of IF's embedded within an IF? Here's the setup.
B3:R3 will eventually have numbers in them, from B to R. As I enter them, I'd like this cell to update the denominator from another sheet. Here's what I have so far:
=IF(R3="",IF(Q3="",IF(P3="",IF(O3="",IF(N3="",IF(M3="",IF(L3="",IF(K3="",IF(J3="",IF(I3="",IF(H3="",IF(G3="",IF(F3="",IF(E3="",IF(D3="",IF(C3="",IF(B3="",100,'week 1'!B24),'week 2'!B50),'Week 3'!B50),'Week 4'!B50),'Week 5'!B50),'Week 6'!B50),'Week 7'!B50),'Week 8'!B50),'Week 9'!B50),'Week 10'!B50),'Week 11'!B50),'Week 12'!B50),'Week 13'!B50),'Week 14'!B50),'Week 15'!B50),'Week 16'!B50),'Week 17'!B50)
Basically I thought it would count down from R, if it was blank, check Q, if not, it would use a # from another sheet. Not sure where I've gone wrong.
Thanks
B3:R3 will eventually have numbers in them, from B to R. As I enter them, I'd like this cell to update the denominator from another sheet. Here's what I have so far:
=IF(R3="",IF(Q3="",IF(P3="",IF(O3="",IF(N3="",IF(M3="",IF(L3="",IF(K3="",IF(J3="",IF(I3="",IF(H3="",IF(G3="",IF(F3="",IF(E3="",IF(D3="",IF(C3="",IF(B3="",100,'week 1'!B24),'week 2'!B50),'Week 3'!B50),'Week 4'!B50),'Week 5'!B50),'Week 6'!B50),'Week 7'!B50),'Week 8'!B50),'Week 9'!B50),'Week 10'!B50),'Week 11'!B50),'Week 12'!B50),'Week 13'!B50),'Week 14'!B50),'Week 15'!B50),'Week 16'!B50),'Week 17'!B50)
Basically I thought it would count down from R, if it was blank, check Q, if not, it would use a # from another sheet. Not sure where I've gone wrong.
Thanks
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
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((A1SUM(MOD(DATE(YEAR(A1MOD(A12,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'
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((A1SUM(MOD(DATE(YEAR(A1MOD(A12,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'