• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Calculations [SOLVED]

S

Sara

Member
Hi there


I've gotten stuck again...http://chandoo.org/forums/topic/automate-accounting-weeks

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
 
Hi Sara ,


The following points are clear from your post :


1. The pay week is always from Sunday to Saturday ; the accounting week is always from Monday to Friday.


2. The accounting week must start within the month and year selected ; this means that the first accounting week will always start on the first Monday of the selected month and year. The last accounting week of the month may have less than 5 days e.g. in this month , the last week starts on the 29th and ends on the 31st. Is this correct ?


3. What about the pay weeks ? Should these also start and end in the selected month and year ?


Narayan
 
Hi Narayan


The pay weeks move with the financial year (Jul - Jun).

The purpose behind the dates is to match the relevant pay week data with the account week per month.


The accounting week will divide the pay period into the selected month and count the number of networking days. For example, if the month starts on Wednesday, the payperiod will start on the previous Sunday, and the first accounting week will only be 3 days long.


Regards

Sara
 
Sara


This post is being conducted across two threads. I can't access sky drives right now but there are many members who can assist you with your problem. I would imagine there is some sort of forum policy about double posting.


I will get to view your file Aussie night time if no one assists in the mean time.


http://chandoo.org/forums/topic/automate-accounting-weeks


Take care


Smallman
 
Hi Sara ,


Let me understand your points :


1. The first accounting week will start from the first weekday of the month , and extend till the first Friday of the month ; thus , if the 1st. of a month falls on a Friday , the first accounting week will be just one day ; is this correct ?


2. Regarding the pay weeks , they are for an entire year ; thus will the first pay week of the year start from the first Sunday of July ? An example will clarify matters further :


Suppose we consider 2012 ; the 1st. of July fell on a Sunday , which means the first pay period of 2012 started on July 1 , 2012 and ended on July 7 , 2012. I assume that the remaining pay periods of 2012 will succeed one after the other.


What about the last pay period of 2012 ?


There is one pay period starting on June 23 , 2012 , and ending on June 29 , 2012. What about the pay period which starts on June 30 , 2012 ? Will this belong to the year 2012 , or will it be the first pay period of 2013 ?


Narayan
 
Hi Narayan


To clarify, point 1 - spot on!

Point 2 - The payweeks are never ending. The financial year is separated into months of the year for my accountants starting with July.

The accountants only want to know about working days within their months, split into weeks.

June 2013:

Pay Periods: 27/5/12 - 2/6/12; 2/6/12 - 9/6/12; 10/6/12 - 16/6/12; 17/6/12 - 23/6/12; 24/6/12 - 30/6/12

The accountants only want:

1/6/12 - 1/6/12; 4/6/12 - 8/6/12; 11/6/12 - 15/6/12; 18/6/12 - 22/6/12; 25/6/12 - 29/6/12


The formula is to split months into weeks, and match with relevant pay weeks


Regards

Sara
 
Hi Sara ,


The second point is still not clear ; let me repeat myself :


Year 2012


Pay week #1 : July 1 , 2012 - July 7 , 2012

.

.

.

.

Pay week #52 : June 23, 2013 - June 29, 2013


Question


Does the pay week starting June 30, 2012 belong to year 2012 , or is it the 1st. pay week of year 2013 ?


Can you please clarify this ?


Narayan
 
Hi Narayan


That's brilliant.

Can you explain how the formula in K1 works?

=MIN(IF(WEEKDAY(ROW(INDIRECT(""&DATE($J$1,MONTH($B$1&0),1)&":"&DATE($J$1,MONTH($B$1&0),8))),2)<=5,ROW(INDIRECT(""&DATE($J$1,MONTH($B$1&0),1)&":"&DATE($J$1,MONTH($B$1&0),8)))))


Cheers

Sara
 
Hi Sara ,


First , I am sorry for a mistake in the formula in K1 ; please replace it with this :


=MIN(IF(WEEKDAY(ROW(INDIRECT(""&$B$4&":"&$B$4+2)),2)<=5,ROW(INDIRECT(""&$B$4&":"&$B$4+2))))


The value in J1 is not required in the formula in K1.


What we want to do is find out the first weekday of the month ; this may be any day from Monday through Friday.


To do this , we need to use the WEEKDAY function , but there are three versions of this ; the Excel help on this says :

[pre]
Code: 
1 or omitted     :  Numbers 1 (Sunday) through 7 (Saturday).
Behaves like previous versions of Microsoft Excel. 

2                :  Numbers 1 (Monday) through 7 (Sunday). 

3                :  Numbers 0 (Monday) through 6 (Sunday).
[/pre]
We can use 1 , but the formula will be longer , since the weekdays are sandwiched between Sunday and Saturday ; we can use either 2 or 3 since the weekends are at one end of the range ; the formula uses 2.


When we use 2 as the second parameter in the WEEKDAY function , any value less than or equal to 5 is a weekday.


Now , we need to find out the first weekday ; since we will always start from the 1st. of a month , we need to check the first 3 days of the month ; I have checked the first 8 , which is unnecessary.


So we check the first 3 days of the month to see whether the WEEKDAY function returns a value less than or equal to 5 ; the only thing that remains is to get the dates corresponding to the first 3 days of the selected month and year.


To do this , we need to use the month and year selected to get a date for the 1st. ; this is done in B4 , using the formula :


=DATE(B2,MONTH(B1&0),1)


MONTH(B1&0) gives a number from 1 through 12 , corresponding to the months Jan , Feb , ... , Dec.


Please re-download the file from here :


https://www.dropbox.com/s/0gaj3ltvyzd86qc/Dates.xlsx


Narayan
 
You must log in or register to reply here.
Back
Top