Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel - remaining wks in a year formula
Help please!! I am finalizing a payroll accrual file and would love it if
there was a way to speed up this process every year. Does any know the formula to get the remaining weeks in a year based on various date fields? In other words... an employee accruing his 1st 40hrs of vacation would be... Emp#123(A1) started on 7/1/05 (A2) - therefore, his specific rate of accrual ..769231 (A3) would be calculated by the remaining weeks 26 (A4) in the year. What formula can I use to find (A4) based on date of hire (A2) and 52 weeks in a year? Anyone's help would be greatly appreciated!! Have a great day peeps. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel - remaining wks in a year formula
You could use this. =53-weeknum(A2,1) The reason I used 53 (not 52) is that the week number for the date specified is the 27th week. To be sure that you are including that week in your calculation, I added 1 to the total weeks. The result of this formula is 26. Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=506824 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel - remaining wks in a year formula
Try this:
Place the date of the start of your fiscal year in a cell (say A1). Then, to calculate the weeks remaining, enter the following in the cell where the week is calculated: =ABS((365-(C2-$A$1))/7) C2 contains the date of hire. This formula calculates the number of days between the start of the fiscal year and the date of hire ("365-(C2-$A$1)"); then converts it to weeks ("/7"); then cleans up the results by using the absolute value ("ABS") function. "GimmeStars" wrote: Help please!! I am finalizing a payroll accrual file and would love it if there was a way to speed up this process every year. Does any know the formula to get the remaining weeks in a year based on various date fields? In other words... an employee accruing his 1st 40hrs of vacation would be... Emp#123(A1) started on 7/1/05 (A2) - therefore, his specific rate of accrual .769231 (A3) would be calculated by the remaining weeks 26 (A4) in the year. What formula can I use to find (A4) based on date of hire (A2) and 52 weeks in a year? Anyone's help would be greatly appreciated!! Have a great day peeps. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel - remaining wks in a year formula
First, verify that you have the Analysis TookPak checked in...
ToolsAddins... This addin comes with Excel but for some reason the Excel Install process does not turn it on. The function WEEKNUM() is now available. You now have to decide the definition of your 'week'. Does it start on Saturday? Sunday? Monday? In other words, is Sunday, December 31, 2006, the end of Calendar year 2006 or the beginning of 2007? =WEEKNUM("7/1/2006") returns 26 =WEEKNUM("12/31/2006") returns 53 =WEEKNUM("12/30/2006") returns 52 So you must first decide how many weeks you have in 2006. Once you've decided (IMHO for payroll accrual purposes this should be 52 in this case), the formula is pretty straight forward... =WEEKNUM("12/30/2006") - WEEKNUM("7/1/2006") or =WEEKNUM("12/30/2006") - WEEKNUM(A2) HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "GimmeStars" wrote: Help please!! I am finalizing a payroll accrual file and would love it if there was a way to speed up this process every year. Does any know the formula to get the remaining weeks in a year based on various date fields? In other words... an employee accruing his 1st 40hrs of vacation would be... Emp#123(A1) started on 7/1/05 (A2) - therefore, his specific rate of accrual .769231 (A3) would be calculated by the remaining weeks 26 (A4) in the year. What formula can I use to find (A4) based on date of hire (A2) and 52 weeks in a year? Anyone's help would be greatly appreciated!! Have a great day peeps. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
How can i get an If formula in excel to edit another cell? | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
Excel 97 - Adding Every 8th Row - Formula should work, but doesn't | Excel Worksheet Functions | |||
How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions |