Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

52/53 week year end date formula

Posted on 2016-10-12
5
Medium Priority
?
417 Views
Last Modified: 2016-10-17
52/53 week year end date calculator

We want to know if there is an Excel formula that serves as a 52/53 week year end date calculator for all situations. Such a formula would answer a question of this nature:
•      For the next 20 years, what is the mm/dd/yyyy for a company whose year end date is the last Saturday in December or
•      For the next 20 years, what is the mm/dd/yyyy for a company whose year end date is the closest Sunday in January


The required input values would include:
•      Month
•      Year
•      Year end day of week, e.g., Saturday
•      Closest day to calendar month end or last day in month

We work with excel enough to be handy with using VBA to automate tasks like this, so we are open to a solution that would involve us plugging code into a VBA module.

Will award full points for a useful solution.

Thanks
0
Comment
Question by:Lee Richardson
  • 3
5 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41841160
Given the year is in cell A2,

Last Saturday of that year:
=(DATE(A2,12,31)-IF(WEEKDAY(DATE(A2,12,31))=7,0,WEEKDAY(DATE(A2,12,31))))

First Sunday of the following year:
=(DATE(A2+1,1,1)+1)+IF(WEEKDAY(DATE(A2+1,1,1)+1)=1,0,8-WEEKDAY(DATE(A2+1,1,1)+1))

This is based on a Sunday-Saturday week.  My gut tells me this could be simplified, but this works.
EE-28976075.xlsx
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 41841503
You can probably use a combination of EOMONTH and CEILING/FLOOR functions.

Syntax for EOMONTH:

=EOMONTH(Date,Offset)  returns the last date of a month based on number of months offset from date.  0 gives the end of the same month, 1 next month, 2 gives next again etc. You can also use -1 for previous month.

Syntax for CEILING or FLOOR:

=CEILING(Number,Factor)  this will round the number to the given factor.  As dates are essentially stored as numbers within excel and the fact that the historical day 1 in the system was a sunday, using CEILING/FLOOR on a date with a factor of 7 will round a date to the preceding or following Saturday (FLOOR rounds down, CEILING rounds up). This works in a similar way to MROUND but with the direction of rounding specified.

If you upload a file with some sample inputs and required results, we can work on specifics.

Thanks
Rob H
1
 
LVL 34

Expert Comment

by:Rob Henson
ID: 41842106
Last Saturday of Year:

=FLOOR(DATE(A2,B2,C2),7)   A2 = Year number, B2 = Month number, C2 = Day number.

Closest Saturday:

=MROUND(DATE(A2,B2,C2),7)   same references

First Sunday of following year:

=CEILING(DATE(A2,B2,C2),7)+1  same references

If it will always be December, then the Month and Day can be changed to fixed values (12 & 31). This could be useful as DATE function can be too clever for its own good. For example, knowing that November has only 30 days, look at this:

=DATE(2016,11,31)

You would expect it to give an error as 31 November doesn't exist; however the DATE function will return 1 December. It works out the date progressively. Year 2016 starts on 1 Jan, 11 months later is 1 November, (strictly speaking I know its only 10), 31 days later is 1 December. Likewise, if for some reason Month was set to 13 it would return a date for a year plus one month.
0
 
LVL 34

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 41842273
Take a look at the attached.

Sheet1
Gives a list for the next 25 years. Change values in row 2 for Year, Month and Day to give effective "Year End Date". Month dates can only be 1 to 12 and Day number will be restricted based on month number.
Then change entry in E2 to give last day of period, drop-down with Sunday through Saturday to choose from. Columns H to L then give the calculated year end date based on 3 options:
1) Last date corresponding to chosen day
2) First date after the Effective Year End corresponding to chosen day
3) Closest date to the Effective Year End corresponding to chosen day

Sheet2
Calculations with options as the above for one specific effective year end date.
Enter year in A3, choose month in B3, choose Day in C3
Choose Last Day in E3 and basis of calculation in G3.
Calculation shows in A7.

I have done some testing for different scenarios and those that I tested came back OK but you might want to do some more extensive testing before taking the results as final.

Thanks
Rob H
Year-End-Dates.xlsx
1
 

Author Closing Comment

by:Lee Richardson
ID: 41847396
Looks great! Thanks, Rob.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question