Automate Accounting Weeks

[Sara]

Hi there


I'm getting lost...help please.


I am trying to set up the beginnings of a dashboard taking payroll data (Sunday to Saturday) and adjusting to accounting weeks (Monday to Friday).


The accounting weeks vary from 4-5 each month, and the payroll data needs to be adjusted often for weeks 1 and 5 for partial periods within the month.


The file below shows two months with the periods shown. Where do I start to write a formula to automate the dates next to the Week numbers?

Should I be heading for a calculation on WEEKDAY formula?


https://skydrive.live.com/redir?resid=9D4A15C8F236789E!116&authkey=!AJZtycJG91zTpdY


Can anyone give me some pointers?


Cheers

Sara

 

[Smallman]

Hi Sara


I think you are heading in the right direction. In B13 (week1 Start) put the following formula;


=B5-WEEKDAY(B5,2)


Add 7 for your finish date and for week 2 start date add 7 again and do the same for the remaining weeks.


In D13 put this formula;


=B5-WEEKDAY(B5,2)+1


Add 5 for your finish date and for week 2 start date add 7 once again do the same for the remaining weeks.


This should give you a start at any rate.


Take care


Smallman

 

[Sara]

Thanks Smallman for getting me started.

I amended it slightly so that if the 1st of the month was a Sunday the date didn't change

=IF(WEEKDAY(B5)=1,B5,B5-WEEKDAY(B5,2))


Although I hadn't stated, the accounting dates needed to be within the month

=IF(MONTH(B11+1)<>MONTH(B5),B5,B11+1)


Cheers

Sara

 

[Sara]

Hi Smallman


I've gotten stuck again.

File link:

https://skydrive.live.com/redir?resid=9D4A15C8F236789E!118&authkey=!AKYmR0rtugSHAgQ


Based on a Month/Year selected, I need to break into the Pay Weeks (Sun - Sat) and Accounting weeks (Mon - Fri)

The accounting weeks must start and end within the Month/Year selected.


I thought I had it until I selected Jun 2013.

Pay Week 1 = 26 May - 1 Jun but it should become 2 Jun - 8 Jun.


How do I edit my formula to pick the first week with networking days within the month selected?


Cheers

Sara