Excel - remaining wks in a year formula

[Guest]

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.

 

[SteveG]

You could use this.

=53-weeknum(A2,1)

The reason I used 53 (not 52) is that the week number for the dat
specified is the 27th week. To be sure that you are including tha
week in your calculation, I added 1 to the total weeks. The result o
this formula is 26.

Does that help?

Stev

 

[Guest]

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.

 

[Guest]

First, verify that you have the Analysis TookPak checked in...
Tools>Addins...

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,