Solved

52/53 week year end date formula

Posted on 2016-10-12
5
46 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 31

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 31

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 31

Accepted Solution

by:
Rob Henson earned 500 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now