# 52/53 week year end date formula

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
###### Who is Participating?

Finance AnalystCommented:
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

Excel VBA DeveloperCommented:
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

Finance AnalystCommented:
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

Finance AnalystCommented:
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

Owner & PresidentAuthor Commented:
Looks great! Thanks, Rob.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.