Solved

52/53 week year end date formula

Posted on 2016-10-12
5
84 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 32

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 32

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 32

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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